Hi,
It is spending a lot of time waiting for IO and something like that.
If you want to see what is the session waiting for ,just do:
alter session set timed_statistics = true; (ignore it if it is already true)
alter session set events '10046 trace name context forever,level 8';
--do your sql here.
find the trace file and tkprof(use oracle 9.2 tkprof if your oracle version is not
9.2, not sure 9.0 will work)it like:
tkprof file=your_tracefile waits=y
For your SQL, I think more hash_join should be used instead of nested loop. Try it.
regards
Zhu Chao
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, December 03, 2003 7:19 AM
> Hi Gurus,
>
> Could someone shed some light on the following tkprof output.
>
> To get 0 record it is aking more than 11 seconds. Also I see huge difference
> between CPU time and elapsed time even though the system is not so busy(It is a
> test machine. very low load on it). If you say it is waiting on something, could
> you tell me how to identify the wait event associated with this and how to
> rectify the same?
>
> Also please let me know why the query count is very high?
>
> select countryname, e.lastupdatedate
> from e e, p p, c c
> where p.pid = e.pid
> and p.hsbc_user_category='GIB'
> and p.business_country_id=c.countryabbrev
> and e.userstatusid in ( select userstatusid from userstatus )
> and p.business_country_id in ( select countryabbrev from c )
> order by countryname, e.lastupdatedate desc
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 2 0.07 0.08 0 0 0 0
> Execute 2 0.00 0.02 0 0 0 0
> Fetch 2 42.95 133.21 58730 118694 24 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 6 43.02 133.31 58730 118694 24 0
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 165
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 SORT ORDER BY
> 0 NESTED LOOPS
> 1 NESTED LOOPS
> 1590 HASH JOIN
> 239 TABLE ACCESS FULL c
> 1589 HASH JOIN
> 239 VIEW VW_NSO_1
> 239 SORT UNIQUE
> 239 INDEX FAST FULL SCAN (object id 76648)
> 1589 TABLE ACCESS FULL p
> 1589 TABLE ACCESS BY INDEX ROWID e
> 1589 INDEX UNIQUE SCAN (object id 76709)
> 0 INDEX UNIQUE SCAN (object id 76899)
>
>
>
>
> ********************************************************************************
>
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 4 0.07 0.08 0 0 0 0
> Execute 5 0.00 0.05 0 0 0 2
> Fetch 2 42.95 133.21 58730 118694 24 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 11 43.02 133.34 58730 118694 24 2
>
> Misses in library cache during parse: 1
>
> Thanks
> Jay
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> 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).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: zhu chao
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).
