[20230130]toad看执行计划注意.txt

--//同事给我看一条sql语句,说生产系统执行8秒,而在toad执行需要1秒,实际上toad如果快,可能并没有完成整个结果集合,也就是先
--//fetch 500条.这样看起来很快,实际上可能执行很慢.
--//做一个例子演示出来.

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t as select * from all_objects;
Table created.

SCOTT@test01p> @ gts t
Gather Table Statistics for table t...
exec dbms_stats.gather_table_stats(null, upper('t'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.

2.在toad下执行:
--//先在sqlplus下执行如下,保证执行计划已经存在共享池子.
alter session set statistics_level = all;
select * from t;

--//然后在toad下执行如下:
alter session set statistics_level = all;
select * from t;

--//然后在toad下查看真实的执行计划,参考链接:
--// http://blog.itpub.net/267265/viewspace-2130781/=> [20161216]toad下显示真实的执行计划.txt

SQL_ID  7hsd86dsfbba6, child number 0
-------------------------------------
SELECT * FROM t
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 |        |       |    97 (100)|          |    501 |00:00:00.01 |      21 |
|   1 |  TABLE ACCESS FULL| T    |      1 |  18725 |  2212K|    97   (2)| 00:00:01 |    501 |00:00:00.01 |      21 |
--------------------------------------------------------------------------------------------------------------------
--//注意看A-Rows仅仅取了501条记录,buffers=21.这样执行相对较快.

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T"."OWNER"[VARCHAR2,128], "T"."OBJECT_NAME"[VARCHAR2,128], "T"."SUBOBJECT_NAME"[VARCHAR2,128],
       "T"."OBJECT_ID"[NUMBER,22], "T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,23],
       "T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7], "T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],
       "T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1], "T"."SECONDARY"[VARCHAR2,1],
       "T"."NAMESPACE"[NUMBER,22], "T"."EDITION_NAME"[VARCHAR2,128], "T"."SHARING"[VARCHAR2,18],
       "T"."EDITIONABLE"[VARCHAR2,1], "T"."ORACLE_MAINTAINED"[VARCHAR2,1], "T"."APPLICATION"[VARCHAR2,1],
       "T"."DEFAULT_COLLATION"[VARCHAR2,100], "T"."DUPLICATED"[VARCHAR2,1], "T"."SHARDED"[VARCHAR2,1],
       "T"."CREATED_APPID"[NUMBER,22], "T"."CREATED_VSNID"[NUMBER,22], "T"."MODIFIED_APPID"[NUMBER,22],
       "T"."MODIFIED_VSNID"[NUMBER,22]
 
--//如果要看完整的执行计划统计:在sql编辑界面上选上auto trace,再次执行,查看执行计划:

SQL_ID  7hsd86dsfbba6, child number 0
-------------------------------------
SELECT * FROM t
 
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 |        |       |    97 (100)|          |  18725 |00:00:00.01 |     368 |
|   1 |  TABLE ACCESS FULL| T    |      1 |  18725 |  2212K|    97   (2)| 00:00:01 |  18725 |00:00:00.01 |     368 |
--------------------------------------------------------------------------------------------------------------------
--//注意看A-Rows取了18725条记录,buffers=368.这样执行相对较慢.
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "T"."OWNER"[VARCHAR2,128], "T"."OBJECT_NAME"[VARCHAR2,128], "T"."SUBOBJECT_NAME"[VARCHAR2,128],
       "T"."OBJECT_ID"[NUMBER,22], "T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,23],
       "T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7], "T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],
       "T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1], "T"."SECONDARY"[VARCHAR2,1],
       "T"."NAMESPACE"[NUMBER,22], "T"."EDITION_NAME"[VARCHAR2,128], "T"."SHARING"[VARCHAR2,18],
       "T"."EDITIONABLE"[VARCHAR2,1], "T"."ORACLE_MAINTAINED"[VARCHAR2,1], "T"."APPLICATION"[VARCHAR2,1],
       "T"."DEFAULT_COLLATION"[VARCHAR2,100], "T"."DUPLICATED"[VARCHAR2,1], "T"."SHARDED"[VARCHAR2,1],
       "T"."CREATED_APPID"[NUMBER,22], "T"."CREATED_VSNID"[NUMBER,22], "T"."MODIFIED_APPID"[NUMBER,22],
       "T"."MODIFIED_VSNID"[NUMBER,22]

--//另外注意两个执行计划的A-time都是00:00:00.01,理论要看完整个结果集合,第2次执行至少要等上2,3秒,而实际看到的
--//A-Time=00:00:00.01,也就是我做的例子两者看执行计划时间差不多.第2个慢主要原因在于输出这个结果集上,会出现
--//大部分时间浪费在网络传输结果上.SQL*Net message from client,明天测试生产系统的情况.