oracle oci.dll文件路径_Oracle的10046事件详解

最新推荐文章于 2021-04-03 03:17:23 发布

weixin_39534759

最新推荐文章于 2021-04-03 03:17:23 发布

阅读量317

点赞数

10046事件是Oracle提供的内部事件，是增强版的SQL_TRACE。

一、10046的级别

不同的Level对应不同的跟踪级别：

0级：SQL_TRACE=FASLE。

1级：SQL_TRACE=TRUE，默认级别。启用标准的SQL_TRACE功能 ( 默认) 包含了 SQL语句、响应时间、服务时间、处理的行数，物理读和写的数目、执行计划以及其他一些额外信息。到版本10.2中执行计划写入到 trace 的条件是仅当相关游标已经关闭时，且与之相关的执行统计信息是所有执行次数的总和数据。到版本11.1中仅在每次游标的第一次执行后将执行计划写入到trace ，执行统计信息仅仅和这第一次执行相关。

4级：比level 1时多出绑定变量的trace。

8级：比level 1多出等待事件，特别对于9i中指出 latch free等待事件很有用，对于分析全表扫描和索引扫描很有用。

12级：4级+8级，比level 1多出绑定变量和等待事件。

16级：在11g中为每一次执行生成STAT信息，仅在11.1之后可用

32级：比level1少执行计划

64级：和level1相比在第一次执行后还可能生成执行计划信息；条件是某个游标在前一次执行的前提下运行耗时变长了一分钟。仅在 11.2.0.2中可用。

Level 28 (4+8+16) 代表同时启用level4、level8、level16。

level 68 ( 64+4 )代表同时启用level64、level4

对于4级的10046，若用tkprof格式化，则会隐藏每一点SQL语句在做什么以及怎么做。

对于8级的10046，等待事件得到的比较多，此时我们不妨用tkprof对等待事件进行汇总。

所以，理解扩展SQL跟踪文件的格式，是每一个面临性能问题或故障排除任务的数据库工程师必备技能。

二、10046 trace文件路径

从11gR1开始，Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。可以用以下命令，获取DIAGNOSTIC_DEST的位置：

SQL> show parameter diagnostic_dest;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------diagnostic_dest                  string       /oracle/app/oracle

11gR1以前，如果是用户进程，10046 trace文件会被生成在user_dump_dest下，如果是后台进程，trace文件会被生成在background_dump_dest下：

SQL> show parameter user_dump_dest ;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------user_dump_dest                       string       /oracle/app/oracle/diag/rdbms/xddb/xddb/traceSQL> show parameter background_dump_dest;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------background_dump_dest                 string       /oracle/app/oracle/diag/rdbms/xddb/xddb/trace

注：

通过设定tracefile_identifier即alter session set tracefile_identifier='my_trace' ，通过这个设置可以帮助我们更容易的找到生成的trace文件。

通过SQL查看当前session的trace文件位置：

select a.value || '/' || b.instance_name || '_ora_' || c.spid || '.trc' trace_file  from (select value from v$parameter where name = 'user_dump_dest') a,       (select instance_name from v$instance) b,       (select spid          from v$process         where addr =               (select paddr                  from v$session                 where sid = (select distinct sid from v$mystat))) c;或：select tracefile  from v$process where addr = (select paddr                 from v$session                where sid = (select distinct sid from v$mystat));

三、10046 trace相关参数

1.timed_statistics

timed_statistics这个参数决定了是否收集与时间相关的统计信息，如果这个参数为FALSE的话，那么SQL Trace的结果基本没有多大的用处，默认情况下这个参数设置为TRUE。

SQL> show parameter timed_statistics ;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------timed_statistics                 boolean       TRUESQL> ALTER SESSION SET timed_statistics=true ;

2.max_dump_file_size

max_dump_file_size这个参数指定dump文件的大小，也就是决定是否限制SQL Trace文件的大小，在一个很忙的系统上面做SQL Trace的话可能会生成很多的信息，因此最好在会话级别将这个参数设置成unlimited(默认)。

