explain plan，autotrace，tkprof，执行计划和静态统计信息的解读(转载自benx)

考自：http://benx.iteye.com/blog/559354地址
[/color][color=darkred] 
explain plan，autotrace，tkprof，执行计划和静态统计信息的解读    
    
    
    
1、  执行计划    
    
执行计划的设定    
conn sys/pwd@gx as sysdba;    
    
CREATE USER TOOL    
  IDENTIFIED BY tool    
  DEFAULT TABLESPACE EXAMPLE    
  TEMPORARY TABLESPACE TEMP    
  PROFILE DEFAULT    
  ACCOUNT UNLOCK;    
  -- 2 Roles for TOOL     
  GRANT RESOURCE TO TOOL;    
  GRANT CONNECT TO TOOL;    
  ALTER USER TOOL DEFAULT ROLE NONE;    
  -- 3 System Privileges for TOOL     
  GRANT CREATE SESSION TO TOOL;    
  GRANT CREATE TABLE TO TOOL;    
  GRANT UNLIMITED TABLESPACE TO TOOL;    
    
CREATE GLOBAL TEMPORARY TABLE tool.PLAN_TABLE    
(    
  STATEMENT_ID       VARCHAR2(30 BYTE),    
  PLAN_ID            NUMBER,    
  TIMESTAMP          DATE,    
  REMARKS            VARCHAR2(4000 BYTE),    
  OPERATION          VARCHAR2(30 BYTE),    
  OPTIONS            VARCHAR2(255 BYTE),    
  OBJECT_NODE        VARCHAR2(128 BYTE),    
  OBJECT_OWNER       VARCHAR2(30 BYTE),    
  OBJECT_NAME        VARCHAR2(30 BYTE),    
  OBJECT_ALIAS       VARCHAR2(65 BYTE),    
  OBJECT_INSTANCE    INTEGER,    
  OBJECT_TYPE        VARCHAR2(30 BYTE),    
  OPTIMIZER          VARCHAR2(255 BYTE),    
  SEARCH_COLUMNS     NUMBER,    
  ID                 INTEGER,    
  PARENT_ID          INTEGER,    
  DEPTH              INTEGER,    
  POSITION           INTEGER,    
  COST               INTEGER,    
  CARDINALITY        INTEGER,    
  BYTES              INTEGER,    
  OTHER_TAG          VARCHAR2(255 BYTE),    
  PARTITION_START    VARCHAR2(255 BYTE),    
  PARTITION_STOP     VARCHAR2(255 BYTE),    
  PARTITION_ID       INTEGER,    
  OTHER              LONG,    
  OTHER_XML          CLOB,    
  DISTRIBUTION       VARCHAR2(30 BYTE),    
  CPU_COST           INTEGER,    
  IO_COST            INTEGER,    
  TEMP_SPACE         INTEGER,    
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),    
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),    
  PROJECTION         VARCHAR2(4000 BYTE),    
  TIME               INTEGER,    
  QBLOCK_NAME        VARCHAR2(30 BYTE)    
)    
ON COMMIT PRESERVE ROWS;    
    
grant all on TOOL.PLAN_TABLE to public;    
    
CREATE PUBLIC SYNONYM PLAN_TABLE FOR TOOL.PLAN_TABLE;    
    
    
使用方法：    
truncate table PLAN_TABL;    
explain plan select * from emp;    
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));    
    
演示:    
conn scott/tiger    
SQL> explain plan for select * from dept where deptno=10;    
Explained    
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));    
    
PLAN_TABLE_OUTPUT    
--------------------------------------------------------------------------------    
Plan hash value: 3383998547    
--------------------------------------------------------------------------    
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |     1 |    16 |     4   (0)| 00:00:01 |    
|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    16 |     4   (0)| 00:00:01 |    
--------------------------------------------------------------------------    
Predicate Information (identified by operation id):    
---------------------------------------------------    
   1 - filter("DEPTNO"=10)    
    
13 rows selected    
    
执行计划解读:--估算表    
表v$sql_plan    
cost概念    
cardinality    
    
    
    
查询路径—估算树    
create table e    
as select * from emp    
    
create table d    
as    
select * from dept    
    
    
Explain plan for    
select ename,dname from d,e where e.deptno=d.deptno    
    
select * from table(dbms_xplan.display());    
    
    
Plan hash value: 1127375450    
     
