Oracle-L: RE: select <sequence>.nextval from dual contributes to poor perfo

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Wed, 02 Oct 2002 14:38:22 -0800
Message-ID: <F001.004DEADE.20021002143822@fatcity.com>

Ken,

Thanks for adding one more example to the history of the poor performance of dual. I have a discussion on my website at www.optimaldba.com/internals/oraint_dual.html.

Why does the app need to select just the nextval? I presume it will be used to insert a value into a table and can be coded in the insert statement. For example, INSERT INTO TABLEA (COL1) VALUES (mysequence.nextval).

Dan Fink

-----Original Message-----
Sent: Wednesday, October 02, 2002 4:13 PM To: Multiple recipients of list ORACLE-L performan

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:fowlerkr_at_groton.pfizer.com

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: kenneth_r_fowler_at_groton.pfizer.com

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: ListGuru_at_fatcity.com (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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Fink, Dan
INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------