[20210205]toad檢視真實執行計劃問題3.txt

[20210205]toad檢視真實執行計劃問題3.txt

--//前段時間遇到toad檢視真實執行計劃問題的問題,實際上最近還遇到一種情況,做一個記錄.

1.環境:
SCOTT@book> @ 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

2.測試:
--//在toad的sql編輯介面上,輸入如下:
select /*+ gather_plan_statistics */ deptno,dname,loc from dept
order by dname

--//看到的執行計劃如下:
Plan hash value: 120787663
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     4 (100)|          |      4 |00:00:00.01 |       6 |      5 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |      4 |    80 |     4  (25)| 00:00:01 |      4 |00:00:00.01 |       6 |      5 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |      5 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------

--//在toad的sql編輯介面上,輸入如下:
select /*+ gather_plan_statistics */ deptno,dname,loc from dept
--1
--order by dname;

--//看到的執行計劃如下:
Plan hash value: 3383998547
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |      4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

--//很明顯這樣看到的執行計劃不是真實的執行計劃,因為我加入提示gather_plan_statistics.使用toad自帶的SQL Tracker看看.
--------------------------------------------------------------------------------
Timestamp: 2021/2/5 15:34:35

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 /*+ gather_plan_statistics */ deptno,dname,loc from dept
--1
--order by dname']
outHash=[0.201803003e+010]
Elapsed time: 0.001

--------------------------------------------------------------------------------
Timestamp: 2021/2/5 15:34:36

Select *
from v$sql_plan
Where hash_value = '2018030035'
and child_number =0
order by id


sqlhv=['2018030035']
cn=[0]


Elapsed time: 0.002

--------------------------------------------------------------------------------
Timestamp: 2021/2/5 15:34:36

explain plan set statement_id='Administrator:020521153435' into SYS.PLAN_TABLE$ For select /*+ gather_plan_statistics */ deptno,dname,loc from dept
--1
--order by dname

Elapsed time: 0.004

--------------------------------------------------------------------------------
Timestamp: 2021/2/5 15:34:36

Select *
From SYS.PLAN_TABLE$
Where statement_id = 'Administrator:020521153435'
order by id


STATEMENT_ID=['Administrator:020521153435']


Elapsed time: 0.003

--------------------------------------------------------------------------------
Timestamp: 2021/2/5 15:34:36

select * from table(dbms_xplan.display(table_name => 'SYS.PLAN_TABLE$',statement_id => 'Administrator:020521153435', format => 'ADVANCED, ALLSTATS, LAST, OUTLINE, PEEKED_BINDS'))
--------------------------------------------------------------------------------
Timestamp: 2021/2/5 15:15:47

--//為什麼計算hash_value錯誤呢?查詢共享池知道對於的sql_id.
SCOTT@book> @ sql_id 6pdk4km3kwfz1
SQL_ID        SQLTEXT
------------- ---------------------------------------------------------------
6pdk4km3kwfz1 select /*+ gather_plan_statistics */ deptno,dname,loc from dept
              --1
              --order by dname;

--//注意看一個小細節,就是後面的分號.在前面的hash_value計算時不存在.
--//如果你寫成如下,沒有最後的分號.
select /*+ gather_plan_statistics */ deptno,dname,loc from dept
--1
--order by dname

--//看到的執行計劃就是如下:
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      4 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------------------------
--//這個是真實的執行計劃.
--//如下現在改寫如下,就是寫回分號:
select /*+ gather_plan_statistics */ deptno,dname,loc from dept
--1
--order by dname;

--//看到的執行計劃就是如下:
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      4 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------------------------
--//有點奇怪吧,現在能看到真實的執行計劃.實際上主要沒有後面的分號已經存在共享池裡面,這樣就可以看到"真實"的執行計劃,
--//但是實際上現在執行語句的sql_id='6pdk4km3kwfz1'.可以簡單驗證如下:

SCOTT@book> select sql_id,sql_text,hash_value,executions from v$sqlarea where sql_id in ('8zt25wdw4jcfm','6pdk4km3kwfz1');
SQL_ID        SQL_TEXT                                                     HASH_VALUE EXECUTIONS
------------- ------------------------------------------------------------ ---------- ----------
6pdk4km3kwfz1 select /*+ gather_plan_statistics */ deptno,dname,loc from d 3341695969          3
              ept --1 --order by dname;

8zt25wdw4jcfm select /*+ gather_plan_statistics */ deptno,dname,loc from d 2018030035          1
              ept --1 --order by dname

--//在toad介面下執行:
select /*+ gather_plan_statistics */ deptno,dname,loc from dept
--1
--order by dname;

SCOTT@book> select sql_id,sql_text,hash_value,executions from v$sqlarea where sql_id in ('8zt25wdw4jcfm','6pdk4km3kwfz1');
SQL_ID        SQL_TEXT                                                     HASH_VALUE EXECUTIONS
------------- ------------------------------------------------------------ ---------- ----------
6pdk4km3kwfz1 select /*+ gather_plan_statistics */ deptno,dname,loc from d 3341695969          4
              ept --1 --order by dname;

8zt25wdw4jcfm select /*+ gather_plan_statistics */ deptno,dname,loc from d 2018030035          1
              ept --1 --order by dname

--//sql_id=6pdk4km3kwfz1的EXECUTIONS增加.在toad介面看到的執行計劃實際上是sql_id=8zt25wdw4jcfm.

3.另外還有一種可能導致看不到真實的執行計劃:
--//如果執行看前面的SQL Tracker看看.它執行的是child_number =0的情況,如果它不存在看到的執行計劃也不真實.
Select *
from v$sql_plan
Where hash_value = '181301342'
and child_number =0
order by id

4.總結:
--//這個問題主要在於toad下可以不輸入後面的分號就可以執行,它自動不上,如果最後一行是註解,這樣解析就出錯了.
--//解決方法很簡單,刪除最後的分號或者在前一行的非註解行加入分號也可以.例子如下:

select /*+ gather_plan_statistics */ deptno,dname,loc from dept;
--1
--order by dname;

--//以前遇到的情況就是會話cursor_sharing=force.