Re: SQL optimization

"zeb" <thierry.constant1.nospam_at_free.fr> a écrit dans le message de news: 3f7e822e$0$13302$626a54ce_at_news.free.fr...
>
> Hi,
>
> The request is long with CBO and very quick with RBO ( the stats are
> uptodate )
> Do you know why I have a full table scan whith CBO ?
>
> Thanks in advance
> Oracle 8.1.7.4
>
> 1 SELECT sales_orders_fact.sales_order_number sales_order_number,
> 2 sales_orders_fact.sales_line_item_id sales_line_item_id,
> 3 sched_lines_fact.schedule_line_id schedule_line_id,
> 4 MAX (e_mrp_result.j_2cstat) e_mrpstatus,
> 5 SUM (e_mrp_result.menge) e_mrpalloc
> 6 FROM e_mrp_result, sched_lines_fact, sales_orders_fact
> 7 WHERE sales_orders_fact.sales_order_number =
> 8
> sched_lines_fact.sales_order_num
> er
> 9 AND sales_orders_fact.sales_line_item_id =
> 10
> sched_lines_fact.sales_line_item
> id
> 11 AND e_mrp_result.aufnr = sched_lines_fact.sales_order_number
> 12 AND e_mrp_result.hppos = sched_lines_fact.sales_line_item_id
> 13 AND e_mrp_result.j_2cepos = sched_lines_fact.schedule_line_id
> 14 AND e_mrp_result.werks = sales_orders_fact.plant_id
> 15 AND e_mrp_result.bdart = 'KD'
> 16 AND e_mrp_result.j_2cbskz = 'C'
> 17 AND sales_orders_fact.e_load_mode = 'B'
> 18 GROUP BY sales_orders_fact.sales_order_number,
> 19 sales_orders_fact.sales_line_item_id,
> 20* sched_lines_fact.schedule_line_id
>
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=710 Card=1 Bytes=78)
> 1 0 SORT (GROUP BY) (Cost=710 Card=1 Bytes=78)
> 2 1 HASH JOIN (Cost=708 Card=1 Bytes=78)
> 3 2 NESTED LOOPS (Cost=623 Card=1985 Bytes=83370)
> 4 3 INDEX (FAST FULL SCAN) OF 'SCHED_LINES_FACT_PK' (UNI
> QUE) (Cost=4 Card=2512564 Bytes=50251280)
>
> 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'SALES_ORDERS_FACT'
> (Cost=623 Card=1282061 Bytes=28205342)
>
> 6 5 BITMAP CONVERSION (TO ROWIDS)
> 7 6 BITMAP AND
> 8 7 BITMAP CONVERSION (FROM ROWIDS)
> 9 8 INDEX (RANGE SCAN) OF 'SALES_ORDERS_FACT_PK'
> (UNIQUE) (Cost=10)
>
> 10 7 BITMAP INDEX (SINGLE VALUE) OF 'SALES_ORDERS_F
> ACT_IDX03'
>
> 11 2 TABLE ACCESS (FULL) OF 'E_MRP_RESULT' (Cost=53 Card=23
> 50 Bytes=84600)
>
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: RULE
> 1 0 SORT (GROUP BY)
> 2 1 NESTED LOOPS
> 3 2 NESTED LOOPS
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'E_MRP_RESULT'
> 5 4 INDEX (RANGE SCAN) OF 'E_MRP_RESULT_IDX01' (NON-UN
> IQUE)
>
> 6 3 INDEX (RANGE SCAN) OF 'SCHED_LINES_FACT_PK' (UNIQUE)
> 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'SALES_ORDERS_FACT'
> 8 7 INDEX (UNIQUE SCAN) OF 'SALES_ORDERS_FACT_PK' (UNIQU
> E)
>
>
>
>
>

Hello,

It is not so easy to answer such a question ;) Especially when one doesn't know the whole operating context of your DB ;)

Seems to be DSS-DW stuff, doesn't it ?

Your full accessed table contains lots of rows ? If I read well, 2350 is not so many rows especially in Data warehouses. If not, it can be an element of the explanation, CBO thinks it would be more expensive to access the table by any index (this is a commonly observed situation)

How long does your query take to complete ? If users and system engineers are OK with throughput time and resource consumption, no matter to further optimizing that code and using hints to force RBO evaluation is not so bad (but not elegant let's say ;) If not, try to use M-Views and enable query rewrite (careful, it requires some careful planning and testing to work fine) and try to stabilize your RBO plan using strored outlines.

Prior to settle that field artillery, please, TKPROF well your query, i.e. identifying which steps of the execution plan are returning or selecting most of the rows, both for RBO and CBO.

Sometimes, when I have to optimize a query with CBO, I simply reorder predicates of the WHERE clause and the query works better by getting a better evaluated explan.

Hope I could have helped you.

C U
Seb Received on Sun Oct 05 2003 - 13:54:14 CDT