当前位置： 首页 > news >正文

60-Oracle 10046事件-实操

news 2025/6/22 8:52:44

在实操中，10046功能强大需要多次尝试才算顺手。功能丰富的10046再结合tkprof等格式化工具将原始跟踪数据转化为可读报告，能够极大提升分析效率。对于复杂性能问题，尤其是涉及优化器行为异常、递归SQL效率低下或系统级性能下降的场景，10046事件往往是最终解决方案的关键所在。作为Oracle DBA核心技能之一，深入理解和熟练运用10046事件将显著提升数据库性能优化工作的质量和效率。

一、 配置10046与脚本实践

高效运用10046事件需要掌握其完整的生命周期管理技术，包括事件激活、参数优化、文件定位和结果分析。不同应用场景下需要采用差异化的跟踪策略。
1. 基础配置与跟踪管理
配置10046事件前需设置 核心参数以优化跟踪输出：
时间统计精确性 ​：
  • timed_statistics必须设置为TRUE（默认为TRUE），确保等待事件和CPU时间记录精确到微秒级
文件大小控制 ​：
  • max_dump_file_size建议设为UNLIMITED，避免大跟踪被截断
文件标识 ​：
tracefile_identifier设置会话跟踪文件的自定义后缀，简化文件查找
统计级别 ​：
statistics_level=ALL可增强执行计划细节（生产环境谨慎使用）
​ 跟踪会话脚本​： 
-- 会话级跟踪（通用脚本）
ALTER SESSION SET tracefile_identifier = '20250621_trace_10046';
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET statistics_level = all; 
-- 可选，增加细节但负载高
ALTER SESSION SET events '10046 trace name context forever, level 12';-- 执行待分析的SQL或PL/SQL
SELECT e.employee_id,e.FIRST_NAME, d.DEPARTMENT_NAME
FROM HR.EMPLOYEES e JOIN HR.DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND e.job_id='IT_PROG';-- 关闭跟踪（两种方式任选）
ALTER SESSION SET events '10046 trace name context off';
-- 或直接退出会话
--运行记录
SYS@CDB$ROOT> ALTER SESSION SET tracefile_identifier = '20250621_trace_10046';
Session altered.
SYS@CDB$ROOT> ALTER SESSION SET timed_statistics = true;
Session altered.
SYS@CDB$ROOT> ALTER SESSION SET max_dump_file_size = unlimited;
Session altered.
SYS@CDB$ROOT> ALTER SESSION SET statistics_level = all; 
-- 可选，增加细节但负载高
Session altered.
SYS@CDB$ROOT> ALTER SESSION SET events '10046 trace name context forever, level 12';
Session altered.
SYS@CDB$ROOT> SELECT e.employee_id,e.FIRST_NAME, d.DEPARTMENT_NAME2  FROM HR.EMPLOYEES e JOIN HR.DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID3* AND e.job_id='IT_PROG';103 Alexander     IT104 Bruce         IT107 Diana         IT106 Valli         IT105 David         IT
SYS@CDB$ROOT> ALTER SESSION SET events '10046 trace name context off';
Session altered.
SYS@CDB$ROOT>
2. 多会话跟踪技术
实际性能分析中常需跟踪 其他会话或系统级活动，Oracle提供了多种跨会话跟踪技术：
DBMS_SYSTEM程序包 ​：
  • 通过SET_EV过程为指定SID/SERIAL#的会话启用跟踪，这是9i/10g的主要方法。需注意此方法需要SYS权限且语法较复杂。
-- 对其他会话启用跟踪（需SYS权限），提前获得sid，serial#
BEGIN DBMS_SYSTEM.SET_EV(sid => 1371, serial => 17, ev => 10046, level => 12, name => '');
END;
/

oradebug命令行工具​：通过setospid或setorapid关联到操作系统进程ID后激活事件，适合诊断已存在的会话。

-- 使用oradeug跟踪其他进程
SELECT spid FROM v$process WHERE addr = (SELECT paddr FROM v$session 
WHERE sid = &sid);
ORADEBUG setospid <spid>
ORADEBUG event 10046 trace name context forever, level 12

