[20181225]12CR2 SQL Plan Directives.txt

来源：这里教程网时间：2026-03-03 12:48:48 作者：

[20181225]12CR2 SQL Plan Directives.txt --//12C引入SQL PLAN Directives.12cR1版本会造成大量的动态取样,影响性能.许多人把OPTIMIZER_ADAPTIVE_FEATURES设置为false. --//这也是为什么我不主张将XX.1版本使用在生产系统.12CR2做了一些改进,废除了OPTIMIZER_ADAPTIVE_FEATURES参数.使用2个新的 --//参数OPTIMIZER_ADAPTIVE_PLANS,OPTIMIZER_ADAPTIVE_STATISTICS,缺省前者true,后者为false. --//通过测试说明问题. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SCOTT@test01p> show parameter OPTIMIZER_ADAPTIVE NAME TYPE VALUE --------------------------------- ------- ------ optimizer_adaptive_plans boolean TRUE optimizer_adaptive_reporting_only boolean FALSE optimizer_adaptive_statistics boolean FALSE --//注:没有OPTIMIZER_ADAPTIVE_FEATURES参数,optimizer_adaptive_plans=true,optimizer_adaptive_statistics=false. 2.建立测试环境: CREATE TABLE t AS SELECT ROWNUM id ,LPAD ('x', 20, 'x') name ,MOD (ROWNUM, 3) flag1 ,MOD (ROWNUM, 3) flag2 ,MOD (ROWNUM, 3) flag3 FROM DUAL CONNECT BY LEVEL <= 1e5; --//说明:flags1,flags2,flags3分别存在3个取值,按照道理存在27种选择.因为存在相关性,仅仅存在3种选择. 3.测试: SCOTT@test01p> alter session set statistics_level=all; Session altered. SCOTT@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1; COUNT(DISTINCTNAME) ------------------- 1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 872fdta99gdk8, child number 0 ------------------------------------- select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 Plan hash value: 2359337548 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 155 (100)| | 1 |00:00:00.06 | 556 | 540 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 12 | | | 1 |00:00:00.06 | 556 | 540 | | | | | 2 | VIEW | VW_DAG_0 | 1 | 1 | 12 | 155 (2)| 00:00:01 | 1 |00:00:00.06 | 556 | 540 | | | | | 3 | HASH GROUP BY | | 1 | 1 | 30 | 155 (2)| 00:00:01 | 1 |00:00:00.06 | 556 | 540 | 1345K| 1345K| 504K (0)| |* 4 | TABLE ACCESS FULL| T | 1 | 3704 | 108K| 154 (1)| 00:00:01 | 33334 |00:00:00.06 | 556 | 540 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1)) --//注意看id=4, E-Rows=3704,估算按照100000/27 = 3703.7,而A-Rows=33334(10000/3 = 3333.3),存在很大偏差. SCOTT@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id ='872fdta99gdk8'; SQL_ID CHILD_NUMBER I ------------- ------------ - 872fdta99gdk8 0 Y --//is_reoptimizable='Y' SCOTT@test01p> exec dbms_spd.flush_sql_plan_directive; PL/SQL procedure successfully completed. set numw 20 column NOTES format a50 SELECT directive_id ,TYPE ,enabled ,state ,notes ,reason FROM dba_sql_plan_directives WHERE directive_id IN (SELECT directive_id FROM dba_sql_plan_dir_objects WHERE owner = USER AND object_name = 'T'); DIRECTIVE_ID TYPE ENA STATE NOTES REASON -------------------- -------------------- --- -------------------- -------------------------------------------------- ------------------------------------ 17342821566768621333 DYNAMIC_SAMPLING YES USABLE <spd_note><internal_state>NEW</internal_state><red SINGLE TABLE CARDINALITY MISESTIMATE undant>NO</redundant><spd_text>{EC(SCOTT.T)[FLAG1, FLAG2, FLAG3]}</spd_text></spd_note> --//指导建议字段flag1,flag2,flag3联合查询时存在偏差,建议动态取样. --//补充说明:{EC(SCOTT.T)[FLAG1,FLAG2, FLAG3]} --//这里的E和C，以及可能出现其他的字符，解释如下： E – equality_predicates_only C – simple_column_predicates_only J – index_access_by_join_predicates F – filter_on_joining_object --//再次执行: SCOTT@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1; COUNT(DISTINCTNAME) ------------------- 1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 872fdta99gdk8, child number 1 ------------------------------------- select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 Plan hash value: 2359337548 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 156 (100)| | 1 |00:00:00.01 | 556 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 12 | | | 1 |00:00:00.01 | 556 | | | | | 2 | VIEW | VW_DAG_0 | 1 | 1 | 12 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | | | | | 3 | HASH GROUP BY | | 1 | 1 | 30 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | 1345K| 1345K| 505K (0)| |* 4 | TABLE ACCESS FULL| T | 1 | 33334 | 976K| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1)) Note ----- - statistics feedback used for this statement --//注意note,指示statistics feedback used for this statement. SCOTT@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id ='872fdta99gdk8'; SQL_ID CHILD_NUMBER I ------------- -------------------- - 872fdta99gdk8 0 Y 872fdta99gdk8 1 N SCOTT@test01p> @ share 872fdta99gdk8 SQL_TEXT = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 SQL_ID = 872fdta99gdk8 ADDRESS = 000007FF1393F830 CHILD_ADDRESS = 000007FF13D9C198 CHILD_NUMBER = 0 USE_FEEDBACK_STATS = Y REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>48</ID><reason>Auto Reoptimization Mismatch(1)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>0</dnum_kksfcxe></ChildNode> -------------------------------------------------- SQL_TEXT = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 SQL_ID = 872fdta99gdk8 ADDRESS = 000007FF1393F830 CHILD_ADDRESS = 000007FF115A7E58 CHILD_NUMBER = 1 REASON = -------------------------------------------------- PL/SQL procedure successfully completed. SELECT directive_id ,TYPE ,enabled ,state ,notes ,reason FROM dba_sql_plan_directives WHERE directive_id IN (SELECT directive_id FROM dba_sql_plan_dir_objects WHERE owner = USER AND object_name = 'T'); DIRECTIVE_ID TYPE ENA STATE NOTES REASON -------------------- -------------------- --- -------------------- -------------------------------------------------- ------------------------------------ 17342821566768621333 DYNAMIC_SAMPLING YES USABLE <spd_note><internal_state>NEW</internal_state><red SINGLE TABLE CARDINALITY MISESTIMATE undant>NO</redundant><spd_text>{EC(SCOTT.T)[FLAG1, FLAG2, FLAG3]}</spd_text></spd_note> 4.继续测试: --//设置OPTIMIZER_ADAPTIVE_STATISTICS=true看看. SCOTT@test01p> alter session set OPTIMIZER_ADAPTIVE_STATISTICS=true ; Session altered. SCOTT@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1; COUNT(DISTINCTNAME) ------------------- 1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 872fdta99gdk8, child number 2 ------------------------------------- select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 Plan hash value: 2359337548 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 157 (100)| | 1 |00:00:00.01 | 556 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 12 | | | 1 |00:00:00.01 | 556 | | | | | 2 | VIEW | VW_DAG_0 | 1 | 1 | 12 | 157 (3)| 00:00:01 | 1 |00:00:00.01 | 556 | | | | | 3 | HASH GROUP BY | | 1 | 1 | 30 | 157 (3)| 00:00:01 | 1 |00:00:00.01 | 556 | 1345K| 1345K| 496K (0)| |* 4 | TABLE ACCESS FULL| T | 1 | 48497 | 1420K| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1)) Note ----- - dynamic statistics used: dynamic sampling (level=2) - 1 Sql Plan Directive used for this statement --//设置OPTIMIZER_ADAPTIVE_STATISTICS=true的情况下,做了动态取样(level=2).产生新的子光标. SCOTT@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id ='872fdta99gdk8'; SQL_ID CHILD_NUMBER I ------------- -------------------- - 872fdta99gdk8 0 Y 872fdta99gdk8 1 N 872fdta99gdk8 2 N SCOTT@test01p> @ share 872fdta99gdk8 old 15: and q.sql_id like ''&1''', new 15: and q.sql_id like ''872fdta99gdk8''', SQL_TEXT = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 SQL_ID = 872fdta99gdk8 ADDRESS = 000007FF1393F830 CHILD_ADDRESS = 000007FF13D9C198 CHILD_NUMBER = 0 USE_FEEDBACK_STATS = Y REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>48</ID><reason>Auto Reoptimization Mismatch(1)</reason><size>3x4</size><kxscflg>32</kxscflg><kxscfl4>4194560</kxscfl4><dnum_kksfcxe>0</dnum_kksfcxe></ChildNode> -------------------------------------------------- SQL_TEXT = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 SQL_ID = 872fdta99gdk8 ADDRESS = 000007FF1393F830 CHILD_ADDRESS = 000007FF115A7E58 CHILD_NUMBER = 1 REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>3</ID><reason>Optimizer mismatch(12)</reason><size>2x440</size><_optimizer_dsdir_usage_control> 0 126 </_optimizer_dsdir_usage_control><optimizer_adaptive_stat istics> false true </optimizer_adaptive_statistics><_optimizer_use_feedback_for_join> false true </_optimizer_use_feedback_for_join><_optimizer_ads_for_pq> false true </_optimizer_ads_for_pq></ChildNode> -------------------------------------------------- SQL_TEXT = select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 SQL_ID = 872fdta99gdk8 ADDRESS = 000007FF1393F830 CHILD_ADDRESS = 000007FF0FDBE618 CHILD_NUMBER = 2 OPTIMIZER_MISMATCH = Y REASON = -------------------------------------------------- SCOTT@test01p> exec dbms_spd.flush_sql_plan_directive; PL/SQL procedure successfully completed. SELECT directive_id ,TYPE ,enabled ,state ,notes ,reason FROM dba_sql_plan_directives WHERE directive_id IN (SELECT directive_id FROM dba_sql_plan_dir_objects WHERE owner = USER AND object_name = 'T'); DIRECTIVE_ID TYPE ENA STATE NOTES REASON -------------------- -------------------- --- ------ -------------------------------------------------- ------------------------------------ 14350253949522184195 DYNAMIC_SAMPLING_RES YES USABLE <spd_note><internal_state>NEW</internal_state><red VERIFY CARDINALITY ESTIMATE ULT undant>NO</redundant><spd_text>{(SCOTT.T, num_rows =100000) - (SQL_ID:4k5yrxfcvd5qb, T.CARD=48497[-2 -2])}</spd_text></spd_note> 17342821566768621333 DYNAMIC_SAMPLING YES USABLE <spd_note><internal_state>MISSING_STATS</internal_ SINGLE TABLE CARDINALITY MISESTIMATE state><redundant>NO</redundant><spd_text>{EC(SCOTT .T)[FLAG1, FLAG2, FLAG3]}</spd_text></spd_note> --//多了一行,动态取样分析后估计T.CARD=48497,虽然与实际A-Rows=33334还是存在很大偏差.指导提示是MISSING_STATS. --//补充说明SQL_ID:4k5yrxfcvd5qb,我没有查询到对于sql语句,有点奇怪!! SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T',options=>'gather auto',no_invalidate=>false); PL/SQL procedure successfully completed. SCOTT@test01p> select column_name,num_buckets,histogram from user_tab_col_statistics where table_name ='T'; COLUMN_NAME NUM_BUCKETS HISTOGRAM ----------- ----------- --------------- ID 1 NONE NAME 1 NONE FLAG1 3 FREQUENCY FLAG2 3 FREQUENCY FLAG3 3 FREQUENCY --//并没有指导建议生成相关列的统计信息. --//实际上12cR2引入新参数AUTO_STAT_EXTENSIONS控制extended stats的收集,缺省设置off.(没有打开).设置AUTO_STAT_EXTENSIONS=on可以打开. SCOTT@test01p> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') c10 from dual; C10 ---------- OFF SCOTT@test01p> exec dbms_stats.set_global_prefs('AUTO_STAT_EXTENSIONS','ON') ; PL/SQL procedure successfully completed. SCOTT@test01p> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') c10 from dual; C10 ---------- ON SCOTT@test01p> exec dbms_stats.gather_table_stats(user,'T',options=>'gather auto',no_invalidate=>false); PL/SQL procedure successfully completed. SCOTT@test01p> column COLUMN_NAME format a30 SCOTT@test01p> select column_name,num_buckets,histogram from user_tab_col_statistics where table_name ='T'; COLUMN_NAME NUM_BUCKETS HISTOGRAM ------------------------------ ----------- --------------- ID 1 NONE NAME 1 NONE FLAG1 3 FREQUENCY FLAG2 3 FREQUENCY FLAG3 3 FREQUENCY SYS_STS0SR$HPC$E#KVDPEN#0R2JOU 3 FREQUENCY 6 rows selected. SCOTT@test01p> column EXTENSION_name format a30 SCOTT@test01p> select * from user_stat_extensions where table_name ='T'; TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DRO ---------- ------------------------------ ------------------------- ------- --- T SYS_STS0SR$HPC$E#KVDPEN#0R2JOU ("FLAG1","FLAG2","FLAG3") SYSTEM YES --//可以发现现在收集了相关列("FLAG1","FLAG2","FLAG3")的统计,并且建立了直方图. SCOTT@test01p> alter session set OPTIMIZER_ADAPTIVE_STATISTICS=false ; Session altered. SCOTT@test01p> select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1; COUNT(DISTINCTNAME) ------------------- 1 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 872fdta99gdk8, child number 1 ------------------------------------- select count(distinct name) from t where flag1=1 and flag2=1 and flag3=1 Plan hash value: 2359337548 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 156 (100)| | 1 |00:00:00.01 | 556 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 12 | | | 1 |00:00:00.01 | 556 | | | | | 2 | VIEW | VW_DAG_0 | 1 | 1 | 12 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | | | | | 3 | HASH GROUP BY | | 1 | 1 | 30 | 156 (2)| 00:00:01 | 1 |00:00:00.01 | 556 | 1345K| 1345K| 507K (0)| |* 4 | TABLE ACCESS FULL| T | 1 | 33334 | 976K| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1)) --//可以发现E-Rows已经正确修正. SCOTT@test01p> exec dbms_spd.flush_sql_plan_directive; PL/SQL procedure successfully completed. DIRECTIVE_ID TYPE ENA STATE NOTES REASON -------------------- -------------------- --- -------------------- -------------------------------------------------- ------------------------------------ 14350253949522184195 DYNAMIC_SAMPLING_RES YES USABLE <spd_note><internal_state>NEW</internal_state><red VERIFY CARDINALITY ESTIMATE ULT undant>NO</redundant><spd_text>{(SCOTT.T, num_rows =100000) - (SQL_ID:4k5yrxfcvd5qb, T.CARD=48497[-2 -2])}</spd_text></spd_note> 17342821566768621333 DYNAMIC_SAMPLING YES SUPERSEDED <spd_note><internal_state>HAS_STATS</internal_stat SINGLE TABLE CARDINALITY MISESTIMATE e><redundant>NO</redundant><spd_text>{EC(SCOTT.T)[ FLAG1, FLAG2, FLAG3]}</spd_text></spd_note> --//注意看现在不是MISSING_STATS而是提示HAS_STATS. SUPERSEDED 表示取代，接替. --//有了相关列统计其它涉及相关列的查询就不会在动态取样,而是估计行数与实际行数接近.而且执行其它类似语句也不会出现is_reoptimizable='Y'的情况. SCOTT@test01p> select max(id) from t where flag1=1 and flag2=1 and flag3=1; MAX(ID) ---------- 100000 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 6stmvx0gcybbg, child number 0 ------------------------------------- select max(id) from t where flag1=1 and flag2=1 and flag3=1 Plan hash value: 2966233522 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 154 (100)| | 1 |00:00:00.01 | 556 | | 1 | SORT AGGREGATE | | 1 | 1 | 14 | | | 1 |00:00:00.01 | 556 | |* 2 | TABLE ACCESS FULL| T | 1 | 33334 | 455K| 154 (1)| 00:00:01 | 33334 |00:00:00.01 | 556 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("FLAG1"=1 AND "FLAG2"=1 AND "FLAG3"=1)) SCOTT@test01p> select sql_id,child_number,is_reoptimizable from v$sql where sql_id ='6stmvx0gcybbg'; SQL_ID CHILD_NUMBER I ------------- ------------ - 6stmvx0gcybbg 0 N --//is_reoptimizable = 'N'. 总结: --//12cR2做了一些改进,optimizer_adaptive_statistics=false,避免大量的动态取样对性能的影响.另外即使设置optimizer_adaptive_statistics=true. --//oracle也保存了动态取样的结果. --//dbms_stats引入新的参数AUTO_STAT_EXTENSIONS,缺省是off.设置on后再分析自动建立扩展统计信息.