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" <[EMAIL PROTECTED]>
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: [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: zhu chao
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).