SQL> show parameter max_dump_file_size;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------max_dump_file_size                 string       unlimitedSQL> ALTER SESSION SET max_dump_file_size=unlimited ;

3. tracefile_identifier

tracefile_identifier这个参数给Trace文件设置识别字符串，设置一个易读的字串能更快的找到Trace文件。

SQL> show parameter tracefile_identifier;

NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------tracefile_identifier                 string当前session启用：SQL> ALTER SESSION SET tracefile_identifier=’my_trace’;

4. diagnostic_dest

diagnostic_dest这个参数11g新增的，用于控制存放trace文件与core文件的路径，默认是$ORACLE_BASE目录。

SQL> show parameter diagnostic_dest;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------diagnostic_dest                  string       /oracle/app/oracle

5. user_dump_dest

user_dump_dest参数指定用户进程trace文件目录。

SQL> show parameter user_dump_dest ;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------user_dump_dest                       string       /oracle/app/oracle/diag/rdbms/xddb/xddb/trace SQL> alter system set user_dump_dest ='/oracle/app/oracle/diag/rdbms/xddb/xddb/trace' scope=both; 更改其目录

6. background_dump_dest

background_dump_dest参数用于后台进程产生的trace文件存储目录下。

SQL> show parameter background_dump_dest ;NAME                             TYPE       VALUE------------------------------------ ----------- ------------------------------background_dump_dest                 string       /oracle/app/oracle/diag/rdbms/xddb/xddb/traceSQL> alter system setbackground_dump_dest='/oracle/app/oracle/diag/rdbms/xddb/xddb/trace' scope=both; 更改其目录

四、10046 trace设置方法

4.1全局设置

在参数文件中增加:

event="10046 trace name context forever,level 12"

此设置对所有用户的所有进程生效、包括后台进程。

如果要关闭可以通过移除这个参数并且重启实例，或者使用下面的alter system命令可以关闭跟踪。

 SQL> alter system set events '10046 trace name context off';

4.2实例级别跟踪

在实例级别设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响，这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪，系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候，在这种情况下，可以打开系统层跟踪小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息，用以下命令打开系统层的跟踪：

 SQL> alter system set events '10046 trace name context forever,level 12'; 用以下命令关闭在所有session中的10046跟踪： SQL> alter system set events '10046 trace name context off';

4.3对当前session设置

(1)语法：

alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level levelnumber] : .......'

(2)通过alter session的方式修改，需要alter session的系统权限:

SQL> alter session set tracefile_identifier='my_trace_10046';       --trace文件标识SQL> alter session set statistics_level=all;               --实例级别设置需要注意，会消耗大量的CPUSQL> alter session set timed_statistics = true;      --默认是trueSQL> alter session set max_dump_file_size = unlimited;       --默认是unlimitedSQL> alter session set events '10046 trace name context forever, level 12'; --开启level 12级别的10046事件。Session altered.SQL> select count(*) from test.test;  COUNT(*)----------    227626SQL> alter session set events '10046 trace name context off';Session altered.

(3)查看session sql trace文件

SQL> select tracefile  from v$process where addr = (select paddr                 from v$session                where sid = (select distinct sid from v$mystat));TRACEFILE--------------------------------------------------------------------------------/oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc

4.4对其他用户session设置

4.4.1通过DBMS_SYSTEM.SET_EV系统包来实现

SQL> desc dbms_system...PROCEDURE SET_EV Argument Name                  Type                  In/Out Default? ------------------------------ ----------------------- ------ -------- SI                        BINARY_INTEGER            IN SE                        BINARY_INTEGER            IN EV                        BINARY_INTEGER            IN LE                        BINARY_INTEGER            IN NM                        VARCHAR2            IN...

