[20210205]警惕toad下优化直方图相关sql语句3.txt

[20210205]警惕toad下优化直方图相关sql语句3.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有一点点不同. --//toad 版本12.6.0.53. --//本来想那原始语句来分析,设计到一些安全问题,我只能建立例子来分析: 1.环境: > @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production create table t tablespace users 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.测试: --//在toad界面下执行: show parameter cursor_sharing cursor_sharing                       string   EXACT --//排除http://blog.itpub.net/267265/viewspace-2749843/=>[20210114]toad查看真实执行计划问题.txt,补充说明我修改了触发器. CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_RECORD_IP    AFTER LOGON    ON DATABASE DECLARE    v_client_info   v$session.client_info%TYPE; BEGIN    v_client_info := NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1');    DBMS_APPLICATION_INFO.set_client_info (v_client_info);    DBMS_SESSION.set_identifier (v_client_info);    IF (USER <> 'SYS' and  USER <> 'SYSTEM')    THEN       EXECUTE IMMEDIATE 'alter session set cursor_sharing =force';    END IF; END; / --//先写sql语句在toad sql编辑窗口: select /*+ gather_plan_statistics */ * from t where flag=:x; --//然后选择执行,代入参数'0'.获取sql_id=ctu9k9j5v97wn. 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 |        |       |   436 (100)|          |      1 |00:00:00.01 |    1567 | |*  1 |  TABLE ACCESS FULL| T    |      1 |  50000 |  5273K|   436   (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.3')       DB_VERSION('11.2.0.3')       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] --//理论应该选择我建立的索引,注意看A-Rows=1,选择索引是最佳选择,这里还有一个细节,我发现这样执行计划里面看不到绑定变量值. > @ bind_cap ctu9k9j5v97wn '' SQL_ID        CHILD_NUMBER WAS NAME                                       POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING ------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- ------------- ctu9k9j5v97wn            0 YES :X                                                1         32 2021-02-05 16:49:41 VARCHAR2(32)    0 --//我不知道为什么出现这样的情况,很奇怪的是如果我在测试环境执行版本11.2.0.4,在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   */ 3.使用10053分析: --//我在toad的sql编辑窗口输入如下,注意sql语句必须做一些改动不然不会硬解析,我修改select为seLECT: alter session set events '10053 trace name context forever, level 12'; seLECT /*+ gather_plan_statistics */ * from t where flag=:x; alter session set events '10053 trace name context off'; --//注意要分别别执行.也就是先输入alter session set events '10053 trace name context forever, level 12';,然后清除,在执行语句. --//最后alter session set events '10053 trace name context off';. --//在sqlplus上重复执行以以上步骤. variable x varchar2(1); exec :x := '0'; alter session set events '10053 trace name context forever, level 12'; seLECT /*+ gather_plan_statistics */ * from t where flag=:x; alter session set events '10053 trace name context off'; # diff -Nur hrp430_ora_19023.trc hrp430_ora_19938.trc  > diff.txt --//检查不同发现: ....   -Registered qb: SEL$1 0xdf0e6678 (PARSER) +Registered qb: SEL$1 0x44ee6a80 (PARSER)  ---------------------  QUERY BLOCK SIGNATURE  --------------------- @@ -1158,25 +1158,26 @@    apadrv-start sqlid=7032951094734105818    : -    call(in-use=1400, alloc=16344), compile(in-use=56624, alloc=58184), execution(in-use=3544, alloc=4032) +    call(in-use=1464, alloc=16344), compile(in-use=56560, alloc=58632), execution(in-use=3544, alloc=4032)    *******************************************  Peeked values of the binds in SQL statement  *******************************************  ----- Bind Info (kkscoacd) -----   Bind#0 -  oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00 -  oacflg=01 fl2=1000010 frm=01 csi=852 siz=32 off=0 -  No bind buffers allocated +  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00 +  oacflg=03 fl2=1000000 frm=01 csi=852 siz=32 off=0 +  kxsbbbfp=2ac244f32f88  bln=32  avl=01  flg=05 +  value="0" --//问题出在这里吗?toad下看不到绑定变量值,导致10053分析有误吗?    Final query after transformations:******* UNPARSED QUERY IS *******  SELECT "T"."ID" "ID","T"."NAME" "NAME","T"."FLAG" "FLAG" FROM "SYS"."T" "T" WHERE "T"."FLAG"=:B1  kkoqbc: optimizing query block SEL$1 (#0)                    : -    call(in-use=1448, alloc=16344), compile(in-use=57552, alloc=58184), execution(in-use=3680, alloc=4032) +    call(in-use=1512, alloc=16344), compile(in-use=57496, alloc=58632), execution(in-use=3680, alloc=4032)   -kkoqbc-subheap (create addr=0x2b85df0ef698) +kkoqbc-subheap (create addr=0x2ac244eefaa0)  ****************  QUERY BLOCK TEXT  **************** @@ -1215,18 +1216,19 @@      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 +    Card: Original: 100000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00    Access Path: TableScan      Cost:  435.68  Resp: 435.68  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 -    Cost: 874.22  Resp: 874.22  Degree: 1 -  Best:: AccessPath: TableScan -         Cost: 435.68  Degree: 1  Resp: 435.68  Card: 50000.00  Bytes: 0 +    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    ***************************************   @@ -1239,7 +1241,7 @@  Permutations for Starting Table :0  Join order[1]:  T[T]#0  *********************** -Best so far:  Table#: 0  cost: 435.6827  card: 50000.0000  bytes: 5400000 +Best so far:  Table#: 0  cost: 2.0007  card: 1.0000  bytes: 108  ***********************  (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000   @@ -1250,27 +1252,28 @@    Trying or-Expansion on query block SEL$1 (#0)  Transfer Optimizer annotations for query block SEL$1 (#0) -id=0 frofand predicate="T"."FLAG"=:B1 +id=0 frofkks[i] (index start key) predicate="T"."FLAG"=:B1 +id=0 frofkke[i] (index stop key) predicate="T"."FLAG"=:B1  Final cost for query block SEL$1 (#0) - All Rows Plan:    Best join order: 1 -  Cost: 435.6827  Degree: 1  Card: 50000.0000  Bytes: 5400000 -  Resc: 435.6827  Resc_io: 434.0000  Resc_cpu: 35372940 -  Resp: 435.6827  Resp_io: 434.0000  Resc_cpu: 35372940 -kkoqbc-subheap (delete addr=0x2b85df0ef698, in-use=13600, alloc=16408) +  Cost: 2.0007  Degree: 1  Card: 1.0000  Bytes: 108 +  Resc: 2.0007  Resc_io: 2.0000  Resc_cpu: 15483 +  Resp: 2.0007  Resp_io: 2.0000  Resc_cpu: 15483 +kkoqbc-subheap (delete addr=0x2ac244eefaa0, in-use=13696, alloc=16408)  kkoqbc-end:          : -    call(in-use=12800, alloc=49184), compile(in-use=58408, alloc=62328), execution(in-use=4048, alloc=8088) +    call(in-use=12864, alloc=49184), compile(in-use=58744, alloc=59424), execution(in-use=4184, alloc=8088)    kkoqbc: finish optimizing query block SEL$1 (#0)  apadrv-end            : -    call(in-use=12800, alloc=49184), compile(in-use=59320, alloc=62328), execution(in-use=4048, alloc=8088) +    call(in-use=12864, alloc=49184), compile(in-use=59656, alloc=63568), execution(in-use=4184, alloc=8088)      Starting SQL statement dump   -user_id=0 user_name=SYS module=TOAD 12.6.0.53 action= -sql_id=636hgs8q54b6u plan_hash_value=1601196873 problem_type=3 +user_id=0 user_name=SYS module=sqlplus@gxqyydg4 (TNS V1-V3) action= +sql_id=636hgs8q54b6u plan_hash_value=120143814 problem_type=3  ----- Current SQL Statement for this session (sql_id=636hgs8q54b6u) -----  seLECT /*+ gather_plan_statistics */ * from t where flag=:x  sql_text_length=60 @@ -1281,22 +1284,33 @@  ============  Plan Table  ============ --------------------------------------+-----------------------------------+ -| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      | --------------------------------------+-----------------------------------+ -| 0   | SELECT STATEMENT   |         |       |       |   436 |           | -| 1   |  TABLE ACCESS FULL | T       |   49K | 5273K |   436 |  00:00:06 | --------------------------------------+-----------------------------------+ +-----------------------------------------------+-----------------------------------+ +| 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:  ---------------------- -1 - filter("FLAG"=:X) +2 - access("FLAG"=:X)    Content of other_xml column  ===========================    db_version     : 11.2.0.3    parse_schema   : SYS -  plan_hash      : 1601196873 -  plan_hash_2    : 2498539100 +  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 @@ -1305,7 +1319,7 @@        DB_VERSION('11.2.0.3')        ALL_ROWS        OUTLINE_LEAF(@"SEL$1") -      FULL(@"SEL$1" "T"@"SEL$1") +      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))      END_OUTLINE_DATA    */   @@ -2288,13 +2302,13 @@      Query Block Registry: -SEL$1 0xdf0e6678 (PARSER) [FINAL] +SEL$1 0x44ee6a80 (PARSER) [FINAL]    : -    call(in-use=15720, alloc=49184), compile(in-use=87608, alloc=149600), execution(in-use=7224, alloc=8088) +    call(in-use=15832, alloc=49184), compile(in-use=90560, alloc=153784), execution(in-use=9096, alloc=12144)    End of Optimizer State Dump  Dumping Hints  ============= -  atom_hint=(@=0xac161720 err=0 resol=0 used=1 token=821 org=1 lvl=1 txt=GATHER_PLAN_STATISTICS ()) +  atom_hint=(@=0xa662f820 err=0 resol=0 used=1 token=821 org=1 lvl=1 txt=GATHER_PLAN_STATISTICS ())  ====================== END SQL Statement Dump ====================== 3.在sqlplus执行: > select /*+ gather_plan_statistics full(t) */ * from t where flag=:x;         ID NAME                                     F ---------- ---------------------------------------- -     100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0            xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx            xxxxxxxxxxxxxxxxxxxx > @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  6q1d1n59rcgjm, child number 0 ------------------------------------- select /*+ gather_plan_statistics full(t) */ * 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 |        |       |   436 (100)|          |      1 |00:00:00.01 |    1567 | |*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |   108 |   436   (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 Peeked Binds (identified by position): --------------------------------------    1 - (VARCHAR2(30), CSID=852): '0' Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("FLAG"=:X) 28 rows selected. --//在sqlplus下即使全表扫描,也能看到Peeked Binds部分.  SELECT *     FROM v$sql_plan    WHERE sql_id IN ('6q1d1n59rcgjm', 'ctu9k9j5v97wn') AND child_number = 0 ORDER BY id; --//主要不同是other_xml: 6q1d1n59rcgjm <other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="plan_hash">1601196873</info><info type="plan_hash_2">2498539100</info><peeked_binds><bind nam=":X" pos="1" dty="1" csi="852" frm="1" mxl="32">30</bind></peeked_binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data></other_xml> ctu9k9j5v97wn <other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="plan_hash">1601196873</info><info type="plan_hash_2">2498539100</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data></other_xml> <peeked_binds><bind nam=":X" pos="1" dty="1" csi="852" frm="1" mxl="32">30</bind></peeked_binds> --//很奇怪为什么toad下生成的执行计划没有取得绑定变化值. --//我发别在sqlplus和toad下执行 > @ share 2kby5rjcxp08g '' SQL_TEXT                       = select /*+ gather_plan_statistics */ * from t where Flag=:x SQL_ID                         = 2kby5rjcxp08g ADDRESS                        = 00000000AD256620 CHILD_ADDRESS                  = 00000000AD2561C0 CHILD_NUMBER                   = 0 REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>30</ID><reason>User Bind Peek settings mismatch(0)</reason><size>8x4</size><ctxflg>1073774864</ctxflg><ctxyfl>6291584</ctxyfl><kksciflg>4122</kksciflg><kkscyfl>6291584</kkscyfl><ctxbposc>65537</ctxbposc><KXSUSB_NEWBND_DRIVER>1</KXSUSB_NEWBND_DRIVER><kxscfl3>2181169216</kxscfl3><_optim_peek_user_binds>1</_optim_peek_us er_binds></ChildNode> -------------------------------------------------- SQL_TEXT                       = select /*+ gather_plan_statistics */ * from t where Flag=:x SQL_ID                         = 2kby5rjcxp08g ADDRESS                        = 00000000AD256620 CHILD_ADDRESS                  = 00000000A5391878 CHILD_NUMBER                   = 1 USER_BIND_PEEK_MISMATCH        = Y REASON                         = -------------------------------------------------- PL/SQL procedure successfully completed. --//从这里可以看出一定是_optim_peek_user_binds在作怪.不知道为什么放弃.. 4.继续测试: --//sqlplus 下: alter session set "_optim_peek_user_binds"=false; variable x varchar2(1); exec :x := '0'; Select /*+ gather_plan_statistics */ * from t where flag=:x; > @ dpc '' outline PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dwt8xfty8qn8a, 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 |        |       |   436 (100)|          |      1 |00:00:00.01 |    1567 | |*  1 |  TABLE ACCESS FULL| T    |      1 |  50000 |  5273K|   436   (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.3')       DB_VERSION('11.2.0.3')       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) --//只要设置"_optim_peek_user_binds"=false;,就看不到绑定变量peek的部分信息. --//换一句话讲只要在toad第一次执行硬分析,不管任何sql语句执行计划就没有Peeked Binds 信息,就是不会做peek. --//导致存在直方图有关sql语句解析报错.很奇怪的是11.2.0.4在outline date存在 OPT_PARAM('_optim_peek_user_binds' 'false') --//而11.2.0.3就没有.不知道什么回事.也许toad下执行sql语句存在什么特殊之处. --//也就是提醒自己使用toad优化sql语句要注意这些细节.