----
Find out why SQL optimizer select a bad index access path when there are 2 predicates,
and each predicate column got an index created on it.
# SQL
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
INDEX PO_ITEM_POID ON PO_ITEM(POID)
INDEX PO_ITEM_STATUS_UPDDT ON PO_ITEM(STATUS, STATUSUPDDT)
It should access data by index on POID column, but it suddenly changed to use index on STATUS column.
Solution
--------
Understand CBO,experiment,observe and interpret.
With SQL session 10053 trace and dbms_xplan.display_cursor().
Result
------
When database collect histogram stats on a column, it calculate a new density for the column,
and it will be extreme smaller, CBO use density as selectivity.
After we hack the column density, database will clear the histogram stats,
the CBO will use 1/NumberOfDistinct, ignore density.
When we check the output of 10053 trace,
both IO costs are same, but index selectivity (ix_sel) and table selectivity (ix_sel_with_filters)
on Index: PO_ITEM_STATUS_RANDOM_UPDDT is smaller than than it on Index: PO_ITEM_POID.
Access Path: index (equal)
Index: PO_ITEM_POID
rsc_cpu: 17832 rsc_io: 3
ix_sel: 1.2670e-05 ix_sel_with_filters: 1.2670e-05
Access Path: index (index-only)
Index: PO_ITEM_STATUS_RANDOM_UPDDT
rsc_cpu: 17632 rsc_io: 3
ix_sel: 6.2422e-06 ix_sel_with_filters: 6.2422e-06
*) Fix: hack the density, make it accurate.
10053 trace output and dbms_xplan page link.
Experiment
----------
InBox
-----
Next Action
-----------
Complete
--------
create test table po_item
- evenly distribution Mod(), value from '01' to '20'
- random distribution dbms_random.value() '01' to '20'
- normal distribution dbms_random.normal() '01' to '99'
create index
collect table stats
collect column STATUS histogram stats
-- This step skipped: run SQL UPDATE to generate required data distribution
run SQL
- enable 10053 trace
- dbms_xplan.display_cursor()
- set autotrace on
*) Purchase_Order Item table
drop TABLE PO_ITEM;
CREATE TABLE PO_ITEM
(
POITEMID NUMBER(8),
POID NUMBER(8),
STATUS CHAR(2 BYTE),
STATUS_random CHAR(2 BYTE),
STATUS_normal CHAR(2 BYTE),
STATUSUPDDT DATE,
ITEMTITLE VARCHAR2(50 BYTE),
ITEMAUTHOR VARCHAR2(50 BYTE),
ITEMPRICE NUMBER(8,2)
)
nologging;
execute dbms_random.seed(0)
insert /*+ append */ into po_item
with g as (
select -- materialize
level
from dual
connect by level <= 2000
)
select
/*+ leading(v1,v2,v3) use_nl(v2) use_nl(v3) */
rownum
,Round(rownum/0.9,0)
,To_Char(Round(Mod(rownum,100)))
,To_Char(Round(dbms_random.value(1,20)))
,LTrim(To_Char(ABS(Mod(10 * dbms_random.normal,100)),'99'))
,To_Date('1999','YYYY') + rownum/86400
,rpad(rownum,50,'x')
,rpad(rownum,50,'*')
,Round(dbms_random.value(1,100),2)
from
g v1
,g v2
,g v3
where
rownum <= 80100;
commit;
exec dbms_stats.gather_table_stats(user,'PO_ITEM');
-- Index on POID column
CREATE INDEX PO_ITEM_POID ON PO_ITEM
(POID)
NoLogging;
-- Index on column STATUS and STATUS_UPD_DATE
CREATE INDEX PO_ITEM_STATUS_UPDDT ON PO_ITEM
(STATUS, STATUSUPDDT)
NoLogging;
-- Index on column STATUS_random and STATUS_UPD_DATE
CREATE INDEX PO_ITEM_STATUS_RANDOM_UPDDT ON PO_ITEM
(STATUS_random, STATUSUPDDT)
NoLogging;
exec dbms_stats.gather_table_stats(user,'PO_ITEM');
*) init parameter
alter system set optimizer_index_cost_adj=25;
alter system set optimizer_index_cost_adj=100;
alter system set optimizer_index_caching=0;
alter system set optimizer_index_caching=25;
alter system set optimizer_mode = FIRST_ROWS_10;
alter system flush shared_pool;
alter system flush BUFFER_CACHE;
set autot off
set serveroutput off
variable a NUMBER;
variable b VARCHAR2(2);
exec :a := 72036876;
exec :a := 70100;
exec :b := '98';
exec :b := '17';
*) enable 10053 trace
ALTER SESSION SET TRACEFILE_IDENTIFIER="i";
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
SELECT /* */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);
alter session set events '10053 trace name context off';
*) display SQL execution plan
-- uses the hint gather_plan_statistics to enable the generation of the execution statistics.
set linesize 300
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
SELECT * FROM table (Cast(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') as SYS.DBMS_XPLAN_TYPE_TABLE));
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS_random = :b ORDER BY poitemid;
SELECT * FROM table (Cast(dbms_xplan.display_cursor(NULL,NULL, 'runstats_last') as SYS.DBMS_XPLAN_TYPE_TABLE));
set autot trace exp
SELECT /*+ gather_plan_statistics */ * FROM po_item WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
-- gather histogram stats on column STATUS
begin
dbms_stats.gather_table_stats(
user,
'po_item',
cascade => true,
estimate_percent => 0.1,
method_opt => 'for columns size 254 status,status_random,status_normal size 100'
);
end;
/
-- hack column stats
begin
DBMS_STATS.SET_COLUMN_STATS (
ownname => USER,
tabname => 'PO_ITEM',
colname => 'STATUS',
-- distcnt => 20,
density => 0.05,
force => True
);
end;
/
-- Compare index access on right index
set autot trace
SELECT /*+ index(po_item,PO_ITEM_POID) */ * FROM PO_ITEM
WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
Reference
---------
*) origianl table and SQL:
abedba.ABEPOITEMS
SELECT /* */ * FROM ABEPOITEMS WHERE ABEPOID = :a AND STATUS = :b ORDER BY ABEPOITEMID;
*) Get stats
-- Display table stats
set autot off
set SQLPATH=A:\maint\monitor;A:\adm\maint
@table scott po_item
-- check CBO parameter
select name,value,isdefault from v$sql_optimizer_env
where sql_id = 'cp44zcmqma34f';
-- Query to check the value of input hidden "parameter_name"
col indx format 9999
col inst_id heading "INST" format 9999
col ksppinm heading "NAME" format a30
col ksppdesc heading "DESC" format a40
col ksppstvl heading "CURR VAL" format a15
col ksppstdvl heading "DEFAULT VAL" format a15
set echo on
set autot off
select v.indx,v.inst_id,ksppinm p_name,ksppstvl curr_val,ksppstdvl default_val,ksppdesc p_desc
from x$ksppi i ,x$ksppcv v
where i.indx=v.indx and ksppinm like '%sort_elimination_cost_ratio%';
-- display real value for low_value and high_value on view user_tab_col_statistics
DECLARE
l_low_value user_tab_col_statistics.low_value%TYPE;
l_high_value user_tab_col_statistics.high_value%TYPE;
l_val1 ABEPOITEMS.STATUS%TYPE;
BEGIN
SELECT low_value, high_value
INTO l_low_value, l_high_value
FROM user_tab_col_statistics
WHERE table_name = 'PO_ITEM'
AND column_name = 'STATUS';
dbms_stats.convert_raw_value(l_low_value, l_val1);
dbms_output.put_line('low_value: ' || l_val1);
dbms_stats.convert_raw_value(l_high_value, l_val1);
dbms_output.put_line('high_value: ' || l_val1);
dbms_output.put_line('high_value: ' || l_low_value||', '||l_high_value);
END;
/
exec DBMS_STATS.UnLOCK_TABLE_STATS(user,'ABEPOitemS');
-- Verify data distribution
select status,count(*)
from abedba.po_item
group by status;
STATUS COUNT(*)
01 2795
04 619
05 252
10 1334827
11 7298222
12 232920
14 313380
15 1912919
16 8764
17 233443
18 116935
19 549803
24 748465
25 356031
35 12905639
45 7594712
50 235
51 75338
52 334234
53 22
54 22325
55 5110
56 12311
60 3
66 245
90 215
97 54017
98 83
--
*) hack object stats
-- hack index stats. --old 3931, new 40100
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => USER,
indname => 'PO_ITEM_POID',
clstfct => 50);
end;
/
begin
DBMS_STATS.SET_INDEX_STATS (
ownname => 'SCOTT',
indname => 'PO_ITEM_STATUS_UPDDT',
clstfct => 1000);
end;
/
-- hack column stats
begin
DBMS_STATS.SET_COLUMN_STATS (
ownname => USER,
tabname => 'PO_ITEM',
colname => 'STATUS',
density => 0.05);
end;
/
*) Export and Import the table stats
begin
dbms_stats.create_stat_table(ownname => user,
stattab => 'MYSTATS',
tblspace => 'USERS');
end;
/
begin
DBMS_STATS.EXPORT_TABLE_STATS (
ownname => USER,
tabname => 'PO_ITEM',
stattab => 'MYSTATS');
end;
/
exec dbms_stats.delete_table_stats(ownname => user,tabname => 'PO_ITEM')
begin
DBMS_STATS.IMPORT_TABLE_STATS (
ownname => USER,
tabname => 'PO_ITEM',
stattab => 'MYSTATS',
force => true);
end;
/
*) others
-- Tune sort index over the access predicate index
alter session set "_sort_elimination_cost_ratio" = 0;
SELECT /*x index(po_item,PO_ITEM_STATUS_UPDDT) */ * FROM PO_ITEM
WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
alter session set "_sort_elimination_cost_ratio" = 1;
SELECT /*x index(po_item,PO_ITEM_STATUS_UPDDT) */ * FROM PO_ITEM
WHERE poid = :a AND STATUS = :b ORDER BY poitemid;
QingDao, China
No comments:
Post a Comment