Oracle CBO 案例 1 - 列的最大、最小值对于执行计划的影响

最新推荐文章于 2021-04-16 02:19:18 发布

cuanchi6533

最新推荐文章于 2021-04-16 02:19:18 发布

阅读量207

点赞数

今天遇到了一个与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的统计信息。

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/

cuanchi6533

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle CBO 案例 1 - 列的最大、最小值对于执行计划的影响

今天遇到了一个与CBO有关的案例。一个表的列的最大/最小值变化后，CBO由于缺少信息（没有重新收集统计信息或者设置列的最大最小值）而导致执行计划出错。[@more@]表: AVLATTRIBVALUESBK索引：IDX_AVLA...
复制链接

扫一扫