系统级跟踪​：通过ALTER SYSTEM为所有当前和新建会话启用跟踪，此方式影响范围大，生产环境需谨慎。 

-- 系统级跟踪（影响大，慎用）
ALTER SYSTEM SET events '10046 trace name context forever, level 8';
3. 文件定位与格式化分析
定位跟踪文件是分析的前提，不同版本有差异方法：
  • ​11g+简化查询​：通过v$diag_info视图直接获取路径
-- 11g+文件定位
SELECT value AS trace_file FROM v$diag_info WHERE name = 'Default Trace File';
--/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_132316_20250621_trace_10046.trc
SYS@CDB$ROOT> show parameter diagnostic_dest;
NAME            TYPE   VALUE
--------------- ------ -----------
diagnostic_dest string /opt/oracle
SYS@CDB$ROOT> show parameter user_dump_dest ;
NAME           TYPE   VALUE
-------------- ------ ---------------------------------------------
user_dump_dest string /opt/oracle/product/23ai/dbhomeFree/rdbms/log
SYS@CDB$ROOT> show parameter background_dump_dest;
NAME                 TYPE   VALUE
-------------------- ------ ---------------------------------------------
background_dump_dest string /opt/oracle/product/23ai/dbhomeFree/rdbms/log
SYS@CDB$ROOT>

通用版本查询​：通过多表关联获取完整路径 

-- 通用文件定位（9i/10g/11g）
SELECT p.tracefile 
FROM v$process p, v$session s 
WHERE p.addr = s.paddr 
AND s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
--/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_132316_20250621_trace_10046.trc

原始跟踪文件（Raw Trace）可读性差，需使用tkprof工具格式化：

tkprof（Trace Kernel Profile）是Oracle数据库自带的性能诊断工具，主要用于格式化原始SQL跟踪文件​（如10046事件或SQL_TRACE生成的.trc文件），生成可读性强的分析报告，帮助DBA识别性能瓶颈

--23 ai
tkprof /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_132316_20250621_trace_10046.trc output.tkp explain=HR/Oracle_4U sys=no waits=yes aggregate=yes
--19c
tkprof /u01/app/oracle/diag/rdbms/test19/test19/trace/test19_ora_27003.trc output.tkp explain=HR/Oracle_4U sys=no waits=yes aggregate=yes

表：tkprof关键参数解析 

​参数​

​功能描述​

​推荐值​

explain

为SQL生成执行计划

用户名/密码

sys

是否包含SYS用户SQL

no（减少干扰）

waits

汇总等待事件信息

yes

aggregate

合并相同SQL语句

yes

sort

排序选项（如fchela, prsela）

fchela（按fetch时间排序）

table

Explain使用的方案表

指定自定义表（可选）

二、使用场景

1. 跟踪当前会话（Current Session）​​
方法1：ALTER SESSION命令
-- 开启跟踪（Level 12 = 绑定变量+等待事件）
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';-- 执行目标SQL
SELECT * FROM HR.employees WHERE EMPLOYEE_ID='145';-- 关闭跟踪
ALTER SESSION SET EVENTS '10046 trace name context off';-- 查找跟踪文件
SELECT value AS trace_file FROM v$diag_info WHERE name = 'Default Trace File';
TRACE_FILE
_____________________________________________________________
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_145425.trc

方法2：DBMS_SESSION包 

