Oracle优化11-10046事件

来源：互联网发布：js dateadd函数编辑：程序博客网时间：2024/06/06 00:29

思维导图

这里写图片描述

Oracle优化10-SQL_TRACE解读

Oracle优化11-10046事件

10046事件概述

上一篇博文中我们说到了SQL_TRACE，10046同样也可以对SQL的执行计划进行分析，并且更加的强大。

10046事件并不是ORACLE官方提供给用户的使用命令，在官网中我们也找不到相关的信息，但是目前已经使用的非常广泛。

100046事件按照手机的内容分为4个等级

Level 1 等同于SQL_TRACE的功能
Level 4 在Level1的基础上增加了收集绑定变量的信息
Level 8 在Level 1的基础上增加等待事件的信息
Level 13 等同于Level 4 + level 8 ,即同时收集绑定变量和等待事件的信息。

Level 1

同于SQL_TRACE的功能

Level 4

案例演示：

##建表SQL> create table t as select * from dba_objects;Table created## 查看表分析的情况，第一次数据为空，说明没有做表分析SQL>  select num_rows, avg_row_len, blocks, last_analyzed  from user_tables  where table_name = 'T';  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED---------- ----------- ---------- -------------##表分析，收集数据SQL>  exec dbms_stats.gather_table_stats(user,'t');PL/SQL procedure successfully completed##再此查看查看表分析的情况，有数据啦 SQL>   select num_rows, avg_row_len, blocks, last_analyzed  from user_tables  where table_name = 'T';  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED---------- ----------- ---------- -------------     35234          93        495 2016-12-16 19##如果需要删除表分析后的数据，可执行以下sql,(在这里我并没有执行)SQL>  analyze table t  delete statistics##设定10046事件的级别为level 4 SQL> alter session set events '10046 trace name context forever ,level 4';Session altered## 因为Level 4 是增加绑定变量的统计信息，所以我们在这里演示以下--定义两个绑定变量x,y，然后让查询绑定这两个变量##定义变量SQL> variable x number;SQL> variable y varchar2;##赋值变量SQL> exec :x:=20;PL/SQL procedure successfully completedx---------20SQL> exec :y:='T';PL/SQL procedure successfully completedy---------T##执行有绑定变量的SQLSQL> select  object_id ,object_name from t where object_id=:x  or object_name=:y  2  ; OBJECT_ID OBJECT_NAME---------- --------  20      ICOL$  79348    Tx---------20y---------T##关闭10046事件SQL> alter session set events '10046 trace name context off';Session altered##查看当前会话的trace文件地址（每个会话生成的trace是不同的）SQL> select * from v$diag_info where name='Default Trace File';   INST_ID NAME                       VALUE---------- ------------------ --------------------------         1 Default Trace File   /oracle/diag/rdbms/cc/cc/trace/cc_ora_23847.trcSQL>

这样就完成了使用10046事件做SQL TRACE的工作。

注意： Level 4 可以获取绑定变量的信息，但是绑定变量的值只能在原始的trace文件中获取，在tkprof之后的文件里是看不到的。

部分原始TRACE文件信息如下：
这里写图片描述

我们可以看到 CURSOR #140353442255544 就是运行的绑定了变量的SQL。

首先执行的是分析，然后开始绑定变量，
Bind#0 表示第一个变量，VALUE=20就是这个变量的值
Bind#1 表示第二个变量，VALUE=”T”就是对应变量的值

分别对应

SQL> exec :x:=20;

SQL> exec :y:=’T’;

这样，把10046设置为4时，就可以捕获到SQL语句中的绑定变量后的变量值，这在分析一些绑定变量的SQL语句时是非常有用的，我们获取到了绑定变量的值就可以重新执行这条SQL，然后就可以分析它的执行计划是否合理。

Level 8

10046事件中 Level 8 的信息，没有绑定变量的值，但它比SQL_TRACE增加了等待事件的信息

SQL>  alter session set events '10046 trace name context forever ,level 8';Session alteredSQL>  select  object_id ,object_name from t where object_id=20  or object_name='T'; OBJECT_ID OBJECT_NAME---------- --------------        20 ICOL$     79348 TSQL> alter session set events '10046 trace name context off';Session alteredSQL>  select * from v$diag_info where name='Default Trace File';  INST_ID NAME                       VALUE---------- ------------------ --------------------------         1 Default Trace File                                  /oracle/diag/rdbms/cc/cc/trace/cc_ora_24846.trcSQL>

先看看原始的TRACE信息：

这里写图片描述

在10046 level=8的trace信息中，只有每个操作的等待事件信息，在这个TRACE文件中，

EXEC #139842323647264执行时有三个等待

WAIT #139842323647264: nam=’SQL*Net message to client’ —-客户端等待服务器端的反馈信息

WAIT #139842323647264: nam=’SQL*Net message from client’—服务器端等待客户端的请求

WAIT #139842323647264: nam=’SQL*Net message to client’ ——-客户端等待服务器端的反馈信息

FETCH #139842323647264:阶段有一个等待

WAIT #139842323647264: nam=’SQL*Net message from client’

等等………

因为这里发出的SQL操作的数据量比较小，所以看到的时间基本上都是消息在客户端和服务端传递时的等待，在实际的生产环境中，通常是一些数据访问时发生的等待。

使用TKPROF工具分析后的TRACE文件：

如果我们使用tkprof工具处理这个trace文件，可以看到下面的一个信息，实际上它是对原文件里的各类数据做了统计汇总，在tkprof之后的trace文件中，会多了一项是等待事件的信息.

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_24846.trc xiaogongjiang.txt sys=noTKPROF: Release 11.2.0.4.0 - Development on Fri Dec 16 21:14:54 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

