[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

---------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

| 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後再分析自動建立擴充套件統計資訊.

[20181225]12CR2 SQL Plan Directives.txt

相關文章