[20161216]toad下显示真实的执行计划.txt
--大家都应该知道使用explain plan看执行计划,有时候显示的执行计划不是真实的执行计划.现在我虽然使用它看,仅仅作为参考.
--昨天看链接:
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/12/13/toad-explain-plan-tip-returning-actual-sql-execution-explain-plan
--才知道toad下如何在sql编辑界面上显示真实的执行计划.
--仅仅在执行计划上点击右键,勾上"load cached plan if possible" 就ok了.我自己写一个例子测试看看.
1.环境:
SCOTT@book> @ &r/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.
--在flag字段上建立直方图.
2.测试:
Select /*+ BIND_AWARE */ * from t where flag=:x;
--实际情况下带入'0','1',两者执行计划不一样.而在toad下使用explain plan看就是全表扫描.
--先带入'0'测试看看.再sql下执行看看.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> variable x varchar2(1)
SCOTT@book> exec :x := '0';
PL/SQL procedure successfully completed.
SCOTT@book> SELECT /*+ BIND_AWARE */ * from t where flag=:x;
ID NAME F
---------- ---------------------------------------- -
100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7739acusdmc6c, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */ * from t where flag=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 108 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:X)
3.toad下查看,修改display mode 改成 dbms_xplan,这样不用帖图.
SQL_ID 7739acusdmc6c, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */ * from t where flag=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 108 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - 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')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "FLAG"[VARCHAR2,1]
2 - "T".ROWID[ROWID,10], "FLAG"[VARCHAR2,1]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//可以发现执行计划走索引.
--//修改SELECT 为sELECT,再看执行计划,看到的执行计划如下:
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 5273K| 435 (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 50000 | 5273K| 435 (1)| 00:00:06 |
---------------------------------------------------------------------------
--很明显这个执行计划可能是不真实的.
3.另外我也发现问题,如果你跟踪toad操作,可以发现
declare
v_ignore raw(100);
v_oldhash number;
v_hash number;
begin
v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);
:outHash := v_hash;
end;
SQLText=['sELECT /*+ BIND_AWARE */ * from t where flag=:x']
outHash=[0.334866302e+010]
Elapsed time: 0.003
--------------------------------------------------------------------------------
Timestamp: 2016/12/16 11:33:09
Select *
from v$sql_plan
Where hash_value = '3348663027'
and child_number =0
order by id
sqlhv=['3348663027']
cn=[0]
Elapsed time: 0.005
explain plan set statement_id='Administrator:121616113309' into SYS.PLAN_TABLE$ For sELECT /*+ BIND_AWARE */ * from t where flag=:x
Elapsed time: 0.005
--它仅仅查询v$sql_plan child_number =0的是否存在,有一些情况child_number =0是已经无效的执行计划,甚至不存在的执行计划.
--这样依旧调用explain plan.这个问题也存在于SGA TRACE的界面上.我一直希望有一个下拉列表,让dba选择对应的child_number.
4.还有这个测试我还发现toad界面下,提示BIND_AWARE无效.
你可以发现在toad下执行,带入变量'0',选择的执行计划依旧是全表扫描.
--//修改flag大写,在toad下执行,变量'0'.
sELECT /*+ BIND_AWARE */ * from t where FLAG=:x;
SCOTT@book> select sql_id,sql_text,executions from v$sqlarea where upper(sql_text) like 'SELECT%BIND_AWARE%' and upper(sql_text) not like '%SQL_TEXT%';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
3bsyf7a6jz1py SELEct /*+ BIND_AWARE */ * from t where flag=:x 3
bh4qquz7sm25k sELECT /*+ BIND_AWARE */ * from t where FLAG=:x 1
7739acusdmc6c SELECT /*+ BIND_AWARE */ * from t where flag=:x 1
--看看sql_id='bh4qquz7sm25k'执行计划.
SCOTT@book> @ &r/dpc bh4qquz7sm25k ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bh4qquz7sm25k, child number 0
-------------------------------------
sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 435 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50000 | 5273K| 435 (1)| 00:00:06 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"=:X)
--选择的是全表扫描.估计和工具显示返回行数有关,不过我打上auto trace测试结果也一样.不知道为什么无效.
--然后在sqlplus执行相同的语句:
SCOTT@book> sELECT /*+ BIND_AWARE */ * from t where FLAG=:x;
ID NAME F
---------- ---------------------------------------- -
100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bh4qquz7sm25k, child number 1
-------------------------------------
sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 108 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:X)
SCOTT@book> @ &r/share bh4qquz7sm25k
SQL_TEXT = sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
SQL_ID = bh4qquz7sm25k
ADDRESS = 000000007BCEEFB8
CHILD_ADDRESS = 000000007CA23160
CHILD_NUMBER = 0
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>45</ID><reason>NLS Settings(0)</reason>
<size>2x4</size><SessionLengthSemantics>0</SessionLengthSemantics><CursorLengthSemantics>0
</CursorLengthSemantics></ChildNode>
--------------------------------------------------
SQL_TEXT = sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
SQL_ID = bh4qquz7sm25k
ADDRESS = 000000007BCEEFB8
CHILD_ADDRESS = 000000007BD006A8
CHILD_NUMBER = 1
LANGUAGE_MISMATCH = Y
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
--不知道为什么,以后研究看看把.
总之:
这个功能有比没有好,当然toad应该改进更好.