[20161216]toad下显示真实的执行计划.txt

最新推荐文章于 2020-12-05 00:36:02 发布

cuixun7780

最新推荐文章于 2020-12-05 00:36:02 发布

阅读量187

点赞数

文章标签：数据库

[20161216]toad下显示真实的执行计划.txt

--大家都应该知道使用explain plan看执行计划,有时候显示的执行计划不是真实的执行计划.现在我虽然使用它看,仅仅作为参考.
--昨天看链接:
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/12/13/toad-explain-plan-tip-returning-actual-sql-execution-explain-plan

--才知道toad下如何在sql编辑界面上显示真实的执行计划.

--仅仅在执行计划上点击右键,勾上"load cached plan if possible" 就ok了.我自己写一个例子测试看看.

1.环境:
SCOTT@book> @ &r/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

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

2.测试:
Select /*+ BIND_AWARE */ * from t where flag=:x;

--实际情况下带入'0','1',两者执行计划不一样.而在toad下使用explain plan看就是全表扫描.
--先带入'0'测试看看.再sql下执行看看.

SCOTT@book> alter system flush shared_pool;
System altered.

SCOTT@book> variable x varchar2(1)
SCOTT@book> exec :x := '0';
PL/SQL procedure successfully completed.

SCOTT@book> SELECT /*+ BIND_AWARE */ * from t where flag=:x;
        ID NAME                                     F
---------- ---------------------------------------- -
    100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
           xxxxxxxxxxxxxxxxxxxx

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7739acusdmc6c, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */ * from t where flag=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T        |      1 |   108 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
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)

3.toad下查看,修改display mode 改成 dbms_xplan,这样不用帖图.
SQL_ID 7739acusdmc6c, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */ * from t where flag=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T        |      1 |   108 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))
      END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG"=:X)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "FLAG"[VARCHAR2,1]
   2 - "T".ROWID[ROWID,10], "FLAG"[VARCHAR2,1]

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--//可以发现执行计划走索引.
--//修改SELECT 为sELECT,再看执行计划,看到的执行计划如下:
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 50000 | 5273K|   435   (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T    | 50000 | 5273K|   435   (1)| 00:00:06 |
---------------------------------------------------------------------------

--很明显这个执行计划可能是不真实的.

3.另外我也发现问题,如果你跟踪toad操作,可以发现

declare
v_ignore raw(100);
v_oldhash number;
v_hash number;
begin
v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);
:outHash := v_hash;
end;

SQLText=['sELECT /*+ BIND_AWARE */ * from t where flag=:x']
outHash=[0.334866302e+010]

Elapsed time: 0.003
--------------------------------------------------------------------------------
Timestamp: 2016/12/16 11:33:09

Select *
from v$sql_plan
Where hash_value = '3348663027'
and child_number =0
order by id

sqlhv=['3348663027']
cn=[0]

Elapsed time: 0.005

explain plan set statement_id='Administrator:121616113309' into SYS.PLAN_TABLE$ For sELECT /*+ BIND_AWARE */ * from t where flag=:x
Elapsed time: 0.005

--它仅仅查询v$sql_plan child_number =0的是否存在,有一些情况child_number =0是已经无效的执行计划,甚至不存在的执行计划.
--这样依旧调用explain plan.这个问题也存在于SGA TRACE的界面上.我一直希望有一个下拉列表,让dba选择对应的child_number.

4.还有这个测试我还发现toad界面下,提示BIND_AWARE无效.
你可以发现在toad下执行,带入变量'0',选择的执行计划依旧是全表扫描.

--//修改flag大写,在toad下执行,变量'0'.
sELECT /*+ BIND_AWARE */ * from t where FLAG=:x;

SCOTT@book> select sql_id,sql_text,executions from v$sqlarea where upper(sql_text) like 'SELECT%BIND_AWARE%' and upper(sql_text) not like '%SQL_TEXT%';
SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
3bsyf7a6jz1py SELEct /*+ BIND_AWARE */ * from t where flag=:x                       3
bh4qquz7sm25k sELECT /*+ BIND_AWARE */ * from t where FLAG=:x                       1
7739acusdmc6c SELECT /*+ BIND_AWARE */ * from t where flag=:x                       1

--看看sql_id='bh4qquz7sm25k'执行计划.

SCOTT@book> @ &r/dpc bh4qquz7sm25k ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bh4qquz7sm25k, child number 0
-------------------------------------
sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |        |       |   435 (100)|          |
|* 1 | TABLE ACCESS FULL| T    | 50000 | 5273K|   435   (1)| 00:00:06 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG"=:X)

--选择的是全表扫描.估计和工具显示返回行数有关,不过我打上auto trace测试结果也一样.不知道为什么无效.
--然后在sqlplus执行相同的语句:
SCOTT@book> sELECT /*+ BIND_AWARE */ * from t where FLAG=:x;
        ID NAME                                     F
---------- ---------------------------------------- -
    100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
           xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
           xxxxxxxxxxxxxxxxxxxx

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bh4qquz7sm25k, child number 1
-------------------------------------
sELECT /*+ BIND_AWARE */ * from t where FLAG=:x

Plan hash value: 120143814

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T        |      1 |   108 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
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)

SCOTT@book> @ &r/share bh4qquz7sm25k
SQL_TEXT                       = sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
SQL_ID                         = bh4qquz7sm25k
ADDRESS                        = 000000007BCEEFB8
CHILD_ADDRESS                  = 000000007CA23160
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>45</ID><reason>NLS Settings(0)</reason>
                                 <size>2x4</size><SessionLengthSemantics>0</SessionLengthSemantics><CursorLengthSemantics>0
                                 </CursorLengthSemantics></ChildNode>
--------------------------------------------------
SQL_TEXT                       = sELECT /*+ BIND_AWARE */ * from t where FLAG=:x
SQL_ID                         = bh4qquz7sm25k
ADDRESS                        = 000000007BCEEFB8
CHILD_ADDRESS                  = 000000007BD006A8
CHILD_NUMBER                   = 1
LANGUAGE_MISMATCH              = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

--不知道为什么,以后研究看看把.
总之:
这个功能有比没有好,当然toad应该改进更好.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2130781/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2130781/

cuixun7780

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
[20161216]toad下显示真实的执行计划.txt

[20161216]toad下显示真实的执行计划.txt --大家都应该知道使用explain plan看执行计划,有时候显示的执行计划不是真实的执行计划.现在我虽然使用它看,仅仅作为参考. --昨天看链接: htt...
复制链接

扫一扫