【Oracle】一款非常好用的trace文件分析工具之二

  • 时间:
  • 浏览:0

  inflating: trca_e85484.html        

The output above indicates that the EMPLOYEE table does not have statistics.

... analyzing trace(s) ...

SQL> SELECT * FROM trca$_log_v;

Value passed to trcanlzr.sql:

sys@RAC>  @trcanlzr.sql  rac1_ora_17428.trc

File trca_e85484.zip has been created

就合适:

  adding: trca_e85484.log (deflated 83%)

sys@RAC> show parameter user_dump_dest

log file sync............................      1         0      0.01      0.01

total....................................     22         0     17.22    105.50     0

trca_e85484.txt   --以文本形式看出统计信息,和html的内容一样,本来形式是文本的方式。

  inflating: trca_e85484.log         

Parameter 1:

Event                                      Times     Count      Max.     Total    Blocks

trca_e85484.zip  trcanlzr.sql

To monitor progress, login as TRCANLZR into another session and execute:

------------------------------------ ----------- ------------------------------

  Length     Date   Time    Name

Archive:  trca_e85484.zip

oracle@rac1:/home/oracle/software/trca/run>sqlplus "/as sysdba"   

Copying now generated files into local directory

4.Trace Analyzer 提供更多的等候事件的细节,更有有助于于dba做出基于等候事件的优化方式

call      count     cpu   elapsed      disk     query  current          rows    misses

2. Trace Analyzer 提供热块,优化器分类分类整理索引和表的统计信息和一点的信息,而哪几种是tkprof所能不能不能不能提供的。

NAME                                 TYPE        VALUE

   373632                   4 files

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

oracle@rac1:/home/oracle/software/trca/run>ls

 --------                   -------

waited on                                 Waited   Zero Time    Wait    Waited  Accessed

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 8 19:57:15 2011

Execute     3      0.05    0.52       0        27       224          216         0

oracle@rac1:/home/oracle/software/trca/run>unzip trca_e85484.zip 

 --------    ----   ----    ----

使用trcanlzr.sql的前一天都要直接跟trace 文件名字,该工具默认会到user_dump_dest 底下去找(对于10g前一天的,会到bdump_dest 底下去找跟踪文件)

  adding: trcanlzr_error.log (deflated 81%)

Trace Analyzer 后后 显示如下形式:

Parse       1      0.00    0.00       0         0         0            0         0

TRACE_FILENAME: rac1_ora_17428.trc

deleting: trcanlzr_error.log

SQL*Net message to client (idle).........      3         0      0.00      0.00

TKPROF: Release 11.2.0.1.0 - Development on Sat Oct 8 19:57:34 2011

Archive:  trca_e85484.zip

----------------------------------------- --------- --------- -------   ------- --------

                                                 /rac1/trace

Explain Plan

下面的输出展示了sql句子及其执行计划和相关对象的统计信息。

SQL>EXIT

library cache pin........................      1         0      0.00      0.00

相对于tkprof的优势:

trca_e85484.html  --都要以网页形式查看统计信息

---------------------------------------------------------------

DELETE FROM SCOTT.EMPLOYEE

DELETE FROM HISTORY where ALERT_TIME

user_dump_dest                       string      /opt/rac/oracle/diag/rdbms/rac

Trace Filename or control_file.txt (required)

   115329  10-08-11 19:57   trca_e85484.txt

DELETE FROM HISTORY where ALERT_TIME

TRCANLZR completed.

------- -------- -------- -------- -------- --------- ---------  -----------  --------

------- ------- -------   -------- -------- --------- --------- ------------ ---------

...3 DELETE STATEMENT

    27117  10-08-11 19:57   trca_e85484.tkprof

...owner.index_name                  num rows     blocks     sample last analyzed date

total       4      0.05    0.52       0        27       224          216         0

Copyright (c) 1982, 509, Oracle and/or its affiliates.  All rights reserved.

  adding: trca_e85484.txt (deflated 87%)

  inflating: trca_e85484.txt         

...2 .DELETE OF 'SCOTT.EMPLOYEE

PL/SQL lock timer........................     15         0      5.01     75.08

trca_e85484.html  trca_e85484.log  trca_e85484.tkprof  trca_e85484.txt  trca_e85484.zip  trcanlzr.sql

Analyzing rac1_ora_17428.trc

test of trca_e85484.zip OK

前一篇文章介绍了咋样安装trca,接下来介绍咋样使用trca:都要进入 trca/run 目录底下 后后 把 /home/oracle/software/trca/run 装下 去去SQL_PATH环境变量底下:

SCOTT.EMPLOYEE..........................

------------------------------------ ---------- ---------- ---------- -------------------

  inflating: trca_e85484.tkprof      

显示的sql句子如下

3.Trace Analyzer与tkprof工具不同,Trace Analyzer 将普通用户的递归和系统内部管理的递归调用分开。

OWNER.TABLE_NAME

Trace Analyzer completed.

Review next trca_e85484.log for parsing messages and totals.

  adding: trca_e85484.html (deflated 90%)   执行分析脚本前一天,会产生四个文件,并打包成.zip文件

  adding: trca_e85484.tkprof (deflated 85%)

trca_e85484.log   --分析过程的日志

trca_e85484.tkprof --和常规的tkprof 产生的文件一样:记录了所有sql的执行计划,Parse,Execute,Fetch  。

oracle@rac1:/home/oracle/software/trca/run>ls

   215465  10-08-11 19:57   trca_e85484.html

1.  跟踪文件分析器能不能显示出确切的绑定变量的值,dba再本来用考虑sql运行时,变量的值具体是哪几种!

SQL*Net message from client (idle).......      2         0     17.22     50.21

0:"2/4/503 15:57:35" 1:1

    15721  10-08-11 19:57   trca_e85484.log

...1 ..TABLE ACCESS (FULL) OF ‘SCOTT.EMPLOYEE'

Review first trcanlzr_error.log file for possible fatal errors.