Maison  >  Article  >  base de données  >  如何使用SQL_TRACE和10046事件

如何使用SQL_TRACE和10046事件

WBOY
WBOYoriginal
2016-06-07 15:57:341220parcourir

提到了使用SQL_TRACE和10046事件。SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段,是强有力的辅助诊断工具。在日常的数据库问题

Eygle大师的微信讲堂昨天开课,第一堂课和大家分享了一些学习Oracle的基本方法,其中提到了使用SQL_TRACE和10046事件。SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段,是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中,SQL_TRACE是非常常用的方法。

对于这个工具,我很早就听过,但是从来就没用过,“纸上得来终觉浅,绝知此事要躬行”,操练起来。

1.环境准备

      我们在Oracle11g中进行测试。

SQL>
 
SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

2.启用SQL_TRACE
      在Oracle中初始化设置中SQL_TRACE默认是关闭的,它可以作为初始化参数在全局启用,也可以通过命令行方式在具体session启用。
 

1. 在全局启用

在参数文件(pfile/spfile)中指定:

SQL_TRACE =true

在全局启用SQL_TRACE会导致所有进程的活动被跟踪,包括后台进程及所有用户进程,这通常会导致比较严重的性能问题,所以在生产环境中要谨慎使用,这个参数在10g之后是动态参数,可以随时调整,在某些诊断中非常有效。

提示: 通过在全局启用SQL_TRACE,,我们可以跟踪到所有后台进程的活动,很多在文档中的抽象说明,通过跟踪文件的实时变化,我们可以清晰的看到各个进程之间的紧密协调。

2. 在当前session级设置
      大多数时候我们使用SQL_TRACE跟踪当前进程.通过跟踪当前进程可以发现当前操作的后台数据库递归活动(这在研究数据库新特性时尤其有效),研究SQL执行,发现后台错误等。

    我在测试中启用session级别的SQL_TRACE,如下所示。


SQL>
 
SQL> show parameter SQL_TRACE

NAME TYPE VALUE

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

SQL_TRACE boolean FALSE

SQL>

SQL> alter session set SQL_TRACE=true;

Session altered.

SQL>

SQL> show parameter SQL_TRACE

NAME TYPE VALUE

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

SQL_TRACE boolean TRUE

SQL>

 
3.连接soctt用户,执行查询语句
    登陆scott用户,执行两条简单的查询语句。


[oracle@hoegh admin]$ sqlplus scott/tiger
 


SQL*Plus: Release 11.2.0.3.0 Production on Wed May 27 09:59:48 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from cat;

TABLE_NAME TABLE_TYPE

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SQL> select * from dept;

    DEPTNO DNAME LOC

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

        10 ACCOUNTING NEW YORK

        20 RESEARCH DALLAS

        30 SALES CHICAGO

        40 OPERATIONS BOSTON


4.生成trace文件

plustrace角色

      和Oracle10g一样,11g中plustrace角色默认也是disabled的。如果使用非授权用户打开Oracle trace功能会得到以下的错误。


SQL>
 
SQL> show user

USER is \"SCOTT\"

SQL>

SQL> set autotrace on

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

SQL>
      这时需要执行$ORACLE_HOME/sqlplus/admin/plustrce.sql脚本,手工创建plustrace角色,在此不做演示。因为我们更多的时候是需要跟踪其他用户的进程,而很多这样的用户可能没有被授予或者不允许授予plustrace角色。这时可以使用DBMS_SYSTEM包来实现对进程的跟踪,这儿需要提供用户进程的sid和serial#。
10046事件
    在这儿就不得不提到10046事件,10046事件是Oracle提供的内部事件,是对SQL_TRACE的增强。
 

10046事件可以设置以下四个级别:

1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE

4 - Level 1 加上绑定值(bind values)

8 - Level 1 + 等待事件跟踪

12 - Level 1 + Level 4 + Level 8
      和SQL_TRACE类似,10046事件可以在全局设置,也可以在session级设置。

生成trace文件
    首先,我们通过查询v$session视图获取scott用户进程的sid和serial#;
    然后执行dbms_system.set_ev过程来实现对进程的跟踪。


SQL>
 
SQL> select sid,serial#,username from v$session where username=\'SCOTT\';

      SID SERIAL# USERNAME

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

        21 2615 SCOTT

SQL>

SQL> exec dbms_system.set_ev(21,2615,10046,12,\'SCOTT\');

PL/SQL procedure successfully completed.

SQL>
 

5.查看trace文件

存放目录

      在11g中trace文件的存放目录有了变化,其中,11gR1 或 11gR1 以上版本可以通过查询diagnostic_dest参数获得;而11gR1以前版本则是通过user_dump_dest参数来指定。


SQL> show parameter diagnostic_dest
 


NAME TYPE VALUE

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

diagnostic_dest string /u01/app/oracle

SQL>
      在测试数据库中,trace文件的具体路径为:/u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/。

Déclaration:
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn