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

  • 时间:
  • 浏览:4
  • 来源:大发5分6合APP下载_大发5分6合APP官网

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

  inflating: trca_e85484.log         

Event                                      Times     Count      Max.     Total    Blocks

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

Value passed to trcanlzr.sql:

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

                                                 /rac1/trace

前一篇文章介绍了如保安装trca,接下来介绍如保使用trca:都要进入 trca/run 目录上端 将会把 /home/oracle/software/trca/run 装进去 SQL_PATH环境变量上端:

... analyzing trace(s) ...

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

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

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

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

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

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

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

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

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

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

  inflating: trca_e85484.tkprof      

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

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

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

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

test of trca_e85484.zip OK

DELETE FROM HISTORY where ALERT_TIME

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

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

TRACE_FILENAME: rac1_ora_17428.trc

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

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

  adding: trca_e85484.tkprof (deflated 85%)

Parameter 1:

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

  inflating: trca_e85484.html        

call      count     cpu   elapsed      disk     query  current          rows    misses

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

sys@RAC> show parameter user_dump_dest

Analyzing rac1_ora_17428.trc

OWNER.TABLE_NAME

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

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

...2 .DELETE OF 'SCOTT.EMPLOYEE

Archive:  trca_e85484.zip

就离米 :

显示的sql语句如下

Parse       1      0.00    0.00       0         0         0            0         0

  inflating: trca_e85484.txt         

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

NAME                                 TYPE        VALUE

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

Trace Analyzer 将会显示如下形式:

   373632                   4 files

DELETE FROM SCOTT.EMPLOYEE

Explain Plan

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

4.Trace Analyzer 提供更多的守候事件的细节,更不不利于dba做出基于守候事件的优化办法

File trca_e85484.zip has been created

  adding: trca_e85484.txt (deflated 87%)

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

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

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

SQL> SELECT * FROM trca$_log_v;

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

SQL>EXIT

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

Copying now generated files into local directory

deleting: trcanlzr_error.log

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

  adding: trca_e85484.log (deflated 83%)

...3 DELETE STATEMENT

total       4      0.05    0.52       0        27       224          216         0

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

DELETE FROM HISTORY where ALERT_TIME

Trace Filename or control_file.txt (required)

trca_e85484.zip  trcanlzr.sql

相对于tkprof的优势:

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

total....................................     22         0     17.22    105.500     0

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

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

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

TRCANLZR completed.

waited on                                 Waited   Zero Time    Wait    Waited  Accessed

trca_e85484.html  --不让 以网页形式查看统计信息

sys@RAC>  @trcanlzr.sql  rac1_ora_17428.trc

使用trcanlzr.sql的很久不让 直接跟trace 文件名字,该工具默认会到user_dump_dest 上端去找(对于10g很久的,会到bdump_dest 上端去找跟踪文件)

Archive:  trca_e85484.zip

Review first trcanlzr_error.log file for possible fatal errors.

  adding: trcanlzr_error.log (deflated 81%)

  Length     Date   Time    Name

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

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

Execute     3      0.05    0.52       0        27       224          216         0

Review next trca_e85484.log for parsing messages and totals.

Trace Analyzer completed.