[20240313]toad gather_plan_statistics执行计划相关问题.txt

[20240313]toad gather_plan_statistics执行计划相关问题.txt

--//自己现在已经很少使用toad,使用也是作为辅助功能,毕竟图形界面能更快的操作显示信息.
--//昨天遇到一个问题,自己当时没有反映过来,浪费点时间,做一个记录避免以后再次犯浑.
--//我一般在toad的sql编辑界面下尽可能看真实的执行计划
--//参考:https://blog.itpub.net/267265/viewspace-2130781/=>[20161216]toad下显示真实的执行计划.txt
--//没有想到看到的执行计划统计存在一些问题，看下面的例子：

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.建立例子:

create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e4;
update t set flag='0' where id=1e4;
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字段上建立直方图.

3.测试:
--//在toad界面上输入如下语句并执行如下：
select  /*+ gather_plan_statistics */ * from t where flag=:x;
--//注:加入gather_plan_statistics提示执行计划可以显示更多信息.

--//带入'1'测试看看,执行计划如下：
--//注意是字符串类型。
plan hash value: 1601196873
---------------------------------------------------------------------------
| id  | operation         | name | e-rows |e-bytes| cost (%cpu)| e-time   |
---------------------------------------------------------------------------
|   0 | select statement  |      |        |       |    48 (100)|          |
|*  1 |  table access full| t    |   5000 |   522k|    48   (0)| 00:00:01 |
---------------------------------------------------------------------------
--//你可以发现并没有显示完整的执行计划统计信息，提示gather_plan_statistics没有起作用。
--//实际上显示是explain plan的执行计划.

--//带入'0'测试看看，执行计划如下:
plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |      1 |00:00:00.01 |     160 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |      1 |00:00:00.01 |     160 |
--------------------------------------------------------------------------------------------------------------------
-//看到的执行计划不同，为什么呢? 实际上问题在于toad执行时每次fetch 1001行。
--//:x = 0 是仅仅返回1条，执行计划已经完成，可以显示完整的统计信息。
--//而带入:x = 1时，仅仅显示前500条(实际上fetch 1001行)，没有执行完成，无法显示完整的执行统计信息。可以拖动滚动条或者按
--//page down键到结尾，这样也可以得到完整的执行计划。

--//如果滚动到中间(实际上只要不到结尾), 看执行计划：
plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |   1001 |00:00:00.01 |      18 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |   1001 |00:00:00.01 |      18 |
--------------------------------------------------------------------------------------------------------------------
--//看到a-rows=1001.也就是第1次fetch的数量,也就是以后在toad下看这类执行计划注意,这样看到的执行计划的统计信息可能不真实!!
--//如果按page down键到结尾再看执行计划：
plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |   9999 |00:00:00.01 |     168 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |   9999 |00:00:00.01 |     168 |
--------------------------------------------------------------------------------------------------------------------

--//如果在sql编辑界面上打开auto trace，全部结果fetch完成就不会出现上面看到的情况了。
--//再次带入'1'测试看看，执行计划如下:
plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |   9999 |00:00:00.01 |     168 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |   9999 |00:00:00.01 |     168 |
--------------------------------------------------------------------------------------------------------------------

4.继续测试：
--//关闭auto trace。
--//再次带入'1'测试看看，执行计划如下:
plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |   9999 |00:00:00.01 |     168 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |   9999 |00:00:00.01 |     168 |
--------------------------------------------------------------------------------------------------------------------

--//再次带入'1'测试看看，执行计划如下:
plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |   1001 |00:00:00.01 |      18 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |   1001 |00:00:00.01 |      18 |
--------------------------------------------------------------------------------------------------------------------
--//注:当时马上看执行计划,理论讲应该没有a-rows之类的统计,本来以为会显示explain plan的执行计划,但是看到的却是
--//a-rows=1001的情况,我猜测可能是使用dbms_xplan.display_cursor,format加入last的缘故.

--//开始我很困惑,实际上这样第1次执行看到的是前1次的执行计划统计信息。

--//使用sql tracker跟踪发现,toad执行如下：
select * from table(dbms_xplan.display_cursor(sql_id => '7pnr0krspk166', cursor_child_no => null, format => 'all,
allstats, last, rows, bytes, cost, outline, partition, parallel, peeked_binds, predicate, projection, alias, remote,
note'))

--//加入有参数last，显示参数我基本全部选上。
--//而第2次执行，因为前面有记录这个统计信息，这样就有显示，虽然不准确。当然这些是我的猜测!!

--//总之，在toad下注意这个细节，建议优化调试sql语句时打开auto trace，这样看执行计划的统计信息比较准确,不容易出现误判。

5.另外的问题注意：
--//你可以注意一个细节，toad下无论带入'0'还是'1'选择的执行计划都是全表扫描，e-rows=5000并没有采用直方图的结果,也就是总记
--//录的50%.
--//即使小量修改sql语句，sql_id不同,导致重新分析sql语句,第1次带入参数'0'，执行计划也是选择全表扫描。
--//如果仔细看outline data部分：
/*+
      begin_outline_data
      ignore_optim_embedded_hints
      optimizer_features_enable('11.2.0.4')
      db_version('11.2.0.4')
      opt_param('_optim_peek_user_binds' 'false')
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      all_rows
      outline_leaf(@"sel$1")
      full(@"sel$1" "t"@"sel$1")
      end_outline_data
  */

--//不知道为什么toad关闭了绑定变量peek。而且我不知道怎么打开，使用toad的版本号12.6.0.53,只有toad 9.x版本正常的.
--//这样使得在toad调试sql语句涉及到这方面相关问题时要小心，而且导致bind_aware提示无效。

select   /*+ gather_plan_statistics bind_aware opt_param('_optim_peek_user_binds' 'true') */ * from t where flag=:x;
plan hash value: 1601196873
 
--------------------------------------------------------------------------------------------------------------------
| id  | operation         | name | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | select statement  |      |      1 |        |       |    48 (100)|          |      1 |00:00:00.01 |     160 |
|*  1 |  table access full| t    |      1 |   5000 |   522k|    48   (0)| 00:00:01 |      1 |00:00:00.01 |     160 |
--------------------------------------------------------------------------------------------------------------------
--//还是全表扫描。
 
6.sqlplus下测试：

scott@book> variable x varchar2(32) ;
scott@book> exec :x :='0';
pl/sql procedure successfully completed.

scott@book> select   /*+ gather_plan_statistics  */ * from t where flag=:x;
        id name                                     f
---------- ---------------------------------------- -
     10000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
           xxxxxxxxxxxxxxxxxxxx


scott@book> @ dpc '' '' ''
plan_table_output
-------------------------------------
sql_id  8s7vwp7ykv52x, child number 1
-------------------------------------
select   /*+ gather_plan_statistics  */ * from t where flag=:x
plan hash value: 120143814
----------------------------------------------------------------------------------------------------------------------------------
| id  | operation                   | name     | starts | e-rows |e-bytes| cost (%cpu)| e-time   | a-rows |   a-time   | buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | select statement            |          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       4 |
|   1 |  table access by index rowid| t        |      1 |      1 |   107 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|*  2 |   index range scan          | i_t_flag |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------
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)
--//建立新的子光标,child number=1,可以使用i_t_flag索引。

7.总结：
--//注意toad下调试优化sql语句时注意这个细节，避免以后在这个方面浪费时间。