-- 开启跟踪
EXEC DBMS_SESSION.session_trace_enable(waits=>TRUE, binds=>TRUE);-- 执行SQL
SELECT * FROM HR.employees WHERE EMPLOYEE_ID='145';-- 关闭跟踪
EXEC DBMS_SESSION.session_trace_disable();-- 获取跟踪文件（同上）
2. 跟踪其他会话（Other Session）​​
方法1：DBMS_SYSTEM包
-- 查询目标会话信息（SID=182, SERIAL#=9802）
SELECT sid, serial#, username, status 
FROM v$session 
WHERE username = 'SYS';
--SID    SERIAL# USERNAME    STATUS
______ __________ ___________ _________21      21946 SYS         ACTIVE44       6355 SYS         ACTIVE54      48562 SYS         ACTIVE169      21945 SYS         ACTIVE182       9802 SYS         ACTIVE-- 开启跟踪（Level 12）
EXEC DBMS_SYSTEM.set_ev(182, 9802, 10046, 12, '');-- 等待目标会话执行SQL（如：HR用户执行查询）-- 关闭跟踪
EXEC DBMS_SYSTEM.set_ev(182, 9802, 10046, 0, '');-- 获取跟踪文件
SELECT p.tracefile 
FROM v$session s, v$process p 
WHERE s.paddr = p.addr AND s.sid = 182;
方法2：DBMS_MONITOR包 
-- 开启跟踪（SID=182, SERIAL#=9802）
EXEC DBMS_MONITOR.session_trace_enable(session_id => 182, serial_num => 9802,waits      => TRUE,binds      => TRUE
);-- 关闭跟踪
EXEC DBMS_MONITOR.session_trace_disable(182, 9802);
 3. 跟踪指定SQL_ID
-- 查找SQL_ID（需提前执行目标SQL）
SELECT sql_id, sql_text 
FROM v$sql 
WHERE sql_text LIKE '%employees%';-- 开启跟踪（SQL_ID='abc123'）
ALTER SYSTEM SET EVENTS 
'SQL_TRACE [SQL: abc123] WAIT=TRUE, BIND=TRUE, LEVEL=12';-- 执行目标SQL（触发跟踪）
SELECT * FROM HR.employees e WHERE e.employee_id = 100;-- 关闭跟踪
ALTER SYSTEM SET EVENTS 
'SQL_TRACE [SQL: abc123] OFF';-- 获取跟踪文件
SELECT tracefile FROM v$diag_info;
 4. 命令行工具（oradebug）
-- 跟踪当前会话
oradebug setmypid
oradebug unlimit
oradebug event 10046 trace name context forever, level 12-- 执行SQL
SELECT * FROM HR.jobs j WHERE j.job_id = 'IT_PROG';-- 关闭跟踪
oradebug event 10046 trace name context off-- 直接输出跟踪文件路径
oradebug tracefile_name
5. 解析跟踪文件（tkprof）​ 

tkprof（Trace Kernel Profile）是Oracle数据库自带的性能诊断工具，主要用于格式化原始SQL跟踪文件​（如10046事件或SQL_TRACE生成的.trc文件），生成可读性强的分析报告，帮助DBA识别性能瓶颈

--转换二进制trc为可读文本
tkprof /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_145425.trc/tmp/trace_output.txt sys=no aggregate=yes sort=prsela,exeela,fchela
关键参数说明：​​
  • sys=no：过滤SYS用户操作
  • sort=prsela：按解析时间排序
  • waits=yes：包含等待事件信息
  • bind=yes：输出绑定变量值
验证结果示例 
TKPROF输出文件头部：
********************************************************************************
COUNT = SQL执行次数
CPU   = CPU时间（秒）
ELAPSED = 总耗时（秒）
DISK   = 物理读次数
QUERY = 逻辑读次数（一致性读）
CURRENT = 逻辑读次数（当前模式）
ROWS   = 处理行数
SQL ID: 8hx54hqvg6tr 
SELECT * FROM HR.employees e WHERE e.department_id = 10Call     Count  CPU   Elapsed  Disk Query  Current  Rows
------- ------ ----- -------- ----- ----- -------- ------
Parse       1  0.00     0.00     0     0        0      0
Execute     1  0.00     0.00     0     0        0      0
Fetch       5  0.01     0.05    10   200        0     45
------- ------ ----- -------- ----- ----- -------- ------
Total       7  0.01     0.05    10   200        0     45等待事件：
Event waited on           Times  Max Wait  Total Waited
------------------------ ------ --------- -------------
db file sequential read       5      0.02          0.05
​注意事项​
  • ​权限要求​：
  • 跟踪其他会话需ALTER SYSTEM或DBA权限
  • 使用DBMS_SYSTEM/DBMS_MONITOR包需EXECUTE权限
  • ​生产环境建议​： 
    -- 限制跟踪文件大小
