oracle tkprof 分析sql执行计划

ce3aob544u

　　oracle   tkprof  分析sql执行计划
　　
　　
　　1)开启系统级sql追踪
　　alter system set sql_trace= true ;
　　select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr = b.addr and a.machine='yangyang（计算机名）';
　　
　　----http://www.iyunv.com/topic/19024  (部分引用)
SQL> select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr = b.addr and a.machine='yangyang'; 
　　SPID                SID    SERIAL#
------------ ---------- ----------
MACHINE
----------------------------------------------------------------
4007               5487          4
yangyang
　　4009               5483          1
yangyang
　　4013               5482          2
yangyang
　　SQL> execute sys.dbms_system.set_ev(49, 3406,10046,1,'');                                                       
　　SQL> execute sys.dbms_system.set_ev(5487,4,10046,1,'');
　　PL/SQL procedure successfully completed.
　　SQL> execute sys.dbms_system.set_ev(5487,4,10046,0,'');
　　
　　获取本机
　　2） 访问数据库后，要关闭追踪
　　alter system set sql_trace= false ;
　　
　　3）获得追踪计划保存的文件
　　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
　　可以制作成sql文件放在linux系统下 例如保存为 trace_sql.sql
　　
　　@/home/netmessage/trace_sql.sql
　　
SQL> @/home/netmessage/trace_sql.sql
 17  ;
　　TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/orcl/udump/orcl_ora_11822.trc
　　
　　
　　4）分析追踪计划 用tkprof （linux 下tkprof 报错，所以在xp下进行分析了一下，linux 找到tkprof 所在的bin 就可以了，应该是8i 以上 在oracle bin中有存在）
　　C:\Documents and Settings\Administrator>tkprof d:\sql.trc d:\sql.txt
　　
　　
　　
　　看一下执行计划的分析结果：
　　
　　
　　
TKPROF: Release 10.2.0.1.0 - Production on 星期一 3月 26 18:39:22 2012
　　Copyright (c) 1982, 2005, Oracle.  All rights reserved.
　　Trace file: d:\sql.trc
Sort options: default
　　********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
　　alter system set sql_trace=true
　　
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.06          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.06          0          0          0           0
　　Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************
　　SELECT PT.VALUE
FROM
 SYS.V_$SESSTAT PT WHERE PT.SID=:1 AND PT.STATISTIC# IN (7,47,50,54,134,335,
  336,337,341,342) ORDER BY PT.STATISTIC#
　　
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0
　　Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************
　　select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
  spare2
from
 obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
  and linkname is null and subname is null
　　
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          4          0           1
　　Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
　　Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=0 pw=0 time=71 us)
      1   INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=50 us)(object id 37)
　　********************************************************************************
　　select cols,audit$,textlength,intcols,property,flags,rowid
from
 view$ where obj#=:1
　　
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1
　　Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
　　
　　********************************************************************************
　　OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
　　call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.06          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.06          0          0          0           0
　　Misses in library cache during parse: 1
Misses in library cache during execute: 1
　　
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
　　call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          7          0           2
　　Misses in library cache during parse: 0
　　2  user  SQL statements in session.
    2  internal SQL statements in session.
    4  SQL statements in session.
********************************************************************************
Trace file: d:\sql.trc
Trace file compatibility: 10.01.00
Sort options: default
　　2  sessions in tracefile.
       3  user  SQL statements in trace file.
       2  internal SQL statements in trace file.
       4  SQL statements in trace file.
       4  unique SQL statements in trace file.
      45  lines in trace file.
      82  elapsed seconds in trace file.