[20230130]toad看執行計劃注意.txt

[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,明天測試生產系統的情況.