[20161216]toad下顯示真實的執行計劃.txt

lfree發表於2016-12-16

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章