其中的参数SI(SID)、SE(SERIAL#)、NM(username)来自v$session视图:

SQL> select sid,serial#,username from v$session;SID SERIAL# USERNAME---------- ---------- ------------------------------       5      52733 TEST    492       4299 SYS执行跟踪:SQL> exec dbms_system.set_ev(5,52733,10046,12,'');PL/SQL procedure successfully completed.SQL> exec dbms_system.set_sql_trace_in_session(5,52733,true); --有时候需要提前开启SQL_TRACE才可以生成trace文件。PL/SQL procedure successfully completed

测试执行SQL语句：

SQL> select * from tab;TNAME                         TABTYPE      CLUSTERID------------------------------ ------- ----------COURSE                         TABLESC                         TABLESTUDENT                    TABLETEACHER                    TABLETEST                         TABLE5 rows selected.SQL> select * from course;      C_ID C_NAME                          T_ID---------- -------------------------------- ----------       1 语文                              1       2 数学                              2       3 英语                              3       4 物理                              4

结束跟踪：

SQL> exec dbms_system.set_sql_trace_in_session(5,52733,false);SQL> exec dbms_system.set_ev(5,52733,10046,0,'');PL/SQL procedure successfully completed.

获取trace文件：

SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=5);

读取当前session设置的参数：

declare lev integer; begin   dbms_system.read_ev('10046',lev);   dbms_output.put_line(lev); end;/

针对所有event：

SQL> set feedback offSQL> set serveroutput onSQL> declareevent_level number;beginfor event_number in 10000..10999 loopsys.dbms_system.read_ev(event_number, event_level);if (event_level > 0) thensys.dbms_output.put_line('Event ' ||to_char(event_number) || ' is set at level ' || to_char(event_level) ); end if; end loop; end; /

4.4.2通过DBMS_MONITOR.SESSION_TRACE_ENABLE实现

PROCEDURE SESSION_TRACE_ENABLE Argument Name                     Type                     In/Out Default? ------------------------------ ----------------------- ------ -------- SESSION_ID                     BINARY_INTEGER              IN     DEFAULT SERIAL_NUM                     BINARY_INTEGER              IN     DEFAULT WAITS                            BOOLEAN               IN     DEFAULT BINDS                            BOOLEAN               IN     DEFAULT PLAN_STAT                     VARCHAR2              IN     DEFAULT-- 实例

-- 开启级别为12的Trace，当前会话EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>true,BINDS=>true);-- 开启级别为12的Trace，其他会话EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(:sid, :serial, true, true);-- 关闭Trace，当前会话EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();-- 关闭Trace，其他会话EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(:sid, :serial);

4.4.3 10046事件进程级别跟踪

假设需要对9834号进程做跟踪，其语法如下所示：

conn / as sysdbaalter session set tracefile_identifier = 'trace_10046'oradebug setospid=9834oradebug unlimitoradebug evnet 10046 trace name context forever,level 12oradebug dump errorstack 3oradebug dump errorstack 3oradebug dump errorstack 3oradebug tracefile_nameoradebug evnet 10046 trace name context off

五、分析trace文件

5.1 可直接查看

more /oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc

trace 文件可以看出 sql 语句经过了 parse(解析) -> exec(执行) -> fetch(从游标中获取数据) 几个过程。这3个分类与通过调用DBMS_SQL的子例程DBMS_SQL.PARSE,DBMS_SQL.EXECUTE,DBMS_SQL.FETCH_ROWS来跑SQL的步调相一致。

5.2 tkprof工具查看

tkprof /oracle/app/oracle/diag/rdbms/xddb/xddb/trace/xddb_ora_6888_my_trace_10046.trc  tkprof_10046.txtSQL ID: br5bhw1m4892h Plan Hash: 1950795681select count(*)from test.testcall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.01          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.14       0.22       3135       3139          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.14       0.24       3135       3139          0           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         1          1          1  SORT AGGREGATE (cr=3139 pr=3135 pw=0 time=227352 us)    227626     227626     227626   TABLE ACCESS FULL TEST (cr=3139 pr=3135 pw=0 time=684591 us cost=855 size=0 card=227626)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                       2        0.00          0.00  Disk file operations I/O                        1        0.00          0.00  db file sequential read                         1        0.00          0.00  db file scattered read                         40        0.01          0.10  SQL*Net message from client                     2       12.47         12.47********************************************************************************