关于autotrace和explain plan是否可以反映真实的执行计划

最新推荐文章于 2016-04-17 01:39:00 发布

zhaosj1726

最新推荐文章于 2016-04-17 01:39:00 发布

阅读量860

点赞数

分类专栏：性能优化文章标签： object sql table access filter user

本文链接：https://blog.csdn.net/zhaosj1726/article/details/6108086

版权

性能优化专栏收录该内容

22 篇文章 0 订阅

订阅专栏

数据库版本,sql*plus版本都是10.2.0.4.0

SQL> CREATE TABLE ZSJ_OBJS AS SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID IS NOT NULL; SQL> UPDATE ZSJ_OBJS SET OWNER='SYS' where owner not in('SYS','SCOTT'); SQL> CREATE INDEX IND1_ZSJ_OBJS ON ZSJ_OBJS(OWNER); SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'ZSJ_OBJS',CASCADE=>TRUE,estimate_percent => 100,method_opt => 'FOR COLUMNS SIZE 254 OWNER'); SQL> SELECT COUNT(1) FROM ZSJ_OBJS; COUNT(1) ---------- 51126 SQL> select owner,count(1) from zsj_objs group by owner; OWNER COUNT(1) ------------------------------ ---------- SCOTT 6 SYS 51120 SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='ZSJ_OBJS'; NUM_ROWS ---------- 51126 SQL> SELECT COLUMN_NAME,NUM_NULLS,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,NUM_BUCKETS,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='ZSJ_OBJS'; COLUMN_NAME NUM_NULLS NUM_DISTINCT NUM_BUCKETS HISTOGRAM --------------------- ---------- ------------ ----------- --------------- OWNER 0 2 2 FREQUENCY OBJECT_NAME NONE SUBOBJECT_NAME NONE OBJECT_ID NONE DATA_OBJECT_ID NONE OBJECT_TYPE NONE CREATED NONE LAST_DDL_TIME NONE TIMESTAMP NONE STATUS NONE TEMPORARY NONE GENERATED NONE SECONDARY NONE SQL> desc zsj_objs; Name Null? Type -------------------------------- -------- ------------------------ OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> set autotrace on; SQL> select /*+ full(t)*/count(1) from zsj_objs t; SQL> / COUNT(1) ---------- 51126 Execution Plan ---------------------------------------------------------- Plan hash value: 4024628057 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 160 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| ZSJ_OBJS | 51126 | 160 (1)| 00:00:02 | ----------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 707 consistent gets 1 rows processed 全表扫描的话,逻辑IO是700多. SQL> VARIABLE V_OWNER VARCHAR2(20); SQL> EXEC :V_OWNER:='SCOTT'; SQL> EXPLAIN PLAN FOR SELECT /*+ ZHAOSJ*/COUNT(STATUS) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- Plan hash value: 4024628057 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 160 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 10 | | | |* 2 | TABLE ACCESS FULL| ZSJ_OBJS | 25563 | 249K| 160 (1)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"=:V_OWNER) SQL> set autotrace traceonly; SQL> SELECT /*+ ZHAOSJ*/COUNT(OBJECT_NAME) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; Execution Plan ---------------------------------------------------------- Plan hash value: 4024628057 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 71 | 160 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 71 | | | |* 2 | TABLE ACCESS FULL| ZSJ_OBJS | 25563 | 1772K| 160 (1)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"=:V_OWNER) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 1 rows processed 这里执行计划显示是全表扫描,可从执行统计信息来看,绝不可能是全表扫描 SQL> set autotrace traceonly explain SQL> SELECT /*+ ZHAOSJ*/COUNT(OBJECT_TYPE) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; Execution Plan ---------------------------------------------------------- Plan hash value: 4024628057 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 160 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 16 | | | |* 2 | TABLE ACCESS FULL| ZSJ_OBJS | 25563 | 399K| 160 (1)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"=:V_OWNER) SQL> set autotrace on; SQL> SELECT /*+ ZHAOSJ*/COUNT(OBJECT_ID) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; COUNT(OBJECT_ID) ---------------- 6 Execution Plan ---------------------------------------------------------- Plan hash value: 4024628057 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 160 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | TABLE ACCESS FULL| ZSJ_OBJS | 25563 | 449K| 160 (1)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"=:V_OWNER) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 1 rows processed SQL> set autotrace off; SQL> select SQL_TEXT,EXECUTIONS,SQL_ID,CHILD_NUMBER,loads from v$sql where sql_text like '%ZHAOSJ%' AND SQL_TEXT NOT LIKE '%V$SQL%'; SQL_TEXT EXECUTIONS SQL_ID CHILD_NUMBER LOADS ------------------------------------------------------------------------------------------------------------------------ ---------- ------------- ------------ ---------- SELECT /*+ ZHAOSJ*/COUNT(OBJECT_TYPE) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 0 a1432puphnh6d 0 1 SELECT /*+ ZHAOSJ*/COUNT(OBJECT_ID) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 1 a4dg408bk4s1v 0 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS649103' FOR SELECT /*+ ZHAOSJ*/COUNT(OBJECT_ID) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 1 g7xfu0r9cct97 0 1 EXPLAIN PLAN FOR SELECT /*+ ZHAOSJ*/COUNT(STATUS) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 1 bvu9dgfv4dknm 0 1 SELECT /*+ ZHAOSJ*/COUNT(OBJECT_NAME) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 1 9pp0np349u3m8 0 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS649103' FOR SELECT /*+ ZHAOSJ*/COUNT(OBJECT_TYPE) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 1 8zcy0xb6pv9a3 0 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS649103' FOR SELECT /*+ ZHAOSJ*/COUNT(OBJECT_NAME) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER 1 0j7shnwq4vn9s 0 1 7 rows selected. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9pp0np349u3m8',0,'ADVANCED')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------ SQL_ID 9pp0np349u3m8, child number 0 ------------------------------------- SELECT /*+ ZHAOSJ*/COUNT(OBJECT_NAME) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER Plan hash value: 3778349780 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 71 | | | | 2 | TABLE ACCESS BY INDEX ROWID| ZSJ_OBJS | 6 | 426 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND1_ZSJ_OBJS | 6 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :V_OWNER (VARCHAR2(30), CSID=852): 'SCOTT' Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"=:V_OWNER) SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a1432puphnh6d',0,'ADVANCED')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- SQL_ID a1432puphnh6d, child number 0 ------------------------------------- SELECT /*+ ZHAOSJ*/COUNT(OBJECT_TYPE) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER Plan hash value: 4024628057 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 160 (100)| | | 1 | SORT AGGREGATE | | 1 | 16 | | | |* 2 | TABLE ACCESS FULL| ZSJ_OBJS | 25563 | 399K| 160 (1)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"=:V_OWNER) SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a4dg408bk4s1v',0,'ADVANCED')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID a4dg408bk4s1v, child number 0 ------------------------------------- SELECT /*+ ZHAOSJ*/COUNT(OBJECT_ID) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER Plan hash value: 3778349780 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 18 | | | | 2 | TABLE ACCESS BY INDEX ROWID| ZSJ_OBJS | 6 | 108 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND1_ZSJ_OBJS | 6 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :V_OWNER (VARCHAR2(30), CSID=852): 'SCOTT' Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"=:V_OWNER) explain plan时都不使用绑定变量peeking机制,无论是显式执行的,还是隐式执行的 SQL> set autotrace traceonly; SQL> SELECT /*+ ZHAOSJ*/COUNT(OBJECT_NAME) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; 实际执行了一次,使用绑定变量peeking机制,但这里也执行了一次explain plan,实际上显示的执行计划是这次explain plan对应的执行计划 SQL> set autotrace traceonly explain SQL> SELECT /*+ ZHAOSJ*/COUNT(OBJECT_TYPE) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; 实际上并不执行,但硬分析了一次,也不使用绑定变量peeking机制,这里也执行了一次explain plan,实际上显示的执行计划是这次explain plan对应的执行计划还是硬分析时的执行计划呢?这个不好确定,因为它们是一样的,都不使用绑定变量peeking机制. SQL> set autotrace on; SQL> SELECT /*+ ZHAOSJ*/COUNT(OBJECT_ID) FROM ZSJ_OBJS WHERE OWNER=:V_OWNER; 实际执行了一次,使用绑定变量peeking机制,这里也执行了一次explain plan,实际上显示的执行计划是这次explain plan对应的执行计划.

简单来说,就是explain plan时不使用绑定变量peeking机制,所以它也许显示不了实际的执行计划.
autotrace traceonly explain会硬分析一次,但不执行,硬分析时也不使用绑定变量peeking机制,也就是说它也显示不了实际的执行计划.
其它的autotrace形式都会执行一次,使用绑定变量peeking机制,但随后显示出来的执行计划都不是实际的执行计划.