[20161216]toad下顯示真實的執行計劃.txt
[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應該改進更好.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2130781/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181120]toad看真實的執行計劃.txt
- 解決TOAD中執行計劃顯示報錯的問題
- [20210114]toad檢視真實執行計劃問題.txt
- [20210205]toad檢視真實執行計劃問題3.txt
- toad與執行計劃
- 【顯示執行計劃】在普通使用者下使用set autot 檢視執行計劃
- 如何獲取真實的執行計劃
- TOAD檢視執行計劃表
- [20140109]顯示執行計劃的問題.txt
- [20230130]toad看執行計劃注意.txt
- TOAD中檢視執行計劃（Explain Plan）AI
- [20190720]12cR2顯示執行計劃.txt
- 在Oracle中，如何得到真實的執行計劃？Oracle
- Oracle如何檢視真實執行計劃（一）Oracle
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- 執行計劃中Note部分顯示'PLAN TABLE' is old version
- 實驗-資料分佈對執行計劃的影響.txt
- [20171220]toad plsql顯示整形的bug.txtSQL
- 從真實案例出發，全方位解讀 NebulaGraph 中的執行計劃
- toad顯示explain plan的問題AI
- 執行計劃-1：獲取執行計劃
- TOAD和PLSQL 預設日期顯示、rowid顯示、TNSNAME的修改SQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- 執行計劃
- 檢視ORACLE的實際執行計劃Oracle
- SQL的執行計劃SQL
- 執行計劃的理解.
- word空格處不顯示下劃線
- [20120327]toad與sqlplus下執行sql語句的一個細節.txtSQL
- toad 中文顯示亂碼解決方法
- [20171225]檢視並行執行計劃注意的問題.txt並行
- [20210926]並行執行計劃疑問.txt並行
- linux下面顯示所有正在執行的執行緒Linux執行緒
- [20131121]奇怪的執行計劃變化.txt
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL