[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.