Confirmation of trace data

From: Paul Baumgartel <treegarden_at_yahoo.com>
Date: Mon, 2 Aug 2004 14:11:27 -0700 (PDT)
Message-ID: <20040802211127.11569.qmail@web13421.mail.yahoo.com>

List--

Is there anything I'm missing here? The following SQL is taking 6 to 8 seconds of elapsed time to run. There are *no* wait events in the trace file (all trace data connected with this SQL appears below). Total CPU for the operations is about .12 seconds, yet as you can see, the elapsed time for each fetch is 6 to 8 seconds.

This SQL is running during a load test; there are approximately 100 other sessions on the server (Windows 2K, dual-hyper-threaded CPU, about 2 GB of memory allotted to Oracle) and at times the CPUs are pegged at 100%. Oracle9iR2.

Given that I see no wait events, can I conclude that the elapsed time is due to waiting for CPU service? Is the fact that I'm joining to DBA_ROLE_PRIVS problematic?

TIA.

PARSING IN CURSOR #6 len=142 dep=2 uid=828 oct=3 lid=828 tim=3027624861 hv=2231874444 ad='6fd24b08'
SELECT t1.schema_name

        from tsc_schemata t1, dba_role_privs t2
        where t2.grantee = :b1 and t1.application_role =

t2.granted_role
END OF STMT
PARSE #6:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=3027624847 EXEC #6:c=0,e=201,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=3027677087 FETCH
#6:c=31250,e=8150816,p=0,cr=53,cu=0,mis=0,r=1,dep=2,og=4,tim=3035838225
EXEC #6:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=3036090408 FETCH
#6:c=93750,e=6570233,p=0,cr=53,cu=0,mis=0,r=1,dep=2,og=4,tim=3042667603

Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 02 2004 - 16:07:10 CDT