Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help on tkprof output

RE: Help on tkprof output

From: Biddell, Ian <ian.biddell_at_hp.com>
Date: Tue, 02 Dec 2003 18:54:24 -0800
Message-ID: <F001.005D872D.20031202185424@fatcity.com>

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-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 Tue Dec 02 2003 - 20:54:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US