Oracle教程FG080-SQL跟踪分析

Part02-生产环境规划与建议

2.1 启用SQL跟踪的方法

启用SQL跟踪的常用方法：

使用ALTER SESSION命令：在当前会话中启用SQL跟踪
使用DBMS_SESSION包：通过PL/SQL包启用SQL跟踪
使用DBMS_MONITOR包：更灵活地控制SQL跟踪
设置初始化参数：在系统级别启用SQL跟踪
使用事件触发器：在特定条件下自动启用SQL跟踪

风哥提示：在生产环境中，启用SQL跟踪可能会产生大量的跟踪文件，应谨慎使用，避免影响系统性能。

2.2 生成跟踪文件的配置

生成跟踪文件的配置：

USER_DUMP_DEST：设置跟踪文件的存储路径
MAX_DUMP_FILE_SIZE：设置跟踪文件的最大大小
SQL_TRACE：启用或禁用SQL跟踪
EVENT：设置跟踪的事件级别
TRACEFILE_IDENTIFIER：为跟踪文件添加标识符，便于识别

更多学习教程公众号风哥教程itpux_com

2.3 跟踪文件的管理

跟踪文件的管理：

定期清理：定期清理过期的跟踪文件，避免占用磁盘空间
压缩存储：对重要的跟踪文件进行压缩存储
备份归档：对重要的跟踪文件进行备份归档
监控大小：监控跟踪文件的大小，避免磁盘空间不足
访问控制：限制跟踪文件的访问权限，保护敏感信息

from oracle:www.itpux.com

Part03-生产环境项目实施方案

在生产环境中使用SQL跟踪的实施方案：

需求分析：确定SQL跟踪的目标和范围。
准备工作：设置跟踪文件的存储路径和大小限制。
启用跟踪：根据需要选择合适的方法启用SQL跟踪。
执行测试：执行需要跟踪的SQL语句。
停止跟踪：在测试完成后停止SQL跟踪。
分析跟踪文件：使用TKPROF等工具分析跟踪文件。
优化SQL语句：根据分析结果优化SQL语句。
清理跟踪文件：清理不需要的跟踪文件，释放磁盘空间。

Part04-生产案例与实战讲解

4.1 启用SQL跟踪实战

示例：启用SQL跟踪

— 方法1：使用ALTER SESSION命令启用SQL跟踪
ALTER SESSION SET SQL_TRACE = TRUE;– 执行需要跟踪的SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 关闭SQL跟踪
ALTER SESSION SET SQL_TRACE = FALSE;– 方法2：使用DBMS_SESSION包启用SQL跟踪
EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);– 执行需要跟踪的SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 关闭SQL跟踪
EXEC DBMS_SESSION.SET_SQL_TRACE(FALSE);– 方法3：使用DBMS_MONITOR包启用SQL跟踪
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);– 执行需要跟踪的SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;– 关闭SQL跟踪
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE();– 设置跟踪文件标识符
ALTER SESSION SET TRACEFILE_IDENTIFIER = ‘fgedu_test’;– 查看跟踪文件的位置
SELECT value FROM v$parameter WHERE name = ‘user_dump_dest’;

— 查看跟踪文件的位置
VALUE
——————————————————————————–
/oracle/diag/rdbms/fgedudb/fgedudb/trace

更多视频教程www.fgedu.net.cn

4.2 跟踪文件分析实战

示例：查找和分析跟踪文件

— 查找跟踪文件
$ cd /oracle/diag/rdbms/fgedudb/fgedudb/trace
$ ls -la fgedudb_ora_*.trc

— 查看跟踪文件内容
$ cat fgedudb_ora_12345.trc | head -50

— 查看跟踪文件中的SQL语句
$ grep -A 10 “SELECT” fgedudb_ora_12345.trc

— 查看跟踪文件中的执行计划
$ grep -A 20 “Execution Plan” fgedudb_ora_12345.trc

— 查看跟踪文件中的等待事件
$ grep -A 5 “WAIT” fgedudb_ora_12345.trc

— 查找跟踪文件
-rw-r—– 1 oracle oinstall 123456 Mar 31 10:00 fgedudb_ora_12345.trc
-rw-r—– 1 oracle oinstall 789012 Mar 31 10:05 fgedudb_ora_67890.trc

