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

来源：这里教程网时间：2026-03-03 18:21:00 作者：

[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,明天测试生产系统的情况.