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

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Wed, 02 Oct 2002 15:33:26 -0800
Message-ID: <F001.004DEBE8.20021002153326@fatcity.com>

RE: select <sequence>.nextval from dual contributes to poor performan...Ken wasn't complaining about the performance of the sequences, but rather about the performance of millions of queries on SYS.DUAL.. .

Original Message ----- From: Nick Wagner To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 02, 2002 4:53 PM Subject: RE: select <sequence>.nextval from dual contributes to poor perfo

you might want to try increasing the 'cache' size of the sequence.

If order is not important, and it is being used so often that it's having locking issues, you may want to create a couple different sequences, and have them increment by different values (start with 1 increment by 3, start with 2 increment by 3, start with 3 increment by 3) and have some switch in a trigger that pulls a value from one of the three sequences.

-----Original Message-----
From: Fowler, Kenneth R [mailto:kenneth_r_fowler_at_groton.pfizer.com] Sent: Wednesday, October 02, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Subject: select <sequence>.nextval from dual contributes to poor 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.pfizercom

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: Tim Gorman
INET: Tim_at_SageLogix.com

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