| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help on tkprof output
Depending on the where clauses added to the subqueries I would say there is potential for the execution plan to change. Especially if the column matched to the IN clause is indexed. You are correct in that the clause may well be required but always be careful when performance tuning a "slightly" different query - sometimes the difference in execution time/plan can be surprising.
Sami
<saminathans_at_myre To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
albox.com> cc:
Sent by: Subject: RE: Help on tkprof output
ml-errors_at_fatcity
.com
03/12/2003 14:34
Please respond to
ORACLE-L
(select countryabbrev from c) subquery will have some more predicate (where
clause) to restric few countries. Basically I don't want to select all the
countries. For simplicity sake I remove those where clause(business logic).
Anyway EXECUTION PLAN will not get changed.
-----Original Message-----
Biddell, Ian
Sent: Tuesday, December 02, 2003 9:54 PM
To: Multiple recipients of list ORACLE-L
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-----
jaysingh1_at_optonline.net
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
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 165
Rows Row Source Operation
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Thanks
Jay
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: jaysingh1_at_optonline.net 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Biddell, Ian INET: ian.biddell_at_hp.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-LReceived on Tue Dec 02 2003 - 22:34:34 CST
(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: Sami INET: saminathans_at_myrealbox.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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au 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).
 |
 |