Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> select <sequence>.nextval from dual contributes to poor performan

select <sequence>.nextval from dual contributes to poor performan

From: Fowler, Kenneth R <kenneth_r_fowler_at_groton.pfizer.com>
Date: Wed, 02 Oct 2002 14:13:25 -0800
Message-ID: <F001.004DEAC1.20021002141325@fatcity.com>


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). Received on Wed Oct 02 2002 - 17:13:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US