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
- select nextval from dual
- 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"
<kenneth_r_fowler_at_groton.pfizer.com> 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: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).
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: hamcdc_at_yahoo.co.uk
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 Thu Oct 03 2002 - 04:28:33 CDT