---------------------------------------------------------------------------    
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
---------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |      |    15 |   630 |     7  (15)| 00:00:01 |    
|*  1 |  HASH JOIN         |      |    15 |   630 |     7  (15)| 00:00:01 |    
|   2 |   TABLE ACCESS FULL| D    |     4 |    88 |     3   (0)| 00:00:01 |    
|   3 |   TABLE ACCESS FULL| E    |    15 |   300 |     3   (0)| 00:00:01 |    
---------------------------------------------------------------------------    
     
Predicate Information (identified by operation id):    
---------------------------------------------------    
     
   1 - access("E"."DEPTNO"="D"."DEPTNO")    
     
Note    
-----    
   - dynamic sampling used for this statement    
    
Explain plan for    
select ename,dname from d, (select ename,deptno from e where rownum<2) e where e.deptno=d.deptno     
    
select * from table(dbms_xplan.display());    
    
Plan hash value: 1791846393    
     
-----------------------------------------------------------------------------    
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
-----------------------------------------------------------------------------    
|   0 | SELECT STATEMENT     |      |     1 |    42 |     5   (0)| 00:00:01 |    
|   1 |  NESTED LOOPS        |      |     1 |    42 |     5   (0)| 00:00:01 |    
|   2 |   VIEW               |      |     1 |    20 |     2   (0)| 00:00:01 |    
|*  3 |    COUNT STOPKEY     |      |       |       |            |          |    
|   4 |     TABLE ACCESS FULL| E    |    15 |   300 |     2   (0)| 00:00:01 |    
|*  5 |   TABLE ACCESS FULL  | D    |     1 |    22 |     3   (0)| 00:00:01 |    
-----------------------------------------------------------------------------    
     
Predicate Information (identified by operation id):    
---------------------------------------------------    
     
   3 - filter(ROWNUM<2)    
   5 - filter("E"."DEPTNO"="D"."DEPTNO")    
     
Note    
-----    
   - dynamic sampling used for this statement    
    
    
驱动表概念    
估算树    
    
     
从左到右 从下到上    
    
autotrace     
oracle_home\sqlplus\admin\    
    
conn sys/pwd@gx as sysdba;    
drop role plustrace;    
create role plustrace;    
    
grant select on v_$sesstat to plustrace;    
grant select on v_$statname to plustrace;    
grant select on v_$mystat to plustrace;    
grant plustrace to dba with admin option;    
grant plustrace  to public;    
    
grant select on v_$sesstat to public;    
grant select on v_$statname to public;    
grant select on v_$mystat to public;    
grant plustrace to dba with admin option;    
grant plustrace  to public;    
    
    
grant alter session to public;    
    
使用命令    
set autotrace on    
set autotrace off    
set autotrace on explain    
set autotrace on statistics    
set autotrace traceonly    
    
    
autotrace输出内容解释    
recursive calls：执行语句时、调用的oracle内部语句(如分析所用的sql)和其他语句（如触发器）。    
测试举例：    
举例1    
conn scott/tiger@gx    
set autotrace on    
alter system flush shared_pool    
set autotrace on    
select * from emp    
select * from emp    
    
    
举例2    
create table exchage_table    
(    
bill_code   number(10),    
exchage_rate  number(16,3)    
)    
BILL_CODE   EXCHAGE_RATE    
100 4.678    
200 5.235    
300 5.430    
400 2.654    
    
    
create or replace function today_exchage(p_code in number) return number is     
v_exange number(16,3);    
begin    
select exchage_rate into v_exange from exchage_table    
where bill_code=p_code;    
return v_exange;    
end;    
    
create table affair    
(    
trans_id  number(10),    
bill_code number(10),    
balance   number(16,2)    
)    
    
TRANS_ID    BILL_CODE   BALANCE    
1000    100 1234.00    
2000    200 4324.32    
3000    300 65464.23    
    
较好的写法为    
select      
trans_id,     
(select exchage_rate     
from exchage_table     
where bill_code=affair.bill_code )*balance     
from affair     
    
举例3    
drop table tppp purge       
create table tppp(p integer)    
    
create or replace trigger t_trigger    
  before insert on tppp      
  for each row    
declare    
  -- local variables here    
begin    
if :new.p>5 then    
raise_application_error(-20001,'bbbbbbbb');    
end if;    
      
end t_trigger;    
    
统计信息    
----------------------------------------------------------    
         29  recursive calls    
         19  db block gets    
         54  consistent gets    
          0  physical reads    
       1172  redo size    
        676  bytes sent via SQL*Net to client    
        627  bytes received via SQL*Net from client    
          3  SQL*Net roundtrips to/from client    
          1  sorts (memory)    
          0  sorts (disk)    
          9  rows processed    
    
