You full scans within the loops which are hitting the disks, ie.58730
which gives the big difference between cpu & elapsed as it's waiting for
IO
Also why do you need
and p.business_country_id=c.countryabbrev
As well as
and p.business_country_id in ( select countryabbrev from c )
Do you want to join to table c for any reason or do you just want to see
if the country abbreviation is in table c?
At the moment you are doing both.
SO maybe fine tune the SQL to only do what you really need it to and
your disk IO would go down along with your elapsed time.
Hth
Ian
-----Original Message-----
[EMAIL PROTECTED]
Sent: Wednesday, 3 December 2003 9:49
To: Multiple recipients of list ORACLE-L
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: Biddell, Ian
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).
