[20181225]12CR2 SQL Plan Directives.txt

来源:https://www.cnblogs.com/lfree/archive/2018/12/25/10175035.html
-Advertisement-
Play Games

[20181225]12CR2 SQL Plan Directives.txt--//12C引入SQL PLAN Directives.12cR1版本會造成大量的動態取樣,影響性能.許多人把OPTIMIZER_ADAPTIVE_FEATURES設置為false.--//這也是為什麼我不主張將XX.1 ...


[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後再分析自動建立擴展統計信息.


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 20181225-Linux Shell Bash環境下自動化創建ssh互信腳本
    20181225 Linux Shell Bash環境下自動化創建ssh互信腳本 1. 我的Blog 博客園 https://www.cnblogs.com/piggybaba/ 個人網站 http://piggybaba.cn GitHub https://github.com/AndyYHM/W ...
  • Jenkins+Maven+Gitlab+Tomcat 自動化構建打包、部署
    一、環境需求 本帖針對的是Linux環境，Windows或其他系統也可借鑒。具體只講述Jenkins配置以及整個流程的實現。 1.JDK（或JRE）及Java環境變數配置，我用的是JDK1.8.0_144，網上帖子也很多，不贅述。 2.Jenkins 持續集成和持續交付項目。 3.現有項目及gitl ...
  • 下載RPM常用網址
    網址一：http://www.rpm-find.net/linux/rpm2html/search.php 網址二：http://rpmfind.net/linux/rpm2html/search.php 這兩個網址感覺一樣，但還是有差別的。基本都能搜索到你想要的包。 rpm安裝命令：http:// ...
  • 簡述FTP主動模式與被動模式
    1 FTP工作模式 2 不同模式FTP面臨的問題 3 主動模式的FTP連接建立連接主要步驟 客戶端打開一個隨機的埠（埠號大於1024，在這裡，我們稱它為x），同時一個FTP進程連接至伺服器的21號命令埠。此時，源埠為隨機埠x（在客戶端）；遠程埠為21（在伺服器端）。輸入正確的用戶名/密碼 ...
  • MySQL AUTO_INCREMENT 學習總結
    正文 之前有碰到過開發同事指出一張InnoDB表的自增列 AUTO_INCREMENT 值莫明的變大，由於這張表是通過mysqldump導出導入的。 問題排查： 1、首先，查看表表義的sql部分的 auto_increment 值部分是正常，所以排除是導入表問題所引起的； 2、最後，經過溝通瞭解懷疑 ...
  • 【MYSQL筆記2】mysql字元集形式（utf8轉gbk）
    在使用中，可能我們在建表的時候要用到中文，因此這裡簡單備份下字元集格式；在mysql環境中輸入 可以查看當前連接系統的參數 為了讓mysql資料庫能支持中文，輸入命令 同理把其他的幾個utf8的也改為gbk的然後輸入 這樣，我們在建表的時候就可以使用中文了，趕緊去試試了！ ...
  • 自研資料庫CynosDB存儲系統如何實現即時恢復
    本文由雲+社區發表 CynosDB for PostgreSQL是騰訊雲自研的一款雲原生資料庫，其主要核心思想來自於亞馬遜的雲資料庫服務Aurora。這種核心思想就是“基於日誌的存儲”和“存儲計算分離”。同時，CynosDB在架構和工程實現上確實有很多和Aurora不一樣的地方。 下圖為CynosD ...
  • 讀懂MySQL執行計劃
    原文：https://mp.weixin.qq.com/s/-BlLvBKcF-yalELY7XkqaQ 前言 在之前的面試過程中,問到執行計劃,有很多童鞋不知道是什麼?甚至將執行計劃與執行時間認為是同一個概念。今天我們就一起來瞭解一下執行計划到底是什麼?有什麼用途? 執行計劃是什麼? 執行計劃，簡 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類