— 查看跟踪文件内容（部分）
Trace file /oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_12345.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
Build label: RDBMS_19.3.0.0.0_LINUX.X64_190417
ORACLE_HOME: /oracle/product/19c/dbhome_1
System name: Linux
Node name: fgedu.net.cn
Release: 4.14.35-2047.501.0.1.el7uek.x86_64
Version: #2 SMP Fri Oct 14 17:03:01 PDT 2022
Machine: x86_64
Instance name: fgedudb
Redo thread mounted by this instance: 1
Oracle process number: 123
Unix process pid: 12345, image: oracle@fgedu.net.cn

*** 2026-03-31 10:00:00.000
*** SESSION ID:(123.45678) 2026-03-31 10:00:00.000
*** CLIENT ID:() 2026-03-31 10:00:00.000
*** SERVICE NAME:(SYS$USERS) 2026-03-31 10:00:00.000
*** MODULE NAME:(SQL*Plus) 2026-03-31 10:00:00.000
*** ACTION NAME:() 2026-03-31 10:00:00.000

— 查看跟踪文件中的SQL语句
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000

— 查看跟踪文件中的执行计划
Execution Plan
———————————————————-
Plan hash value: 3711770936

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 3333 | 232K| 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_EMPLOYEES | 3333 | 232K| 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_EMP_DEPT_SALARY | 3333 | | 2 (0)| 00:00:01 |
—————————————————————————————–

学习交流加群风哥微信: itpux-com

4.3 TKPROF工具使用实战

示例：使用TKPROF工具分析跟踪文件

— 使用TKPROF工具分析跟踪文件
$ tkprof fgedudb_ora_12345.trc fgedudb_ora_12345.out explain=sys/sys@fgedudb sort=prscnt

— 查看TKPROF生成的报告
$ cat fgedudb_ora_12345.out

— 查看SQL语句的执行统计信息
$ grep -A 10 “SELECT” fgedudb_ora_12345.out

— 查看SQL语句的执行计划
$ grep -A 20 “Execution Plan” fgedudb_ora_12345.out

— 查看SQL语句的等待事件
$ grep -A 5 “Wait Events” fgedudb_ora_12345.out

— TKPROF生成的报告（部分）
TKPROF: Release 19.0.0.0.0 – Development on Wed Mar 31 10:10:00 2026

Trace file: fgedudb_ora_12345.trc
Sort options: prscnt

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 12345abcde
Plan Hash: 3711770936

SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
execute 1 0.00 0.00 0 0 0 0
fetch 23 0.01 0.02 0 42 0 334
——- —— ——– ———- ———- ———- ———- ———-
total 24 0.01 0.02 0 42 0 334

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
——- —————————————————
334 TABLE ACCESS BY INDEX ROWID FGEDU_EMPLOYEES (cr=42 pr=0 pw=0 time=12345 us cost=4 size=237990 card=3333)
334 INDEX RANGE SCAN IDX_FGEDU_EMP_DEPT_SALARY (cr=2 pr=0 pw=0 time=6789 us cost=2 size=0 card=3333)(object id 123456)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 24 0.00 0.00
SQL*Net message from client 24 0.01 0.05
********************************************************************************

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

风哥提示：在使用SQL跟踪时，应注意以下几点：

谨慎使用：在生产环境中，启用SQL跟踪可能会产生大量的跟踪文件，应谨慎使用，避免影响系统性能。
设置合理的参数：设置合适的跟踪文件大小限制和存储路径，避免磁盘空间不足。
使用TKPROF工具：使用TKPROF工具分析跟踪文件，生成可读的报告，便于分析。
关注关键指标：关注SQL语句的执行时间、资源消耗和等待事件，找出性能瓶颈。
定期清理：定期清理过期的跟踪文件，释放磁盘空间。
保护敏感信息：跟踪文件中可能包含敏感信息，应限制访问权限。
结合其他工具：结合AWR报告、ASH报告等工具，全面分析SQL语句的性能。
测试充分：在生产环境中实施优化方案前，应进行充分的测试，确保优化效果。
持续监控：持续监控SQL语句的执行情况，及时发现和解决性能问题。
文档化：对SQL跟踪的结果和优化方案进行文档化，便于后续参考。

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

风哥提示：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html