【优化】SQL_TRACE之生成跟踪文件

一：跟踪文件：

Oracle跟踪文件分为三种类型，一种是后台报警日志文件，记录数据库在启动、关闭和运行期间后台进程的活动情况,如表空间创建、回滚段创建、某些alter命令、日志切换、错误消息等。在数据库出现故障时，应首先查看该文件，但文件中的信息与任何错误状态没有必然的联系。后台报警日志文件保存BACKGROUND_DUMP_DEST参数指定的目录中，文件格式为 SIDALRT.LOG。另一种类型是DBWR、LGWR、SMON等后台进程创建的后台跟踪文件。后台跟踪文件根据后台进程运行情况产生，后台跟踪文件也保存在BACKGROUND_DUMP_DEST参数指定的目录中，文件格式为siddbwr.trc、sidsmon.trc等。还有一种类型是由连接到Oracle的用户进程(Server Processes)生成的用户跟踪文件。这些文件仅在用户会话期间遇到错误时产生。此外，用户可以通过执行oracle跟踪事件（见后面）来生成该类文件，用户跟踪文件保存在USER_DUMP_DEST参数指定的目录中，文件格式为oraxxxxx.trc，xxxxx为创建文件的进程号（或线程号）

 

二：当SQL语句出现性能问题时，我们可以用SQL_TRACE来跟踪SQL的执行情况，通过跟踪，我们可以了解一条SQL或者PL/SQL包的运行情况，SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中，我们可以读这个trace 文件来了解在这个SQL执行过程中Oracle 都做了哪些操作。可以通过sql命令启动SQL_TRACE，或者在初始化参数里面。

 

三：有以下三种方法生成跟踪文件

1.SQL_TRACE

2.10046

3.DBMS_MONITOR包

 

四：Trace文件命名规则

9iR1：ora_xxxx.trc

9iR2以后：$ORACLE_SID_ora_xxxx.trc

 

实验：

sql_tarce测试：

启用SQL_TRACE

会话级别：alter session set sql_trace=true;

实例级：alter database set sql_trace=true;（不建议开启）

 

TRACE文件存放位置

10G: $ORACLE_BASE/admin/SID/udump

11G: $ORACLE_BASE/diag/rdbms/sid/SID/trace

 

1）——查看参数：

SYS@ORA11GR2>show parameter tracefile

（此参数为空，表示生成trace文件按系统默认方式生成tracefile，当然也可以自定义）

NAME                                 TYPE        VALUE

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

tracefile_identifier                 string

SYS@ORA11GR2>show parameter sql_trace

(value是false表示系统当前不会产生trace文件)

NAME                                 TYPE        VALUE

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

sql_trace                            boolean     FALSE

 

2）进入hr用户下进行操作生成trace文件：

自定义tracefile文件名：

HR@ORA11GR2>alter session set tracefile_identifier='my_trace';

 

Session altered.

 

开启当前会话，以产生trace文件：

HR@ORA11GR2>alter session set sql_trace=true;

 

Session altered.

 

进行相关操作以产生trace文件；

HR@ORA11GR2>select count(*) from hr.employees where job_id='SA_REP';

 

  COUNT(*)

----------

        30

关闭当前会话，不生成trace文件

HR@ORA11GR2>alter session set sql_trace=false;

 

Session altered.

 

3）查找trace文件位置

HR@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>show parameter dump

 

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/ora

                                                 11gr2/ORA11GR2/trace

 

4）查看生成的trace文件：

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$ ls *my_trace*trc

ORA11GR2_ora_17931_my_trace.trc

[oracle@wang trace]$

[oracle@wang trace]$ ls | grep my_trace

ORA11GR2_ora_17931_my_trace.trc

ORA11GR2_ora_17931_my_trace.trm

[oracle@wang trace]$

或者

SYS@ORA11GR2> SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

   FROM v$process a, v$session b, v$parameter c, v$instance d

  WHERE a.addr = b.paddr

    AND b.audsid = userenv('sessionid')

    AND c.name = 'user_dump_dest';

 

TRACE

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

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_2016.trc

 

SYS@ORA11GR2>

这个sql语句是个通用的sql，由于我们之前设置了trace的标识名，那么，我们用sql语句生得到的trace文件还需要加上标识名称才可以，或者修改一下sql语句如下：

SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || 'my_trace.trc' trace FROM ……

 

5）利用tkprof工具格式化trace文件

[oracle@wang trace]$ tkprof ORA11GR2_ora_17931_my_trace.trc my_trace.txt

 

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 12 21:32:17 2016

 

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

 

6）验证：

[oracle@wang trace]$ ls my_trace.txt

my_trace.txt

[oracle@wang trace]$

 

——查看格式化后的trace文件：

[oracle@wang trace]$ vi my_trace.txt

……省略……

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

SQL ID: fa48b614fg9d1 Plan Hash: 128828553

 

select count(*)

from

 hr.employees where job_id='SA_REP'

 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          1          1          0           1

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

total        4      0.00       0.00          1          1          0           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         1          1          1  SORT AGGREGATE (cr=1 pr=1 pw=0 time=2140 us)

        30         30         30   INDEX RANGE SCAN EMP_JOB_IX (cr=1 pr=1 pw=0 time=2127 us cost=1 size=270 card=30)(object id 88139)

 

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

……省略……

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

转载于:http://blog.itpub.net/31397003/viewspace-2126465/