ALTER SESSION SET max_dump_file_size = 1G;-- 精简跟踪级别（避免Level 12的高负载）
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
    • 高级诊断​：
    • Level 16：添加执行计划中的行源统计信息
ALTER SESSION SET EVENTS '10046 trace name context forever, level 16';
--精准捕获SQL执行细节。建议结合AWR/ASH报告交叉分析，优先跟踪高负载SQL（v$sqlstats中ELAPSED_TIME排序）。

三、高级诊断场景

除常规SQL优化外，10046还在特殊场景中发挥关键作用：
  • ​并行查询诊断​：跟踪文件记录并行从属进程的操作，通过setorapid跟踪特定从属进程
  • ​RMAN/Data Pump性能​：在后台进程启用跟踪分析备份恢复、导入导出性能瓶颈
  • ​自适应执行计划​：12c+中Level 64跟踪捕获因执行时间突变触发的计划重新生成
  • ​云环境诊断​：Autonomous Database中通过控制台下载DIAGNOSTIC_DEST内容分析

四、使用体验

Oracle 10046事件作为数据库性能分析领域的基石工具，通过其多层次的跟踪能力，为SQL执行过程提供了无与伦比的可见性。从基础执行统计到绑定变量分析，再到等待事件捕获，10046构建了完整的性能诊断体系。随着Oracle版本的演进，10046事件持续增强其诊断精度和应用场景范围——11g的统一诊断目录和新增级别大幅提高了可用性，而12c及以后版本对自适应优化器和多租户环境的支持则确保了其在现代数据库生态中的持续价值。

http://www.lryc.cn/news/573228.html

相关文章：

  • Java面试复习指南：JVM原理、并发编程与Spring框架
  • 微服务架构的适用
  • Zephyr 电源管理机制深度解析：从 Tickless Idle 到平台 Suspend 实践
  • 【设计模式】6.原型模式
  • 道德的阶梯：大语言模型在复杂道德困境中的价值权衡
  • 经典控制理论：线性化笔记
  • 开源无广告GIF 制作软件三模录制，教程 / 游戏 GIF 一键生成支持鼠标轨迹显示
  • Linux运维新人自用笔记（Ubuntu磁盘命名规则、新磁盘分区、主流文件系统类型、mkfs命令格式化文件系统、临时和永久挂载、挂载报错、dd指令）
  • [muduo] TcpConnection | 回调交互
  • Go语言网络编程：使用 net/http 构建 RESTful API
  • React JSX语法
  • 分布式锁的四种实现方式：从原理到实践
  • 【Linux仓库】进程概念与基本操作【进程·贰】
  • 使用 Telegraf 向 TDengine 写入数据
  • HarmonyOS 5的分布式通信矩阵是如何工作的？
  • Flink流水线+Gravitino+Paimon集成
  • 5.2 Qt Creator 使用FFmpeg库
  • ffmpeg（六）：图片与视频互转命令
  • 项目练习：Jaspersoft Studio制作PDF报表时，detail和column footer之间存在很大的空白区
  • VR油库虚拟仿真系统：开启智慧油库新时代
  • 广州华锐互动：以技术创新引领虚拟现实体验新高度
  • 蚂蚁百宝箱快速创建智能体AI小程序
  • 【Node】最佳Node.js后端开发模板推荐
  • win环境使用openssl创建p12证书
  • 18年磨一剑！开利科技启动数字化增量投资新时代
  • 软件工程概述：核心概念、模型与方法全解析
  • （详细介绍）线性代数中的零空间（Null Space）
  • Java、PHP、C++ 三种语言实现爬虫的核心技术对比与示例
  • Mac电脑-触摸板增强工具-BetterTouchTool
  • Sublime text启用vim