Oracle-L: Execution Plan is good but HIGH CPU

From: Muqthar Ahmed <Muqthar.Ahmed_at_decoratetoday.com>
Date: Mon, 27 Oct 2003 21:24:26 -0800
Message-ID: <F001.005D4B62.20031027212426@fatcity.com>

Hi,

Execution plan looks good but the query is consuming 800 seconds CPU time........why?

SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid

  and t2.prsuid = :b3
  and t2.wpbkid = :b2
  and t1.wpptid = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   5618      0.63       0.58          0          0          0           0
Fetch     5617    800.05     782.07          0    1409683          0        4187
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    11236    800.68     782.66          0    1409683          0        4187

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109 (DDTBL) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   NESTED LOOPS
      0    TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR'
      0     INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE)
      0    TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE'
      0     INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' 
                (NON-UNIQUE)

********************************************************************************

Muqthar Ahmed

--

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

Author: Muqthar Ahmed
INET: Muqthar.Ahmed_at_decoratetoday.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 Mon Oct 27 2003 - 23:24:26 CST