[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语句要注意这些细节.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2756573/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
