[20140108]12c新特性_Statistics Feedback.txt
摘自:Apress.Pro.Oracle.SQL.2nd.Edition.Nov.2013.pdf
Statistics feedback, known as cardinality feedback prior to Oracle 12c, is a mechanism used by the optimizer to
improve automatically plans' repeated query executions that have cardinality misestimates. The first time a SQL
statement executes, the optimizer determines the execution plan and marks the plan to enable statistics feedback
monitoring if.
.Any of the tables in the statement have missing statistics
.There are ANDed or ORed filter predicates on a table
.Any predicates contain complex operators for which the optimizer cannot compute cardinality estimates accurately
After a completed statement execution, the optimizer then compares the original cardinality estimates with
the actual cardinalities. If there are any significant differences, it stores the correct estimates to use during the next
execution of that statement to reoptimize (reparse) it. Prior to Oracle 12c, this is as far as things went. But, in 12c, the
optimizer also stores a SQL plan directive. A SQL plan directive contains additional information and instructions
the optimizer can use to generate a better plan the next time the statement is executed; but, it isn't pertinent just to a
single cursor. It is pertinent to any cursor that uses similar expressions.
自己写一个例子来说明:
SCOTT@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
CREATE TABLE t1 AS SELECT ROWNUM id1,FLOOR(SQRT(ROWNUM)) id2 ,'test' NAME FROM DUAL CONNECT BY LEVEL
insert into t1 select rownum+9999,100 ,'test' NAME FROM DUAL CONNECT BY LEVEL
commit;
create index i_t1_id2 on t1(id2);
exec dbms_stats.gather_table_stats(user, 'T1', method_opt=>'for all columns size 1 ',no_invalidate => false);
column low_value format a10
column high_value format a10
column data_type format a10
select column_name,data_type,num_distinct,low_value,high_value,num_buckets,histogram from dba_tab_cols where owner=user and table_name='T1';
COLUMN_NAME DATA_TYPE NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_BUCKETS HISTOGRAM
-------------------- ---------- ------------ ---------- ---------- ----------- ---------------
NAME CHAR 1 74657374 74657374 1 NONE
ID2 NUMBER 100 C102 C202 1 NONE
ID1 NUMBER 19998 C102 C3026463 1 NONE
--ID2字段没有建立直方图.
alter session set statistics_level=all;
SCOTT@ztest> select * from t1 where id2=100 and name='Test';
no rows selected
SCOTT@ztest> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g90m6jyj35qj7, child number 0
-------------------------------------
select * from t1 where id2=100 and name='Test'
Plan hash value: 588477579
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 0 |00:00:00.01 | 46 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 2 (0)| 0 |00:00:00.01 | 46 |
|* 2 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 200 | 1 (0)| 9999 |00:00:00.01 | 21 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='Test')
2 - access("ID2"=100)
--可以发现扫描索引时E-rows=200,而A-row=9999,两者存在很大差异.
SCOTT@ztest> select sql_id,sql_text,is_reoptimizable from v$sql where sql_id = 'g90m6jyj35qj7';
SQL_ID SQL_TEXT I
------------- ------------------------------------------------------------ -
g90m6jyj35qj7 select * from t1 where id2=100 and name='Test' Y
--is_reoptimizable=Y,说明存在下次执行有再优化的可能.
SELECT D.DIRECTIVE_ID,
d.TYPE,
d.state,
d.auto_drop,
d.reason,
d.created,
o.owner,
O.OBJECT_type,
o.object_name,
o.SUBOBJECT_NAME
FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id = o.directive_id
AND o.owner = USER
AND o.object_name = 'T1'
order by 1;
no rows selected
--执行如下
exec dbms_spd.flush_sql_plan_directive;
SELECT D.DIRECTIVE_ID,
d.TYPE,
d.state,
d.auto_drop,
d.reason,
d.created,
o.owner,
O.OBJECT_type,
o.object_name,
o.SUBOBJECT_NAME
FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id = o.directive_id
AND o.owner = USER
AND o.object_name = 'T1'
order by 1;
SCOTT@ztest> /
DIRECTIVE_ID TYPE STATE AUT REASON CREATED OWNER OBJECT OBJECT_NAME SUBOBJECT_
-------------------- -------------------- ------------- --- ------------------------------------ ------------------------------ ------ ------ -------------------- ----------
1837740869431841840 DYNAMIC_SAMPLING NEW YES SINGLE TABLE CARDINALITY MISESTIMATE 2014-01-08 10:43:56.000000 SCOTT TABLE T1
1837740869431841840 DYNAMIC_SAMPLING NEW YES SINGLE TABLE CARDINALITY MISESTIMATE 2014-01-08 10:43:56.000000 SCOTT COLUMN T1 ID2
--提示字段ID2存在SINGLE TABLE CARDINALITY MISESTIMATE.
SCOTT@ztest> select * from t1 where id2=100 and name='Test';
no rows selected
SCOTT@ztest> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g90m6jyj35qj7, child number 1
-------------------------------------
select * from t1 where id2=100 and name='Test'
Plan hash value: 3617692013
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 16 (100)| 0 |00:00:00.01 | 53 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 1 | 16 (0)| 0 |00:00:00.01 | 53 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("ID2"=100 AND "NAME"='Test'))
Note
-----
- statistics feedback used for this statement
--可以发现我并没有建立直方图在ID2字段,oracle在执行时发现统计信息存在差异,第2次执行纠正了执行计划,选择了全部扫描.
column is_bind_aware format a10
column is_bind_sensitive format a10
column is_obsolete format a10
column is_reoptimizable format a10
column is_resolved_adaptive_plan format a10
column is_shareable format a10
SELECT sql_id,
child_number,
sql_text,
is_bind_aware,
is_bind_sensitive,
is_obsolete,
is_reoptimizable,
is_resolved_adaptive_plan,
is_shareable
FROM v$sql
WHERE sql_id = 'g90m6jyj35qj7';
SQL_ID CHILD_NUMBER SQL_TEXT IS_BIND_AW IS_BIND_SE IS_OBSOLET IS_REOPTIM IS_RESOLVE IS_SHAREAB
------------- -------------------- ------------------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
g90m6jyj35qj7 0 select * from t1 where id2=100 and name='Test' N N N Y N
g90m6jyj35qj7 1 select * from t1 where id2=100 and name='Test' N N N N Y
--child_numnber=0 的is_shareable='N',这样以后会剔除共享池.