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