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

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

相关文章