[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语句时注意这个细节，避免以后在这个方面浪费时间。
--//自己现在已经很少使用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语句时注意这个细节，避免以后在这个方面浪费时间。
