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:[EMAIL PROTECTED]]
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:[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).
