Histograms Hindering in chosing optimal access path by CBO.
Hi All,
The index is getting used if i just analyze the table with buckets 1 for COLS stats, where as same is going for FTS when i take STATS of the cols with 50/75/100 buckets.
PS below
Code:
WW04_PRO_DBA> analyze table shipment_2002 delete statistics;
Table analyzed.
WW04_PRO_DBA> analyze table shipment_2002 estimate statistics sample 40 percent;
Table analyzed.
WW04_PRO_DBA> Select
2 TABLE_NAME ,
3 NUM_ROWS ,
4 BLOCKS ,
5 EMPTY_BLOCKS ,
6 AVG_SPACE ,
7 CHAIN_CNT ,
8 AVG_ROW_LEN ,
9 AVG_SPACE_FREELIST_BLOCKS ,
10 SAMPLE_SIZE ,
11 LAST_ANALYZED
12 From
13 dba_tables
14 Where
15 Table_Name = 'SHIPMENT_2002'
16 /
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
--------------- ---------- -------- -------------- ----------- ----------- ------------- ------------ ---------
SHIPMENT_2002 1401935 20784 975 874 0 105 460658 11-SEP-03
WW04_PRO_DBA> ed
Wrote file afiedt.buf
1 Select
2 TABLE_NAME ,
3 COLUMN_NAME ,
4 NUM_DISTINCT ,
5 LOW_VALUE ,
6 HIGH_VALUE ,
7 DENSITY ,
8 NUM_NULLS ,
9 NUM_BUCKETS ,
10 LAST_ANALYZED ,
11 SAMPLE_SIZE
12 from
13 dba_tab_columns
14 where
15* Table_Name = 'SHIPMENT_2002'
WW04_PRO_DBA> /
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS SAMPLE_SIZE
---------------- ---------------------- ------------- ------------------------ -------------------------- -------------------- ------------- -------------
SHIPMENT_2002 SLS_ORG_CD 76 41455331 55535645 .0131578947368421 1 460658
SHIPMENT_2002 SLS_ORD_NUMBER 181091 30303030313631343333 49313433303932303530 5.52208558128234E-06 1 460658
SHIPMENT_2002 SHIPID 325636 30303231313333313837 41333733373434323838 .0000030709135353585 1 460658
SHIPMENT_2002 SLS_ORD_LINE_ITEM_NM 1413 303030303031 313232303031 .000707714083510262 1 460658
SHIPMENT_2002 PART_NUMBER 2879 3130333235302D423234 525A32394C2D454E .000347342827370615 1 460658
WW04_PRO_DBA> select * from shipment_2002 where part_number='ajsd';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=382 Card=487 Bytes=4
3830)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SHIPMENT_2002' (Cost=382
Card=487 Bytes=43830)
2 1 INDEX (RANGE SCAN) OF 'PK_SHIPMENT_2002' (UNIQUE) (Cost=
6 Card=487)
WW04_PRO_DBA> analyze table shipment_2002 estimate statistics sample 40 percent for all columns size 75;
Table analyzed.
WW04_PRO_DBA> Select
2 TABLE_NAME ,
3 COLUMN_NAME ,
4 NUM_DISTINCT ,
5 LOW_VALUE ,
6 HIGH_VALUE ,
7 DENSITY ,
8 NUM_NULLS ,
9 NUM_BUCKETS ,
10 LAST_ANALYZED ,
11 SAMPLE_SIZE
12 from
13 dba_tab_columns
14 where
15 Table_Name = 'SHIPMENT_2002';
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS SAMPLE_SIZE
---------------- ----------------------- ------------ ----------------------- ------------------------- -------------------- ------------ ------------
SHIPMENT_2002 SLS_ORG_CD 76 41455331 55535645 .00666666666666667 31 460658
SHIPMENT_2002 SLS_ORD_NUMBER 181091 30303030313631343333 49313433303932303530 .0000134466437177281 75 460658
SHIPMENT_2002 SHIPID 325636 30303231313333313837 41333733373434323838 .0000076974590687614 75 460658
SHIPMENT_2002 SLS_ORD_LINE_ITEM_NM 1413 303030303031 313232303031 .00666666666666667 42 460658
SHIPMENT_2002 PART_NUMBER 2879 3130333235302D423234 525A32394C2D454E .00587852006350807 68 460658
WW04_PRO_DBA> select * from shipment_2002 where part_number='ajsd';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1268 Card=8241 Bytes
=741690)
1 0 TABLE ACCESS (FULL) OF 'SHIPMENT_2002' (Cost=1268 Card=824
1 Bytes=741690)
I dunno why CBO sometimes acts crazyly..could any one explain this behaviour.
Thanks
Abhay.
