| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1027008539.1355.1.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> Oh dear -
> I have to contradict you twice in one day.
>
> Having done a bit of stress-testing and generally
> having fun with the optimisers, I can tell you that
> one test involving 255 tables gave me an optimisation
> time of:
> Rule 45 minutes 22 seconds
> all_rows 3.04 seconds
> first_rows 3.04 seconds
>
>
> The CBO usually finds optimal paths in complex
> joins despite the best efforts of designers and
> dbas to confuse the issue with unsuitable indexing
> strategies, inappropriate statistics, and misleading
> resource definitions.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
Well, I'm still struggling to figure out why RBO typically beats CBO on our system. Below is a typical example. The query runs nicely with simple nested loops, but CBO never seems smart enough to figure that out.
In the example below, I've reversed the order of the tables in the FROM clause from RBO to CBO.
Some of my parameters are:
optimizer_index_cost_adj: 100
optimizer_index_caching: 0
db_file_multiblock_read_count: 32
db_block_size: 16384
Thanks for any insight.
--steve
company_24_at_dilbert>
company_24_at_dilbert> SELECT
2 count(*)
3 FROM
4 Ic_Product_Id ProdId,
5 Fc_Unit_Of_Measure UOM,
6 Ic_Product Prod,
7 Fc_Unit_Of_Measure PUOM,
8 Ic_Ps_Size PSize,
9 Ic_Product_Produce ProdP,
10 Fc_Name_Location Loc,
11 Ga_Lot Lot,
12 Ga_Block Block,
13 Ic_Tag Tag,
14 Ic_Warehouse,
15 Ic_Inventory IC,
16 Ar_Trx_Product IProd,
17 Ar_Trx_Detail IDtl,
18 Ar_Trx_Line IL,
19 AR_TRX_HEADER_SHIP,
20 Ar_Trx_Header
21 WHERE
22 IL.ArTrxLineTrxType = '1' AND
23 IL.LineType = '1' AND
24 IProd.FillType IN ('2','7') AND
25 IDtl.GLDELETECODE = 'N' AND
26 IDtl.ArTrxHdrIdx = IL.ArTrxHdrIdx AND
27 IDtl.ArTrxLineSeq = IL.ArTrxLineSeq AND
28 IDtl.ArTrxLineTrxType = IL.ArTrxLineTrxType AND
29 IProd.ArTrxHdrIdx = IDtl.ArTrxHdrIdx AND
30 IProd.ArTrxDtlSeq = IDtl.ArTrxDtlSeq AND
31 IDtl.ShipHdrIdx = AR_TRX_HEADER_SHIP.ShipHdrIdx AND
32 IC.ProductIdx = Prod.ProductIdx (+) AND
33 ProdId.ProductIdx = IC.ProductIdx AND
34 IC.ProductIdx = ProdP.ProductIdx (+) AND
35 ProdP.SizeIdx = PSize.SizeIdx (+) AND
36 UOM.UomIdx (+) = Prod.UomIdx AND
37 PUOM.UomIdx (+) = PSize.UomIdx AND
38 IProd.InventoryIdx = IC.InventoryIdx AND
39 IC.TagIdx = Tag.TagIdx AND
40 IC.WarehouseIdx = IC_WAREHOUSE.WarehouseIdx AND
41 IC_WAREHOUSE.NameIdx = Loc.NameIdx AND
42 IC_WAREHOUSE.NameLocationSeq = Loc.NameLocationSeq AND
43 IC.GaLotIdx = Lot.GaLotIdx (+) AND
44 IC.GaBlockIdx = Block.GaBlockIdx (+) AND
45 AR_TRX_HEADER_SHIP.artrxhdridx = AR_TRX_HEADER.artrxhdridx AND
46 IL.ArTrxHdrIdx = AR_TRX_HEADER.ArTrxHdrIdx AND
47 AR_TRX_HEADER.SOSTATUS >= '6' AND
48 ( AR_TRX_HEADER.SHIPDATETIME BETWEEN TO_DATE('02/01/2002 04:00:00',
'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('02/28/2002 03:59:59', 'MM/DD/YYYY
HH24:MI:SS') ) AND
COUNT(*)
13286
Execution Plan
0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS (OUTER)
7 6 NESTED LOOPS (OUTER)
8 7 NESTED LOOPS
9 8 NESTED LOOPS (OUTER)
10 9 NESTED LOOPS (OUTER)
11 10 NESTED LOOPS
12 11 NESTED LOOPS
13 12 NESTED LOOPS
14 13 NESTED LOOPS
15 14 NESTED LOOPS
16 15 NESTED LOOPS
17 16 NESTED LOOPS
18 17 TABLE ACCESS (BY INDEX R
OWID) OF 'AR_TRX_HEADER'
19 18 INDEX (RANGE SCAN) OF
'XIE4AR_TRX_HEADER' (NON-UNIQUE)
20 17 TABLE ACCESS (BY INDEX R
OWID) OF 'AR_TRX_HEADER_SHIP'
21 20 INDEX (RANGE SCAN) OF
'XIF6902AR_TRX_HEADER_SHIP' (NON-UNIQUE)
22 16 TABLE ACCESS (BY INDEX ROW
ID) OF 'AR_TRX_LINE'
23 22 INDEX (RANGE SCAN) OF 'X
IF6095AR_TRX_LINE' (NON-UNIQUE)
24 15 TABLE ACCESS (BY INDEX ROWID
) OF 'AR_TRX_DETAIL'
25 24 INDEX (RANGE SCAN) OF 'XIF
6010AR_TRX_DETAIL' (NON-UNIQUE)
26 14 TABLE ACCESS (BY INDEX ROWID)
OF 'AR_TRX_PRODUCT'
27 26 INDEX (UNIQUE SCAN) OF 'XPKA
R_TRX_PRODUCT' (UNIQUE)
28 13 TABLE ACCESS (BY INDEX ROWID) OF
'IC_INVENTORY'
29 28 INDEX (UNIQUE SCAN) OF 'XPKIC_
INVENTORY' (UNIQUE)
30 12 TABLE ACCESS (BY INDEX ROWID) OF '
IC_WAREHOUSE'
31 30 INDEX (UNIQUE SCAN) OF 'XPKIC_WA
REHOUSE' (UNIQUE)
32 11 INDEX (UNIQUE SCAN) OF 'XPKIC_TAG' (
UNIQUE)
33 10 INDEX (UNIQUE SCAN) OF 'XPKGA_BLOCK' (
UNIQUE)
34 9 INDEX (UNIQUE SCAN) OF 'XPKGA_LOT' (UNIQ
UE)
35 8 INDEX (UNIQUE SCAN) OF 'XPKFC_NAME_LOCATIO
N' (UNIQUE)
36 7 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PRODUCT
_PRODUCE'
37 36 INDEX (UNIQUE SCAN) OF 'XPKIC_PRODUCT_PROD
UCE' (UNIQUE)
38 6 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PS_SIZE'
39 38 INDEX (UNIQUE SCAN) OF 'XPKIC_PS_SIZE' (UNIQ
UE)
40 5 INDEX (UNIQUE SCAN) OF 'XPKFC_UNIT_OF_MEASURE' (
UNIQUE)
41 4 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PRODUCT'
42 41 INDEX (UNIQUE SCAN) OF 'XPKIC_PRODUCT' (UNIQUE)
43 3 INDEX (UNIQUE SCAN) OF 'XPKFC_UNIT_OF_MEASURE' (UNIQ
UE)
44 2 INDEX (UNIQUE SCAN) OF 'XPKIC_PRODUCT_ID' (UNIQUE)
Statistics
0 recursive calls
0 db block gets
1036744 consistent gets
0 physical reads
0 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
company_24_at_dilbert>
4 Ar_Trx_Header,
5 AR_TRX_HEADER_SHIP,
6 Ar_Trx_Line IL,
7 Ar_Trx_Detail IDtl,
8 Ar_Trx_Product IProd,
9 Ic_Inventory IC,
10 Ic_Warehouse,
11 Ic_Tag Tag,
12 Ga_Block Block,
13 Ga_Lot Lot,
14 Fc_Name_Location Loc,
15 Ic_Product_Produce ProdP,
16 Ic_Ps_Size PSize,
17 Fc_Unit_Of_Measure PUOM,
18 Ic_Product Prod,
19 Fc_Unit_Of_Measure UOM,
20 Ic_Product_Id ProdId
21 WHERE
22 IL.ArTrxLineTrxType = '1' AND
23 IL.LineType = '1' AND
24 IProd.FillType IN ('2','7') AND
25 IDtl.GLDELETECODE = 'N' AND
26 IDtl.ArTrxHdrIdx = IL.ArTrxHdrIdx AND
27 IDtl.ArTrxLineSeq = IL.ArTrxLineSeq AND
28 IDtl.ArTrxLineTrxType = IL.ArTrxLineTrxType AND
29 IProd.ArTrxHdrIdx = IDtl.ArTrxHdrIdx AND
30 IProd.ArTrxDtlSeq = IDtl.ArTrxDtlSeq AND
31 IDtl.ShipHdrIdx = AR_TRX_HEADER_SHIP.ShipHdrIdx AND
32 IC.ProductIdx = Prod.ProductIdx (+) AND
33 ProdId.ProductIdx = IC.ProductIdx AND
34 IC.ProductIdx = ProdP.ProductIdx (+) AND
35 ProdP.SizeIdx = PSize.SizeIdx (+) AND
36 UOM.UomIdx (+) = Prod.UomIdx AND
37 PUOM.UomIdx (+) = PSize.UomIdx AND
38 IProd.InventoryIdx = IC.InventoryIdx AND
39 IC.TagIdx = Tag.TagIdx AND
40 IC.WarehouseIdx = IC_WAREHOUSE.WarehouseIdx AND
41 IC_WAREHOUSE.NameIdx = Loc.NameIdx AND
42 IC_WAREHOUSE.NameLocationSeq = Loc.NameLocationSeq AND
43 IC.GaLotIdx = Lot.GaLotIdx (+) AND
44 IC.GaBlockIdx = Block.GaBlockIdx (+) AND
45 AR_TRX_HEADER_SHIP.artrxhdridx = AR_TRX_HEADER.artrxhdridx AND
46 IL.ArTrxHdrIdx = AR_TRX_HEADER.ArTrxHdrIdx AND
47 AR_TRX_HEADER.SOSTATUS >= '6' AND
48 ( AR_TRX_HEADER.SHIPDATETIME BETWEEN TO_DATE('02/01/2002 04:00:00',
'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('02/28/2002 03:59:59', 'MM/DD/YYYY
HH24:MI:SS') ) AND
COUNT(*)
13286
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=9069 Card=1 By
tes=176)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=9069 Card=1 Bytes=176)
3 2 NESTED LOOPS (OUTER) (Cost=9069 Card=1 Bytes=171)
4 3 NESTED LOOPS (OUTER) (Cost=9069 Card=1 Bytes=168)
5 4 NESTED LOOPS (OUTER) (Cost=9068 Card=1 Bytes=161)
6 5 NESTED LOOPS (Cost=9067 Card=1 Bytes=153)
7 6 HASH JOIN (Cost=9062 Card=5 Bytes=715)
8 7 TABLE ACCESS (FULL) OF 'AR_TRX_HEADER' (Cost
=17 Card=310 Bytes=5270)
9 7 HASH JOIN (Cost=9043 Card=122 Bytes=15372)
10 9 TABLE ACCESS (FULL) OF 'AR_TRX_LINE' (Cost
=18 Card=9743 Bytes=116916)
11 9 MERGE JOIN (Cost=8856 Card=11541 Bytes=131
5674)
12 11 SORT (JOIN) (Cost=8036 Card=102703 Bytes
=9859488)
13 12 NESTED LOOPS (Cost=3295 Card=102703 By
tes=9859488)
14 13 MERGE JOIN (Cost=3295 Card=102703 By
tes=9448676)
15 14 SORT (JOIN) (Cost=2628 Card=68875
Bytes=5234500)
16 15 NESTED LOOPS (OUTER) (Cost=54 Ca
rd=68875 Bytes=5234500)
17 16 NESTED LOOPS (OUTER) (Cost=54
Card=68875 Bytes=4959000)
18 17 NESTED LOOPS (OUTER) (Cost=5
4 Card=68875 Bytes=4683500)
19 18 NESTED LOOPS (OUTER) (Cost
=54 Card=68875 Bytes=4476875)
20 19 HASH JOIN (Cost=54 Card=
68875 Bytes=2686125)
21 20 NESTED LOOPS (Cost=1 C
ard=28 Bytes=448)
22 21 TABLE ACCESS (FULL)
OF 'IC_WAREHOUSE' (Cost=1 Card=28 Bytes=252)
23 21 INDEX (UNIQUE SCAN)
OF 'XPKFC_NAME_LOCATION' (UNIQUE)
24 20 TABLE ACCESS (FULL) OF
'IC_INVENTORY' (Cost=19 Card=68875 Bytes=1584125)
25 19 TABLE ACCESS (BY INDEX R
OWID) OF 'IC_PRODUCT'
26 25 INDEX (UNIQUE SCAN) OF
'XPKIC_PRODUCT' (UNIQUE)
27 18 INDEX (UNIQUE SCAN) OF 'XP
KFC_UNIT_OF_MEASURE' (UNIQUE)
28 17 INDEX (UNIQUE SCAN) OF 'XPKG
A_BLOCK' (UNIQUE)
29 16 INDEX (UNIQUE SCAN) OF 'XPKGA_
LOT' (UNIQUE)
30 14 SORT (JOIN) (Cost=667 Card=74785 B
ytes=1196560)
31 30 TABLE ACCESS (FULL) OF 'AR_TRX_P
RODUCT' (Cost=27 Card=74785 Bytes=1196560)
32 13 INDEX (UNIQUE SCAN) OF 'XPKIC_PRODUC
T_ID' (UNIQUE)
33 11 SORT (JOIN) (Cost=821 Card=70293 Bytes=1
265274)
34 33 TABLE ACCESS (FULL) OF 'AR_TRX_DETAIL'
(Cost=174 Card=70293 Bytes=1265274)
35 6 TABLE ACCESS (BY INDEX ROWID) OF 'AR_TRX_HEADE
R_SHIP' (Cost=1 Card=18292 Bytes=182920)
36 35 INDEX (UNIQUE SCAN) OF 'XPKAR_TRX_HEADER_SHI
P' (UNIQUE)
37 5 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PRODUCT_PRO
DUCE' (Cost=1 Card=4454 Bytes=35632)
38 37 INDEX (UNIQUE SCAN) OF 'XPKIC_PRODUCT_PRODUCE'
(UNIQUE)
39 4 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PS_SIZE' (Cos
t=1 Card=1272 Bytes=8904)
40 39 INDEX (UNIQUE SCAN) OF 'XPKIC_PS_SIZE' (UNIQUE)
41 3 INDEX (UNIQUE SCAN) OF 'XPKFC_UNIT_OF_MEASURE' (UNIQ
UE)
42 2 INDEX (UNIQUE SCAN) OF 'XPKIC_TAG' (UNIQUE)
Statistics
40 recursive calls
1163 db block gets
443855 consistent gets
24159 physical reads
0 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
4 sorts (disk)
1 rows processed
company_24_at_dilbert> Received on Thu Jul 18 2002 - 17:18:07 CDT
 |
 |