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