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

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的步调相一致。

相关内容含义：

(1)解析含义

解析在跟踪文件中通常通过两个相邻的条目表示

第一个是PARSING IN CURSOR，第二个是PARSE。

PARSING IN CURSOR #140614507473560 len=30 dep=0 uid=0 oct=3 lid=0 tim=1598323647702391 hv=1715741776 ad='dd80da38' sqlid='br5bhw1m4892h'select count(*) from test.testEND OF STMTPARSE #140614507473560:c=46992,e=128564,p=6,cr=130,cu=0,mis=1,r=0,dep=0,og=1,plh=1950795681,tim=1598323647702390

(2)SQL具体执行过程以及CPU等性能指标

PARSE #140614507449024:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1598323647601076EXEC #140614507449024:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1598323647601322FETCH #140614507449024:c=1000,e=5896,p=1,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=299250003,tim=1598323647607235FETCH #140614507449024:c=0,e=8,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=299250003,tim=1598323647607293

(3)执行过程中发生的等待

WAIT #140614507449024: nam='db file sequential read' ela= 5664 file#=1 block#=81256 blocks=1 obj#=42 tim=1598323647607122

(4)commit和rollback和XCTEND条目格式

XCTEND rlbk=0, rd_only=0

 Oracle不需要客户端显示地开始一个事务，DBMS在第一个数据项目被修改或分布式操作执行后会自动打开一个事务

比如，通过dblink从一个表执行select

在trc中事务的边界通过XCTEND条目标记，格式如下：

XCTEND rlbk=[0-1],rd_only=[0-1]

(5)游标执行计划、统计信息与STAT条目格式

一组STAT条目的每一行代表了形成语句结果的行源

所谓的行源，指从索引或表中检索的数据或者多表连接的中间结果(因为必须先进行两表连接)。

10g以后，STAT条目仅在TIMED_STATISTICS=TRUE,并且SQL_TRACE=TRUE时才被写入。

注意，若STATISTICS_LEVEL=BASIC(缺省为TYPICAL)时会隐式设置TIMED_STATISTICS=FASLE。

STAT #140614507449024 id=1 cnt=1 pid=0 pos=1 obj=20 op='TABLE ACCESS BY INDEX ROWID ICOL$ (cr=4 pr=1 pw=0 time=5899 us cost=2 size=54 card=2)'

(6)      等待事件和WAIT条目格式

WAIT #140614507473560: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1598323647702555WAIT #140614507473560: nam='Disk file operations I/O' ela= 48 FileOperation=2 fileno=4 filetype=2 obj#=265662 tim=1598323647702747WAIT #140614507473560: nam='db file sequential read' ela= 3857 file#=4 block#=586 blocks=1 obj#=265662 tim=1598323647706628

(7) 绑定变量和BINDS条目格式

绑定变量的详细信息包括绑定变量的数据类型和值。

通过这些信息我们可以得到最大化的诊断。

例如，索引列的数据类型与绑定变量的数据类型不匹配，导致索引失效，CPU使用率增加，因为还存在隐式数据类型转换。

一个BINDS条目的结构由后面跟着游标编号的单词BINDS和每一个绑定变量单独的子部分组成

BINDS #140614507455464: Bind#0  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0  kxsbbbfp=7fe35dc9f8d8  bln=22  avl=02  flg=05  value=31 Bind#1  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24  kxsbbbfp=7fe35dc9f8f0  bln=22  avl=02  flg=01  value=11

当将绑定变量与子部分相关联时，不用关心数字，他们会被包含在绑定变量的名称中，例如 ":B"。

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********************************************************************************