[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分析.