explain plan，autotrace，tkprof

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