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

  • 时间:
  • 浏览:0
  • 来源:5分11选5官方_大发5分3D

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

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

显示的sql语录如下

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

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

total....................................     22         0     17.22    105.80     0

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

2. Trace Analyzer 提供热块,优化器架构设计 索引和表的统计信息和某些的信息,而哪几个是tkprof所可不也能了提供的。

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

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

DELETE FROM HISTORY where ALERT_TIME <= :b1  AND INSTANCE_NUMBER = :b2

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

相对于tkprof的优势:

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

TRACE_FILENAME: rac1_ora_17428.trc

使用trcanlzr.sql的随后 可不也能直接跟trace 文件名字,该工具默认会到user_dump_dest 里面去找(对于10g随后 的,会到bdump_dest 里面去找跟踪文件)

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

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

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

  adding: trca_e85484.log (deflated 83%)

4.Trace Analyzer 提供更多的在等待事件的细节,更有益于dba做出基于在等待事件的优化依据

Parameter 1:

  inflating: trca_e85484.html        

  Length     Date   Time    Name

DELETE FROM SCOTT.EMPLOYEE

trca_e85484.zip  trcanlzr.sql

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

Trace Analyzer completed.

... analyzing trace(s) ...

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

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

waited on                                 Waited   Zero Time    Wait    Waited  Accessed

sys@RAC> show parameter user_dump_dest

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

Trace Filename or control_file.txt (required)

...2 .DELETE OF 'SCOTT.EMPLOYEE

Execute     3      0.05    0.52       0        27       224          216         0

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

   373632                   4 files

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

  adding: trca_e85484.tkprof (deflated 85%)

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

  inflating: trca_e85484.tkprof      

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

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

前一篇文章介绍了何如安装trca,接下来介绍何如使用trca:前要进入 trca/run 目录里面 肯能把 /home/oracle/software/trca/run 插进SQL_PATH环境变量里面:

                                                 /rac1/trace

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

sys@RAC>  @trcanlzr.sql  rac1_ora_17428.trc

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

Archive:  trca_e85484.zip

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

  adding: trcanlzr_error.log (deflated 81%)

DELETE FROM HISTORY where ALERT_TIME <= :"2/4/803 15:57:35" AND INSTANCE_NUMBER = 1

3.Trace Analyzer与tkprof工具不同,Trace Analyzer 将普通用户的递归和系统组织组织结构的递归调用分开。

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

Review first trcanlzr_error.log file for possible fatal errors.

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

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

Parse       1      0.00    0.00       0         0         0            0         0

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

Explain Plan

call      count     cpu   elapsed      disk     query  current          rows    misses

就合适:

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

Review next trca_e85484.log for parsing messages and totals.

  inflating: trca_e85484.txt         

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

Copying now generated files into local directory

File trca_e85484.zip has been created

Archive:  trca_e85484.zip

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

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

Value passed to trcanlzr.sql:

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

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

Event                                      Times     Count      Max.     Total    Blocks

  inflating: trca_e85484.log         

SQL> SELECT * FROM trca$_log_v;

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

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

SQL>EXIT

OWNER.TABLE_NAME

  adding: trca_e85484.txt (deflated 87%)

NAME                                 TYPE        VALUE

TRCANLZR completed.

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

Trace Analyzer 肯能显示如下形式:

deleting: trcanlzr_error.log

Analyzing rac1_ora_17428.trc

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

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

total       4      0.05    0.52       0        27       224          216         0

test of trca_e85484.zip OK

...3 DELETE STATEMENT

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