爱站程序员基地今天遇到了一个与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/
