[20210205]警惕toad下優化直方圖相關sql語句.txt

[20210205]警惕toad下優化直方圖相關sql語句.txt

--//今天優化sql語句在toad12 ,我發現一個奇怪現象,語句的執行計劃不使用我建立的索引.折騰N久,才想起以前遇到
--//的情況,連結如下:http://blog.itpub.net/267265/viewspace-2668520/=>[20191213]toad 12下BIND_AWARE提示無效.txt
--//問題我優化的資料庫11.2.0.3與我的測試環境11.2.0.4有一點點不同.
--//先演示測試環境遇到的情況:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e5;
update t set flag='0' where id=1e5;
commit ;
create index i_t_flag on t(flag);

SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => '',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 4',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

2.測試:
--//先寫sql語句在toad sql編輯視窗:
select /*+ gather_plan_statistics */ * from t where flag=:x;
--//然後選擇執行,代入引數'0'.獲取sql_id=apjr1ppx7hgjm.

SQL_ID  ctu9k9j5v97wn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where flag=:x
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   435 (100)|          |      1 |00:00:00.01 |    1567 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |  50000 |  5273K|   435   (1)| 00:00:06 |      1 |00:00:00.01 |    1567 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~      
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "FLAG"[VARCHAR2,1]
 
-//注意看下劃線,不知道toad下加入OPT_PARAM('_optim_peek_user_binds' 'false'),導致繫結變數peek無效.
--//E-Rows=50000,也就是一半記錄,導致執行計劃走全表掃描.

--//即使加入提示改寫如下:
select /*+ OPT_PARAM('_optim_peek_user_binds' 'true') gather_plan_statistics */ * from t where flag=:x;
--//一樣無效.

3.使用dbms_sqldiag包看執行計劃分析:

$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');

SCOTT@book> @ 10053x ctu9k9j5v97wn 0
PL/SQL procedure successfully completed.

SCOTT@book> @ pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_57333_actu9k9j5v97wn.trc

--//我才發現這樣看到的執行計劃可能不是真實的執行計劃.
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
  Column (#3):
    NewDensity:0.000005, OldDensity:0.000005 BktCnt:100000, PopBktCnt:99999, PopValCnt:1, NDV:2
  Column (#3): FLAG(
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.000005
    Histogram: Freq  #Bkts: 2  UncompBkts: 100000  EndPtVals: 2
  Table: T  Alias: T
    Card: Original: 100000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  434.96  Resp: 434.96  Degree: 0
      Cost_io: 434.00  Cost_cpu: 35372940
      Resp_io: 434.00  Resp_cpu: 35372940
  Access Path: index (AllEqRange)
    Index: I_T_FLAG
    resc_io: 2.00  resc_cpu: 15483
    ix_sel: 0.000010  ix_sel_with_filters: 0.000010
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: I_T_FLAG
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0
...

----- Current SQL Statement for this session (sql_id=2scp7tw6bfvzu) -----
/* SQL Analyze(15,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7bcb1030       145  package body SYS.DBMS_SQLTUNE_INTERNAL
0x7bcb1030     12098  package body SYS.DBMS_SQLTUNE_INTERNAL
0x7d7830c0      1229  package body SYS.DBMS_SQLDIAG
0x7c1b2e80         1  anonymous block
sql_text_length=84
sql=/* SQL Analyze(15,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |         |       |       |     2 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | T       |     1 |   108 |     2 |  00:00:01 |
| 2   |   INDEX RANGE SCAN           | I_T_FLAG|     1 |       |     1 |  00:00:01 |
-----------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("FLAG"=:X)

Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : SCOTT
  plan_hash      : 120143814
  plan_hash_2    : 2969257144
Peeked Binds
============
  Bind variable information
    position=1
    datatype(code)=1
    datatype(string)=VARCHAR2(32)
    char set id=852
    char format=1
    max length=32
    value=0
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))
    END_OUTLINE_DATA
  */

--//執行計劃對不上.outlined裡面沒有OPT_PARAM('_optim_peek_user_binds' 'false')
--//另外我嘗試在toad下執行@ 10053x ctu9k9j5v97wn 0,看到的情況也是一樣的.
--//也就是使用dbms_sqldiag包看執行計劃分析也要注意.

4.繼續分析:
SCOTT@book> alter session set "_optim_peek_user_binds"=false;
Session altered.

SCOTT@book> @ 10053x ctu9k9j5v97wn 0
PL/SQL procedure successfully completed.

--//檢視轉儲,
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
  Column (#3):
    NewDensity:0.000005, OldDensity:0.000005 BktCnt:100000, PopBktCnt:99999, PopValCnt:1, NDV:2
  Column (#3): FLAG(
    AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.000005
    Histogram: Freq  #Bkts: 2  UncompBkts: 100000  EndPtVals: 2  --//直方圖存在
  Table: T  Alias: T
    Card: Original: 100000.000000  Rounded: 50000  Computed: 50000.00  Non Adjusted: 50000.00
  Access Path: TableScan
    Cost:  434.96  Resp: 434.96  Degree: 0
      Cost_io: 434.00  Cost_cpu: 35372940
      Resp_io: 434.00  Resp_cpu: 35372940
  Access Path: index (AllEqRange)
    Index: I_T_FLAG
    resc_io: 873.00  resc_cpu: 25717867
    ix_sel: 0.500000  ix_sel_with_filters: 0.500000  --//ix_sel: 0.500000,走索引肯定很差
    Cost: 873.70  Resp: 873.70  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 434.96  Degree: 1  Resp: 434.96  Card: 50000.00  Bytes: 0

    check parallelism for statement[<unnamed>]
kkfdtParallel: parallel is possible (no statement type restrictions)
    kkfdPaForcePrm: dop:1 ()
kkfdPaPrm: use dictionary DOP(1) on table
kkfdPaPrm:- The table : 96021
kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop)
kkfdiPaPrm: dop:1 serial(?)
***************************************
...

sql_text_length=84
sql=/* SQL Analyze(44,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |   435 |           |
| 1   |  TABLE ACCESS FULL | T       |   49K | 5273K |   435 |  00:00:06 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("FLAG"=:X)

Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : SCOTT
  plan_hash      : 1601196873
  plan_hash_2    : 2498539100
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
    END_OUTLINE_DATA
  */

--//設定環境一致後,看到的執行計劃一致,實際上dbms_sqldiag包是分析語句
sql=/* SQL Analyze(15,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
--//不知道為什麼toad在執行sql語句時遇到直方圖情況是自動加上了OPT_PARAM('_optim_peek_user_binds' 'false').

5.總結:
--//toad 12下要注意這個細節,不然可能遇到無法解析的情況.
--//結果導致加入BIND_AWARE提示無效.
--//另外在11.2.0.3下我看到的情況更加奇怪另外寫一篇blog分析.