[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语句时注意这个细节,避免以后在这个方面浪费时间。

热门相关:成人学院   小姨子的梦   天有眼国语   女职员:职场恋爱   妻子的视频