Most commonly this is due to poor coding, where "poor" means the sequence value is obtained explicitly in a trigger or before the insert/update/etc is done.
Certainly plain old "insert into xxx values (bbb.nextval, ... )" is a lot faster than a) select nextval from dual b) do insert and the former is also a lot nicer on indexes as well. The other thing is that you'll see that 'select ... from dual' is 5 logical IO's. I you can change the app to query from a "local_dual" table stored as an IOT then this drops significantly. Alternatively, you could have a local dual which is a synonym to SYS.X$DUAL. Its a question of whether your app supports such mods hth connor --- "Fowler, Kenneth R" <[EMAIL PROTECTED]> wrote: > Hi, > > > I am looking after an Oracle EE V8.1.6 database on > Solaris 2.6 and I have > been monitoring the database to try and look for > causes of poor performance. > I have been using Quest SQLLab Vision which is one > of the tools around that > will look at the SGA directly and sample stats > multiple times /sec. Based > upon the information I get back, I can see that the > following query... > > SELECT STAGE_DATA_SEQ.NEXTVAL FROM DUAL; > > Seems to use a significant amount of resource when > you take into account the > number of times it is executed. The query plan > shows a full scan of > sys.dual and it uses significant CPU and I/O. > > > Is there a better (less resource intensive) way to > get the nextval?? It may > seem a little petty but it just happens that this > query is the second > highest resource user when you take into account the > number of times it is > executed. > > > Thanks, > Ken > _________________________________________ > Clinical and Regulatory Informatics - Groton/New > London > Coordinator, Business and Technical Services > Tel: (860) 732-0026 Fax: (860) 715-8346 > Email: mailto:[EMAIL PROTECTED] > > > > LEGAL NOTICE > Unless expressly stated otherwise, this message is > confidential and may be privileged. It is intended > for the addressee(s) only. Access to this E-mail by > anyone else is unauthorized. If you are not an > addressee, any disclosure or copying of the contents > of this E-mail or any action taken (or not taken) in > reliance on it is unauthorized and may be unlawful. > If you are not an addressee, please inform the > sender immediately. > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Fowler, Kenneth R > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
