Oracle SQL 学习笔记33 - 跟踪SQL运行信息

应用跟踪概览

End-to-End 应用跟踪有两种形式：Enterprise Manager和DBMS_MONITOR。
此外还可以通过trcsess utility和SQL Trace 和 TKPROF进行跟踪。

End-to-End 应用跟踪

End-to-End 应用跟踪简化了在多层环境下诊断性能问题的过程。可以用于识别高负载的SQL和在数据库级别监控用户会话在做什么。更进一步，通过追踪一个服务中的指定模块和行为，简化应用workload的管理。

EM

em.JPG

DBMS_MONITOR

EXECUTE  DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(
    client_id  =>  'OE.OE',
    waits  =>  TRUE,  binds  =>  FALSE);

EXECUTE  DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
    service_name  =>  'ACCTG',
    module_name  =>  'PAYROLL');

EXECUTE  DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
    service_name  =>  'ACCTG',
    module_name  =>  'GLEDGER',
    action_name  =>  'INSERT  ITEM');

查看收集到的End-to-End 应用跟踪的统计信息

某一个服务的统计信息可以在V$SERVICE_STATS视图中找到。而服务、模块和行为的综合统计信息可以在V$SERV_MOD_ACT_STATS视图中找到。

数据库的调用时间和CPU的使用时间的统计信息可以在V$SVCMETRIC视图中找到。

全部的跟踪信息可以在Oracle Enterprise Manager 报告中或者DBA_ENABLED_TRACES视图中找到。

trace_enabled.JPG

使用Trace工具搜集会话SQL运行信息

trcsess.JPG

trcsess utility

SELECT  sid  ||  '.'  ||  serial#,  username
FROM  v\$session
WHERE  username  in  ('HR',  'SH');
-- SID = 236.57, username = 'HR'

trcsess  session  =  236.57  orcl_ora_11155.trc
output=x.txt

通过SQL Trace 能获得的信息包括：

• 语法解析，执行，数目查询
• CPU和已经执行的时间
• 逻辑读取数和物理读取数
• 库缓存的错漏
• 每个发生语法解析下的用户名
• 每次提交和回滚

使用TKPROF工具对trace文件进行解析

SQL Trace Facility

通常，SQL Trace 功能发生在会话级别，为SQL语句收集会话统计信息，并生成可以被TKPROF格式化的输出。

sql_trace_fac.JPG

如何使用SQL Trace Facility

1. 设置初始化参数
2. 启用tracing
3. Run 应用
4. 禁用tracing
5. 关闭会话
6. 格式化trace文件
7. 解析输出

初始化参数

TIMED_STATISCTICS = {false|true}
MAX_DUMP_FILE_SIZE =  {n|unlimited}
USER_DUMP_DEST  =  directory_path
STATISTICS_LEVEL  =  {BASIC|TYPICAL|ALL}

开启SQL Trace

• 为当前会话开启

ALTER  SESSION  SET  sql_trace  =  true;

• 为任意会话开启

EXECUTE  dbms_session.set_sql_trace(true);

EXECUTE  dbms_system.set_sql_trace_in_session(session_id,  serial_id,  true);

• 为一个实例开始

SQL_TRACE  =  TURE

格式化Trace 文件

tkprof  tracefile  outputfile  [options]

• 实例

tkprof
tkprof  ora_902.trc  run1.txt
tkprof  ora_902.trc  run2.txt  sys=no  sort-execpu  print=3

TKPROF命令的输出

• TKPROF命令会输出SQL语句的文本，其中Trace统计信息将被分到3个SQL处理步骤中：

步骤	说明
PARSE	将SQL语句翻译成执行计划
EXECUTE	执行语句（这一步骤修改INSERT，UPDATE和DELETE语句的数据）
FETCH	获取查询语句返回的行（获取动作只会为SELECT语句执行）

• 对于trace统计信息，主要有7个条目

条目名	说明
Count	procedure执行次数
CPU	CPU处理的秒数
Elapsed	执行耗费的所有秒数
Disk	读取的物理块数
Query	为consistent读取的逻辑缓存数
Current	在当前模式下读取的逻辑缓存数
Rows	fetch或者execute处理的行数

• TKPROF的输出还包含以下信息：
• 递归SQL语句
• 库缓存的错漏
• Parsing 用户ID
• 执行计划
• 优化模式或者hint
• Row source 操作

实例，没有index的TRKPFRO：

exm.JPG