【筆記】 sql_trace相關

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/，如需轉載，請註明出處，否則將追究法律責任。