今天遇到了一个与CBO有关的案例。一个表的列的最大/最小值变化后,CBO由于缺少信息(没有重新收集统计信息或者设置列的最大最小值)而导致执行计划出错。
[@more@]表: AVLATTRIBVALUESBK
索引:
IDX_AVLATTRIBVALUES_MEMBERIDBK - AVLATTRIBVALUESBK(memberid,dataareaid)
IDX_AVLATTRIBVALUES_CAMPBK - AVLATTRIBVALUESBK(PROCESSCAMPAIGN)
该表的PROCESSCAMPAIGN列中全部为唯一的值201102。正常情况下SQL的执行计划和效率如下:
SQL> SELECT mbr.dataareaid, mbr.memberid accountno, mbr.leaderid,
2 atr.processcampaign yyyycc, TO_NUMBER (atr.field29) net_sls,
3 TO_NUMBER (atr.field24) net_rtn, TO_NUMBER (atr.field17) orders,
4 atr.field16 rem_code, TO_NUMBER (atr.field1) awd_sales,
5 atr.field25 tr_lvl,
6 DECODE ('', 'F', mbr.roleid, NVL (atr.attroleid, mbr.roleid)) ROLE,
7 mbr.memberstatus, atr.attroleid currrole,
8 TO_NUMBER (atr.field82) avldrloa, TO_NUMBER (atr.field83) avactivefsb,
9 TO_NUMBER (atr.field84) avnetsalesfsb,
10 TO_NUMBER (atr.field85) avtotactivefsb,
11 TO_NUMBER (atr.field86) avtotnetsalesfsb
12 FROM avlattribvaluesbk atr, avlmembers mbr
13 WHERE atr.dataareaid = mbr.dataareaid
14 AND atr.memberid = mbr.memberid
15 AND mbr.dataareaid = '825'
16 AND atr.processcampaign = 201102
17 AND mbr.leaderid ='60000137447' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2402233046
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1200 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | AVLATTRIBVALUESBK | 1 | 109 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 1200 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| AVLMEMBERS | 8 | 328 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MEMBER_LEADERID | 8 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_AVLATTRIBVALUES_MEMBERIDBK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ATR"."PROCESSCAMPAIGN"=201102)
4 - access("MBR"."LEADERID"='60000137447' AND "MBR"."DATAAREAID"='825')
5 - access("ATR"."MEMBERID"="MBR"."MEMBERID" AND "ATR"."DATAAREAID"='825')
Statistics
----------------------------------------------------------
1745 recursive calls
0 db block gets
482 consistent gets
0 physical reads
0 redo size
1281 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autotrace off;
由于应用每个月的开头都要将processcampagin字段修改为当月,比如201103。那么修改后就发现CBO使用了错误的执行计划,效率降低很多。
SQL> update SYSTEM.AVLATTRIBVALUESBK set PROCESSCAMPAIGN=201103;
SQL> commit;
Commit complete.
SQL>
SQL> set autotrace on;
SQL> SELECT mbr.dataareaid, mbr.memberid accountno, mbr.leaderid,
2 atr.processcampaign yyyycc, TO_NUMBER (atr.field29) net_sls,
3 TO_NUMBER (atr.field24) net_rtn, TO_NUMBER (atr.field17) orders,
4 atr.field16 rem_code, TO_NUMBER (atr.field1) awd_sales,
5 atr.field25 tr_lvl,
6 DECODE ('', 'F', mbr.roleid, NVL (atr.attroleid, mbr.roleid)) ROLE,
7 mbr.memberstatus, atr.attroleid currrole,
8 TO_NUMBER (atr.field82) avldrloa, TO_NUMBER (atr.field83) avactivefsb,
9 TO_NUMBER (atr.field84) avnetsalesfsb,
10 TO_NUMBER (atr.field85) avtotactivefsb,
11 TO_NUMBER (atr.field86) avtotnetsalesfsb
12 FROM avlattribvaluesbk atr, avlmembers mbr
13 WHERE atr.dataareaid = mbr.dataareaid
14 AND atr.memberid = mbr.memberid
15 AND mbr.dataareaid = '825'
16 AND atr.processcampaign = 201103
17 AND mbr.leaderid ='60000137447' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 470956695
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 150 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 150 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| AVLATTRIBVALUESBK | 1 | 109 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_AVLATTRIBVALUES_CAMPBK | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| AVLMEMBERS | 1 | 41 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | IDX_MEMBER_MEMBERID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ATR"."DATAAREAID"='825')
3 - access("ATR"."PROCESSCAMPAIGN"=201103)
4 - filter("MBR"."LEADERID"='60000137447')
5 - access("ATR"."MEMBERID"="MBR"."MEMBERID" AND "MBR"."DATAAREAID"='825')
Statistics
----------------------------------------------------------
1756 recursive calls
0 db block gets
189109 consistent gets
4446 physical reads
10196 redo size
1281 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
0 rows processed
CBO判断201103不在列的最大/最小值范围内,于是选用IDX_AVLATTRIBVALUES_CAMPBK 索引访问表AVLATTRIBVALUESBK。
解决方法:
1. 重新收集该表AVLATTRIBVALUESBK的统计信息。
or
2. 设置AVLATTRIBVALUESBK列processcampagin的最大/最小值为201103
SQL> DECLARE
2 mysrec SYS.DBMS_STATS.STATREC;
3 datevals SYS.DBMS_STATS.numarray;
4 min_val number;
5 max_val number;
6 BEGIN
7 select min(201103),max(201103) into min_val,max_val from dual;
8 datevals := SYS.DBMS_STATS.numarray(min_val,max_val);
9 mysrec.EPC := 2;
10 sys.dbms_stats.prepare_column_values(mysrec,datevals);
11 sys.dbms_stats.set_column_stats (ownname=>'SYSTEM',tabname=>'AVLATTRIBVALUESBK',colname=>'PROCESSCAMPAIGN', srec=>mysrec);
12 COMMIT;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL> select owner,table_name,column_name,conv_raw(low_value,'NUMBER'),conv_raw(high_value,'NUMBER')
2 from dba_tab_col_statistics where table_name='AVLATTRIBVALUESBK' and column_name='PROCESSCAMPAIGN';
OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
CONV_RAW(LOW_VALUE,'NUMBER')
----------------------------------------------------------------------------------------------------------------------------------
CONV_RAW(HIGH_VALUE,'NUMBER')
----------------------------------------------------------------------------------------------------------------------------------
SYSTEM AVLATTRIBVALUESBK PROCESSCAMPAIGN
201103
201103
通过上面两个方法让CBO知道processcampagin的最大/最小值后,CBO就能选用回原来正确的执行计划了。
SQL> set autotrace on;
SQL> SELECT mbr.dataareaid, mbr.memberid accountno, mbr.leaderid,
2 atr.processcampaign yyyycc, TO_NUMBER (atr.field29) net_sls,
3 TO_NUMBER (atr.field24) net_rtn, TO_NUMBER (atr.field17) orders,
4 atr.field16 rem_code, TO_NUMBER (atr.field1) awd_sales,
5 atr.field25 tr_lvl,
6 DECODE ('', 'F', mbr.roleid, NVL (atr.attroleid, mbr.roleid)) ROLE,
7 mbr.memberstatus, atr.attroleid currrole,
8 TO_NUMBER (atr.field82) avldrloa, TO_NUMBER (atr.field83) avactivefsb,
9 TO_NUMBER (atr.field84) avnetsalesfsb,
10 TO_NUMBER (atr.field85) avtotactivefsb,
11 TO_NUMBER (atr.field86) avtotnetsalesfsb
12 FROM avlattribvaluesbk atr, avlmembers mbr
13 WHERE atr.dataareaid = mbr.dataareaid
14 AND atr.memberid = mbr.memberid
15 AND mbr.dataareaid = '825'
16 AND atr.processcampaign = 201103
17 AND mbr.leaderid ='60000137447' ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2402233046
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1200 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | AVLATTRIBVALUESBK | 1 | 109 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 1200 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| AVLMEMBERS | 8 | 328 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MEMBER_LEADERID | 8 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_AVLATTRIBVALUES_MEMBERIDBK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ATR"."PROCESSCAMPAIGN"=201103)
4 - access("MBR"."LEADERID"='60000137447' AND "MBR"."DATAAREAID"='825')
5 - access("ATR"."MEMBERID"="MBR"."MEMBERID" AND "ATR"."DATAAREAID"='825')
Statistics
----------------------------------------------------------
2160 recursive calls
0 db block gets
580 consistent gets
16 physical reads
0 redo size
1281 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
50 sorts (memory)
0 sorts (disk)
0 rows processed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25491501/viewspace-1046915/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25491501/viewspace-1046915/