oracle tkprof 分析sql执行计划

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.iteye.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.