作者介绍 蒋健，云趣网络科技联合创始人，11g OCM，多年Oracle设计、管理及实施经验，精通数据库优化，Oracle CBO及并行原理，曾为多个行业的客户的 Oracle 系统实施小型机到 X86跨平台迁移和数据库优化服务。云趣鹰眼监控核心设计和开发者，资深Python Web开发者。 通常，DBA 确定通过 Oracle 的**活动会话图确定 Top SQL，有了 SQL 的执行会话信息和 SQL 文本，可以和开发人员确定 Top SQL 来自哪些应用模块的。有时候，Oracle DBA 需要自己确认 SQL 的来源，本文将演示如何使用 Oracle 提供丰富的跟踪功能，来确认递归 SQL 的调用者来源。 通过**会话页面，DBA 发现一个异常的 Top SQL，SQL ID 为c7452agj0s0t6，消耗了9%的数据库时间。 通过 SQL 详情页面，可以确定 SQL c7452agj0s0t6 的用户名和模块，但是没有确定 SQL c7452agj0s0t6 是哪个客户机器造成的。 从 SQL 的文本中，这是一个针对系统视图 V$ACTIVE_SESSION_HISTORY 的查询。 SELECT GROUP_TYPE, BUCKET_START, BUCKET_END, TM_GROUP_TYPE, TM_BUCKET_START, TM_BUCKET_END, SUM(TM_CPU_FIRST_BUCKET_VALUE) TM_CPU_FIRST_BUCKET_VALUE, SUM(TM_CPU_MIDDLE_BUCKETS_VALUE) TM_CPU_MIDDLE_BUCKETS_VALUE, ... 省略大量 SQL 文本 FROM TABLE(SYS.GV$(CURSOR( ... 省略大量 SQL 文本 SELECT ASH0.* FROM V$ACTIVE_SESSION_HISTORY ASH0 ... 省略大量 SQL 文本 通过 SQL 的活动会话信息，可以确定执行该 SQL 的进程为后台 PZ 进程，这些会话的 QC（query coordinator）不为空，也就是这是针对视图 V$ACTIVE_SESSION_HISTORY 的并行查询造成的。因为执行时间很短，并且执行此 SQL 的会话为短连接，无法查询到 QC 会话的来源。 为了确定 SQL c7452agj0s0t6 的来源，我们使用 Oracle 11g 中的新功能，对单个 SQL ID 打开10046事件跟踪，命令如下： alter system set events 'sql_trace [sql:c7452agj0s0t6] wait=true,bind=true,plan_stat=all_executions,level=12'; 通过以下查询确定 trace 文件的产生目录： select value from v$diag_info where name='Default Trace File'; 对最新生成的 PRDDB_ora**.trc 执行 grep c7452agj0s0t6，找到跟踪文件如下，可以确定该的客户端为 testapp。通过 dep=2，知道 SQL c7452agj0s0t6 为深度二级的递归 SQL，并不是用户直接提交的 SQL。我们依然不知道这个递归 SQL 具体由什么 SQL 调用的。 *** 2016-10-14 09:34:13.921 *** SESSION ID:(329.41269) 2016-10-14 09:34:13.921 *** CLIENT ID:() 2016-10-14 09:34:13.921 *** SERVICE NAME:(PRDDB) 2016-10-14 09:34:13.921 *** MODULE NAME:(python@testapp (TNS V1-V3)) 2016-10-14 09:34:13.921 *** ACTION NAME:() 2016-10-14 09:34:13.921 ... 省略大量文本 ===================== PARSING IN CURSOR #139987897061528 len=11079 dep=2 uid=87 oct=3 lid=87 tim=1476408854404253 hv=3792438054 ad='21b01ba960' sqlid='c7452agj0s0t6' 关闭 SQL ID 跟踪的命令如下： alter system set events 'sql_trace [sql:c7452agj0s0t6] off'; 为了确认什么 SQL 调用了 c7452agj0s0t6，我们需要在会话级别打开10046跟踪，因为执行 SQL c7452agj0s0t6 的会话是短连接，我们创建一个针对用户 APPUSER 的 logon 触发器，下次这个用户登录时会启动10046跟踪。 create or replace trigger sys.set_trace after logon on database when (user in ('APPUSER')） declare begin execute immediate 'alter session set statistics_level=all'; execute immediate 'alter session set max_dump_file_size=unlimited'; execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; end set_trace; / 接着，在跟踪文件目录中找到最新生成的跟踪文件 PRDDB1_ora_10452.trc 包含 SQL ID c7452agj0s0t6。接着我们使用 orasrp 这个工具分析10046跟踪文件. orasrp 为 Oracle Session Resource Profiler，出自一位俄罗斯 DBA 的强大的10046分析工具，网址为：http://oracledba.ru/orasrp/ orasrp PRDDB1_ora_10452.trc PRDDB1_ora_10452.html orasrp相对于 Oracle 自带的 tkprof，功能更加强大，其中一大优势是会生成会话的递归调用树。递归调用树(Session Call Graph)部分如下图，SQL hash value=3792438054 为 SQL c7452agj0s0t6，深度为2，**的 SQL hash value = 2036392974。 ** SQL 文本如下，原来 SQL c7452agj0s0t6 是由 dbms_sqltune.report_sql_monitor 这个生成 SQL Monitor 报告的函数递归调用的。确定是前几天新部署的监控 job，在后台定时抓取和保存新生成的 SQL monitor 报告，但是执行过于频繁。解决的方法为降低后台 job 的执行频率，对每次抓取 SQL monitor 的执行时间提高**。 select dbms_sqltune.report_sql_monitor(type= |
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
注册
登录
