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