Re: Execution Plan is good but HIGH CPU

Hi,

    I guess you are just using RBO and did not analyze your table. Try analyze it and run it again.     You nested loop maybe is inefficient, as it generate a lot of buffer_gets.Maybe you can consider using hash_join instead of nested loop.

    If you still plan to use nested loop, consider this part:      


>       0    TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE'

>       0     INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' 

>                 (NON-UNIQUE)

    I guess it is using inefficient index.

Regards
Zhu Chao.
www.cnoug.org

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, October 28, 2003 1:24 PM

> 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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: chao_ping_at_vip.163.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).