cardinality feedback(基数反馈)

优化器在很多原因下会错误的评估cardinalities,比如丢失统计信息、统计信息不准确或者复杂的谓词条件。在这种情况下,动态采样和extended statistics会帮助优化器
准确的评估谓词连接的选择性。除此之外,Oracle优化器在11gR2中又引入了cardinality feedback这项技术(对于sys用户默认是禁止该特性的)。此项新技术只对重复执行的语句有效,在SQL第一次执行时,记录存储实际的Cardinality 和评估的Cardinality之间的差异,如果差异较大,在第二次执行时,优化器会依据实际的Cardinality 重新决策生成执行计划,但是需要注意的是,当使用更准确的Cardinality重新生成执行计划时,不一定生成的执行计划与第一次时不一样,完全有可能是相同的。

实验环境11.2.0.4

脚本
drop table t1 purge;
drop table t2 purge;
drop table tmp purge;

create table tmp as select rownum id from dual connect by level < 10000;

create table t1 
as 
select rownum id,
       mod(rownum,5) val1,
       mod(rownum,5000) val2
from tmp v1,tmp v2 
where rownum < 1000000;

create table t2
as 
select 
       rownum id,
       mod(rownum,5) val1,
       mod(rownum,4000) val2
from tmp v1,tmp v2 
where rownum < 1000000; 
alter table t1 add constraint pk_id_t1 primary key(id); 
alter table t2 add constraint pk_id_t2 primary key(id); 
begin     
    dbms_stats.gather_table_stats(        
        ownname      => user,
        tabname      =>'T1',
        method_opt   => 'for all columns size 1'
    );
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T2',
        method_opt   => 'for all columns size 1'
    );
end;
/

select /*+ gather_plan_statistics */
    t1.val1, t1.val2, t2.val1, t2.val2
from
    t1, t2
where
    t1.val1 = 2
and t1.val2 = 4002
and t2.id = t1.id
and t2.val1 = 2
and t2.val2 = 3002
;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

update t1 set val2 = 4002 where val2 between 4004 and 4015;
update t2 set val2 = 3002 where val2 between 3005 and 4057;
commit;

select /*+ gather_plan_statistics */
    t1.val1, t1.val2, t2.val1, t2.val2
from
    t1, t2
where
    t1.val1 = 2
and t1.val2 = 4002
and t2.id = t1.id
and t2.val1 = 2
and t2.val2 = 3002;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

更新前的执行计划如下

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  a9592mqtc8yzn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */     t1.val1, t1.val2, t2.val1,
t2.val2 from     t1, t2 where     t1.val1 = 2 and t1.val2 = 4002 and
t2.id = t1.id and t2.val1 = 2 and t2.val2 = 3002

Plan hash value: 1945341087

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |     50 |00:00:00.01 |    2923 |      2 |
|   1 |  NESTED LOOPS                |          |      1 |     40 |     50 |00:00:00.01 |    2923 |      2 |
|   2 |   NESTED LOOPS               |          |      1 |     40 |    200 |00:00:00.04 |    2723 |      2 |
|*  3 |    TABLE ACCESS FULL         | T1       |      1 |     40 |    200 |00:00:00.03 |    2320 |      0 |
|*  4 |    INDEX UNIQUE SCAN         | PK_ID_T2 |    200 |      1 |    200 |00:00:00.01 |     403 |      2 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2       |    200 |      1 |     50 |00:00:00.01 |     200 |      0 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("T1"."VAL2"=4002 AND "T1"."VAL1"=2))
   4 - access("T2"."ID"="T1"."ID")
   5 - filter(("T2"."VAL2"=3002 AND "T2"."VAL1"=2))

进行记录的更新

SQL> update t1 set val2 = 4002 where val2 between 4004 and 4015;

2400 rows updated.

SQL> update t2 set val2 = 3002 where val2 between 3005 and 4057;

248750 rows updated.

SQL> commit;

Commit complete.

更新后的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
SQL_ID  a9592mqtc8yzn, child number 1
-------------------------------------
select /*+ gather_plan_statistics */     t1.val1, t1.val2, t2.val1,
t2.val2 from     t1, t2 where     t1.val1 = 2 and t1.val2 = 4002 and
t2.id = t1.id and t2.val1 = 2 and t2.val2 = 3002

Plan hash value: 1641483461

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    150 |00:00:00.01 |   61418 |      2 |
|   1 |  NESTED LOOPS                |          |      1 |     50 |    150 |00:00:00.01 |   61418 |      2 |
|   2 |   NESTED LOOPS               |          |      1 |     50 |  50000 |00:00:00.27 |   11418 |      2 |
|*  3 |    TABLE ACCESS FULL         | T2       |      1 |     50 |  50000 |00:00:00.20 |    2320 |      0 |
|*  4 |    INDEX UNIQUE SCAN         | PK_ID_T1 |  50000 |      1 |  50000 |00:00:00.05 |    9098 |      2 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T1       |  50000 |      1 |    150 |00:00:00.03 |   50000 |      0 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("T2"."VAL2"=3002 AND "T2"."VAL1"=2))
   4 - access("T2"."ID"="T1"."ID")
   5 - filter(("T1"."VAL2"=4002 AND "T1"."VAL1"=2))

Note
-----
   - cardinality feedback used for this statement

查看下share pool中的SQL信息,应该是2个child

SQL> select a.sql_id, child_number child_no, b.sql_text
  2    from v$sql_shared_cursor a, v$sqlstats b
  3   where a.sql_id = b.sql_id
  4     and USE_FEEDBACK_STATS = 'Y'
  5     and a.sql_id = 'a9592mqtc8yzn';

SQL_ID                 CHILD_NO SQL_TEXT
-------------------- ---------- --------------------------------------------------
a9592mqtc8yzn                 0 select /*+ gather_plan_statistics */     t1.val1,
                                t1.val2, t2.val1, t2.val2 from     t1, t2 where
                                  t1.val1 = 2 and t1.val2 = 4002 and t2.id = t1.id
                                 and t2.val1 = 2 and t2.val2 = 3002

a9592mqtc8yzn                 1 select /*+ gather_plan_statistics */     t1.val1,
                                t1.val2, t2.val1, t2.val2 from     t1, t2 where
                                  t1.val1 = 2 and t1.val2 = 4002 and t2.id = t1.id
                                 and t2.val1 = 2 and t2.val2 = 3002

关闭cardinality feedback的方法
系统级-alter system set “_optimizer_use_feedback”=false;
会话级-alter session set “_optimizer_use_feedback”=false;
语句hint-/*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */

此条目发表在CBO分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s