在一次运行    
    
统计信息    
---------------------------------------------------------    
         29  recursive calls    
          0  db block gets    
        117  consistent gets    
          1  physical reads    
          0  redo size    
        483  bytes sent via SQL*Net to client    
        416  bytes received via SQL*Net from client    
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)    
          0  sorts (disk)    
          9  rows processed    
drop  trigger t_trigger;    
    
统计信息    
----------------------------------------------------------    
          0  recursive calls    
          0  db block gets    
        108  consistent gets    
          0  physical reads    
          0  redo size    
        483  bytes sent via SQL*Net to client    
        416  bytes received via SQL*Net from client    
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)    
          0  sorts (disk)    
          9  rows processed    
    
解决方法为    
1、  编写高效的trigger    
2、  用过程代替trigger    
    
举例4:    
自我管理表空间与数据字典表空间    
本地管理的表空间能够减少递归sql    
    
    
输出内容: 逻辑I/O  （DB BLOCKS| CONSISTENT GETS）    
解释    
    
对于一个SQL 逻辑I/O越小越好，通常通过SQL调整实现的    
    
    
    
TKPROF    
    
使用 TKPROF 工具简介    
TKPROF 工具简介    
TKPROF 工具的使用步骤    
TKPROF 工具如何分析 trace 文件    
启用TKPROF    
如何设置自动跟踪    
1、设定执行表，autotrace。方法如前所述，这里再重复一边。    
用system登录    
执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表    
执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色    
如果想计划表让每个用户都能使用，则    
SQL>create public synonym plan_table for plan_table;    
SQL> grant all on plan_table to public;    
    
2、设定tkprof    
ALTER SESSION  SET SQL_TRACE = TRUE    
ALTER SESSION  SET TIMED_STATISTICS = TRUE;    
alter session set events ‘10046 trace name context forever,level 12’;    
alter session  set max_dump_file_size=unlimited；    
alter session set events  '10046 trace name context off'    
    
    
    
获取跟踪文件名称    
跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称（适用于Win环境，如果是unix需要做一定修改）    
conn system/pwd    
    
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.trc' filename    
FROM    
v$process p,    
v$session s,    
v$parameter p1,    
v$parameter p2    
WHERE p1.name = 'user_dump_dest'    
AND p2.name = 'db_name'    
AND p.addr = s.paddr    
AND s.audsid = USERENV ('SESSIONID')    
    
在unix的目录下    
http://www.eygle.com/faq/script/gettrcnameunix.sql    
    
有了正确而详细的诊断数据之后，你需要以摘要的形式对其进行查看，这有助于你以最快的速度做出响应。    
Cmd tkprof path\xxx.prc xxx.txt    
    
报告解读：    
parse(分析)：在共享池中找到该查询（软分析）或者创建该查询的新计划（硬分析）    
execute(执行)：执行查询的所有工作    
fetch(提取)：显示select的提取工作，对于update，则没有内容    
    
count(计数):执行的次数    
cpu:此阶段cpu的耗时，以毫秒为单位    
elapsed(占用时间):挂钟时间，如果大于cpu时间，则有等待时间    
disk(磁盘)：执行物理I/O的次数    
QUERY(查询):检索一致性执行的I/O次数    
CURRENT(当前)：到当前多执行的逻辑I/O次数    
ROW:此阶段被处理或者受到影响的行    
    
如果一个UPDATE语句EXECUTE的QUERY,CURRENT，ROWS分别为2000 1000 500,表示这个语句访问了2000个块找到需要UPDATE的行记录，在UPDATE的时候只访问了1000个块，一共更新了500行。如果只获取很少的数据，而要访问了大量的块，表明SQL与需要优化了。    
    
    
MISSES 缓存命中率：0 表示已经通过软分析    
OPTIMIZER GOAL（优化程序目标）    
    
执行计划：与前面的执行计划相比，增加了各个阶段涉及的行数    
    
关闭    
alter system set events '10046 trace name context off';    
    
更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟踪：     
dbms_support.start_trace(waits=>;true, binds=>;true)     
/* code to be traced goes here */     
dbms_support.stop_trace()     
    
请注意DBMS_SUPPORT 没有文档说明，可能也不是数据库默认安装的一部分。要了解DBMS_SUPPORT的信息，请参考MetaLink ( metalink.oracle.com)。      
    
