ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【笔记】 sql_trace相关

【笔记】 sql_trace相关

原创 Linux操作系统 作者:yellowlee 时间:2009-05-19 21:03:09 0 删除 编辑

SQL> create table tt( id number);
 
Table created
 
SQL>  insert into tt select rownum from dba_objects;
 
39530 rows inserted
 

清除缓冲区:

SQL> alter session set events 'immediate trace name flush_cache level 1';


Session altered
 


SQL> alter session set sql_trace=true;
 
Session altered
 
SQL>
SQL> declare
  2   l_num number;
  3     begin
  4         for i in 1..39530
  5        loop
  6            select id into l_num from test.tt where id = i;
  7         end loop;
  8      end;
  9  /
 
PL/SQL procedure successfully completed
 
SQL> alter session set sql_trace=false;
 
Session altered

 

用tkprof查看trc文件:(disk和query注意,不清除缓冲区的话没有disk)

E:\oracle\admin\yellow\udump>tkprof yellow_ora_812.trc trace.txt print=100 record=sql.txt sys=no


TKPROF: Release 9.2.0.1.0 - Production on 星期二 5月 19 21:02:08 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: yellow_ora_812.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
********************************************************************************

The following statement encountered a error during parse:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       21      0.00       0.00          0          0          0           0
Execute 158139      1.73       1.55          0          0          0           0
Fetch   158149      1.18       1.05        182     316312          0      158140
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   316309      2.92       2.61        182     316312          0      158140
================
Error encountered: ORA-06576

 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       43      0.04       0.03          1         10          0           0
Execute     48      6.01       6.87          0          0          0          27
Fetch       11      0.00       0.00          0         33          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      102      6.06       6.90          1         43          0          38

Misses in library cache during parse: 2


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       22      0.00       0.00          0          0          0           0
Execute 197669      2.23       1.95          0          0          0           0
Fetch   197679      1.45       1.33        265     395372          0      197670
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   395370      3.68       3.30        265     395372          0      197670

Misses in library cache during parse: 15

   48  user  SQL statements in session.
   22  internal SQL statements in session.
   70  SQL statements in session.
********************************************************************************
Trace file: yellow_ora_812.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
      48  user  SQL statements in trace file.
      22  internal SQL statements in trace file.
      70  SQL statements in trace file.
      21  unique SQL statements in trace file.
  395849  lines in trace file.

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-600494/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 【转】 sql_trace
请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    659560