这里写图片描述

最后一部分就是这个SQL语句执行过程中产生的等待事件。

Level 12

SQL> alter session set events '10046 trace name context forever ,level 12';Session alteredSQL> variable x number;SQL>  variable y varchar2;SQL> exec :x:=20;PL/SQL procedure successfully completedx---------20SQL> exec :y:='T';PL/SQL procedure successfully completedy---------TSQL> select  object_id ,object_name from t where object_id=:x  or object_name=:y ; OBJECT_ID OBJECT_NAME---------- --------------------------------------------------------------------------------        20 ICOL$     79348 Tx---------20y---------TSQL> alter session set events '10046 trace name context off';Session alteredSQL> select * from v$diag_info where name='Default Trace File';   INST_ID NAME                       VALUE---------- ------------------ ------------------------         1 Default Trace File            /oracle/diag/rdbms/cc/cc/trace/cc_ora_24941.trcSQL>

查看生成的trace文件部分

这里写图片描述

其实我们不难发现， Level 12 = Level 4 + Level 8 . 既包含了绑定变量，也包含了等待事件的信息。

做个tkprof看下

oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_24941.trc xgj.txt sys=noTKPROF: Release 11.2.0.4.0 - Development on Fri Dec 16 21:28:17 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

这里写图片描述

对其他会话进行TRACE

在实际生产中，我们很有可能需要对其他会话做Trace. Oracle 提供了以下的方式对其他会话进行跟踪。

SQL_TRACE

SQL> select  a.SID ,a.SERIAL#   from v$session a where a.SID = 522;       SID    SERIAL#---------- ----------       522        151

这里写图片描述

报错：

PLS-00201: identifier ‘SYS.DBMS_SYSTEM’ must be declared

解决：

使用SYS用户将execute权限赋给当前用户

SQL> conn sys/system as sysdba;Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as sys@cc AS SYSDBASQL> grant execute on sys.dbms_system to xgj;Grant succeeded

步骤：

会话A

##根据SID查找相关信息 ---会话ASQL> select sid from v$mystat where rownum=1;       SID----------       459SQL> select  a.SID ,a.SERIAL#   from v$session a where a.SID = 459;       SID    SERIAL#---------- ----------       459        225

会话B

跟踪会话A的SQL执行情况：

 ##开启跟踪  --会话BSQL>  exec sys.dbms_system.set_sql_trace_in_session(459,225,true);PL/SQL procedure successfully completed

回切到会话A

执行SQL

elect  a.param ,a.param_name ,a.current_value  from system_param  a  where a.param = 802 ;

至此，就可以生成trc文件了。

会话B

切到会话B：##关闭跟踪， 直接退出session也可以关闭SQL> exec sys.dbms_system.set_sql_trace_in_session(522,151,false);PL/SQL procedure successfully completed

10046事件

在会话Ｂ中对会话Ａ进行trace.　

会话A

SQL> select sid from v$mystat  where rownum =1;       SID----------       965SQL> select  a.SID ,a.SERIAL#  from v$session a where a.sid = 965;       SID    SERIAL#---------- ----------       965       4599

会话B

SQL> exec sys.dbms_monitor.session_trace_enable(965,4599,waits => true,binds => true);PL/SQL procedure successfully completed

会话Ａ

SQL> select  a.cust_id ,a.cust_name ,a.cust_type from cust a  where a.cust_id=11002;CUST_ID         CUST_NAME   CUST_TYPE                                                  ------------- ------------- ---------11002            test1205    A

至此，就可以查看到trace文件了。

会话Ｂ：

SQL>  exec sys.dbms_monitor.session_trace_disable(965,4599);PL/SQL procedure successfully completed

trace文件：

oracle@entel2:[/oracle/diag/rdbms/cc/cc/trace]$ls *29076*cc_ora_29076.trc  cc_ora_29076.trm

查看生成的文件：

路径：

select * from v$diag_info where name='Default Trace File';或者select value from v$parameter where name = 'user_dump_dest';

这里写图片描述

文件名：

select s.SID,       s.SERVER,       lower(case               when s.SERVER in ('DEDICATED', 'SHARED') then                i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||                p.SPID || '.trc'               else                null             end) as trace_file_name  from v$instance      i,       v$session       s,       v$process       p,       v$px_process    pp,       v$shared_server ss where s.PADDR = p.ADDR   and s.SID = pp.SID(+)   and s.PADDR = ss.PADDR(+)   and s.TYPE = 'USER'   and s.SID = '459' order by s.SID ;

这里写图片描述

路径+文件名称就是一个完整的trace文件啦

到ORACLE服务器对应的目录下查看

oracle@entel2:[/oracle/diag/rdbms/cc/cc/trace]$ls *26640*cc_ora_26640.trc  cc_ora_26640.trm

取到后就可以使用tkprof分析了。

关于DBLINK操作中的trace问题

如果一条SQL语句包含了DBLINK，如果我们想对这条SQL做trace，在本地只能trace到本地的信息，而对于远端的SQL，由于它运行在远端的数据库上，我们如果想要获取它的信息，需要到远端的数据库上，找到运行这条SQL语句的session,然后对它做trace。

另外这条SQL的执行计划也只能从远端服务器上获取到。

总结

当我们能够定位到某个SQL语句的性能发生了问题的时候，使用SQL_TRACE或者10046事件这两个工具将非常有用。

如果我们无法知道哪个SQL语句发生了性能问题，系统表现文整体性能的下降，那么做一个STATSPACK或者AWR报告将更加的合适，它能够对整个数据库的实例运行情况进行采样分析。

1 0