跟踪别人的代码。如果你想跟踪没有读/写权限的代码，则激活扩展SQL跟踪就有点麻烦了。但也不会难很多。你首先要获得你想跟踪的会话的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的过程调用，可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数：      
    
    
dbms_system.set_bool_param_in_session(     
   sid     =>; 42,     
   serial# =>; 1215,     
   parnam  =>; 'timed_statistics',     
   bval    =>; true)     
dbms_system.set_int_param_in_session(     
   sid     =>; 42,     
   serial# =>; 1215,     
   parnam  =>; 'max_dump_file_size',     
   intval  =>; 2147483647)     
    
    
（对于Oracle8 8.1.6以前的版本，你可以用ALTER SYSTEM命令处理这些参数。）      
    
接下来要激活跟踪。有几种方法可以采用，包括下面两个：     
    
方法一是使用DBMS_SUPPORT：      
    
    
dbms_support.start_trace_in_session(     
   sid     =>; 42,     
   serial# =>; 1215,     
   waits   =>; true,     
   binds   =>; true)     
/* code to be traced executes during this time window */     
dbms_support.stop_trace_in_session(     
  sid      =>; 42,     
  serial   =>; 1215)     
    
    
若想激活扩展SQL跟踪，请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程。该过程不允许在跟踪文件中指定等待和绑定的数据。      
    
第二种方法更为精致，但在Oracle数据库10g之前的版本中并不支持这种方法。 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题，这些问题是由连接共享和多线程操作所引起的。你可以在Oracle数据库10g中指定要跟踪的服务、模块或行动，而不指定要跟踪的Oracle数据库会话：      
    
    
dbms_monitor.serv_mod_act_trace_enable(     
  service_name  =>; 'APPS1',     
  module_name   =>; 'PAYROLL',     
  action_name   =>; 'PYUGEN',     
  waits         =>; true,     
  binds         =>; true,     
  instance_name =>; null)     
/* code to be traced executes during this time window */     
dbms_monitor.serv_mod_act_trace_disable(     
  service_name  =>; 'APPS1',     
  module_name   =>; 'PAYROLL',     
  action_name  =>; 'PYUGEN')     
    
    
利用DBMS_MONITOR包，Oracle可为要跟踪的特定的业务操作提供完全支持激活或停止诊断数据收集的方法。     
    
    
在PL/SQL中,由于不能执行alter session,可以使用    
　　    
　　dbms_session.set_sql_trace(TRUE);    
　　    
　　必须安装DBMS_SESSION包，并"直接"赋给用户alter session的权限。    
当我们使用sql    
For Unix:    
$ sqlplus "/ as sysdba"    
    
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 8 12:08:09 2004    
    
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.    
    
    
Connected to:    
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production    
With the Partitioning, OLAP and Oracle Data Mining options    
JServer Release 9.2.0.4.0 - Production    
    
SQL> set echo on    
SQL> @gettrcnameunix    
SELECT       d.VALUE    
         || '/'    
         || LOWER (RTRIM (i.INSTANCE, CHR (0)))    
         || '_ora_'    
         || p.spid    
         || '.trc' trace_file_name    
    FROM (SELECT p.spid    
            FROM v$mystat m, v$session s, v$process p    
           WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,    
         (SELECT t.INSTANCE    
            FROM v$thread t, v$parameter v    
           WHERE v.NAME = 'thread'    
             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,    
         (SELECT VALUE    
            FROM v$parameter    
           WHERE NAME = 'user_dump_dest') d    
TRACE_FILE_NAME    
--------------------------------------------------------------------------------    
/opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc    
    
For Nt:     
SELECT    d.VALUE    
        || '\'    
        || LOWER (RTRIM (i.INSTANCE, CHR (0)))    
        || '_ora_'    
        || p.spid    
        || '.trc' trace_file_name    
   FROM (SELECT p.spid    
           FROM v$mystat m, v$session s, v$process p    
          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,    
        (SELECT t.INSTANCE    
           FROM v$thread t, v$parameter v    
          WHERE v.NAME = 'thread'    
            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,    
        (SELECT VALUE    
           FROM v$parameter    
          WHERE NAME = 'user_dump_dest') d    
TRACE_FILE_NAME    
--------------------------------------------------------------------------------    
e:\oracle\admin\eygle\udump\eygle_ora_3084.trc   
explain

真正的唯一的限制是用户不能去解释其它用户的表，视图，索引或其它类型，用户必须是所有被解释事物的所有者，如果不是所有者而只有select权限，explain会返回一个错误。