Oracle PL/SQL 性能监控详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

PL/SQL 性能监控方法[1]：

详细见：Oracle AWR 详解、Oracle ASH 详解。

---

2. AWR

2.1 报告

@?/rdbms/admin/awrrpt.sql

2.2 TOP SQL

- Elapsed Time
- CPU Time
- Buffer Gets
- Disk Reads
- Executions
- Parse Calls

2.3 PL/SQL

- PL/SQL Time
- PL/SQL Executions
- Package 调用

详细见：Oracle AWR 详解。

---

3. ASH

3.1 实时

SELECT sample_time, session_id, sql_id, event, wait_class
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
ORDER BY sample_time;

3.2 报告

@?/rdbms/admin/ashrpt.sql

3.3 PL/SQL

SELECT sql_id, COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_opname IN ('PL/SQL EXECUTE', 'CALL')
GROUP BY sql_id
ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;

详细见：Oracle ASH 详解。

---

4. SQL Monitor

4.1 实时

SELECT sql_id, status, elapsed_time, cpu_time
FROM v$sql_monitor
WHERE status = 'EXECUTING';

-- 报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

-- HTML
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id => '&sql_id',
  type => 'HTML'
) FROM dual;

4.2 长操作

SELECT sid, serial#, opname, sofar, totalwork, ROUND(sofar/totalwork*100, 2) AS pct
FROM v$session_longops
WHERE sofar < totalwork;

---

5. V$SQL

SELECT sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, parse_calls
FROM v$sql
WHERE parsing_schema_name = 'SCOTT'
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- PL/SQL
SELECT sql_text, executions, elapsed_time
FROM v$sql
WHERE command_type = 47  -- PL/SQL EXECUTE
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

---

6. V$DB_OBJECT_CACHE

SELECT type, name, namespace, locks, pins, executions, kept
FROM v$db_object_cache
WHERE type LIKE '%PACKAGE%'
ORDER BY executions DESC FETCH FIRST 20 ROWS ONLY;

6.1 Pin

EXEC DBMS_SHARED_POOL.KEEP('SCOTT.MY_PKG');

---

7. DBMS_PROFILER

7.1 安装

@?/rdbms/admin/proftab.sql
@?/rdbms/admin/profload.sql

7.2 启用

EXEC DBMS_PROFILER.START_PROFILER('test');

-- 执行代码

EXEC DBMS_PROFILER.STOP_PROFILER;

7.3 分析

SELECT u.unit_name, d.line, d.total_occur, d.total_time, d.min_time, d.max_time
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON d.runid = u.runid AND d.unit_number = u.unit_number
WHERE d.runid = 1
ORDER BY d.total_time DESC;

---

8. DBMS_HPROF

8.1 安装

@?/rdbms/admin/dbmshptab.sql
CREATE DIRECTORY hprof_dir AS '/tmp';

8.2 启用

EXEC DBMS_HPROF.START_PROFILING('HPROF_DIR', 'test.txt');

-- 执行

EXEC DBMS_HPROF.STOP_PROFILING;

-- 分析
EXEC DBMS_HPROF.ANALYZE('HPROF_DIR', 'test.txt');

8.3 查看

SELECT * FROM dbmshp_function_info 
ORDER BY function_elapsed_time DESC FETCH FIRST 20 ROWS ONLY;

SELECT * FROM dbmshp_parent_child_info;

---

9. 10046 事件

9.1 启用

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- 执行

ALTER SESSION SET EVENTS '10046 trace name context off';

9.2 分析

tkprof trace.trc output.txt explain=scott/tiger sys=no sort=exeela

详细见：Oracle 10046 事件与 SQL Trace。

---

10. V$SESSION

10.1 活动

SELECT sid, serial#, username, status, event, sql_id, blocking_session
FROM v$session
WHERE username IS NOT NULL AND status = 'ACTIVE';

10.2 等待

SELECT sid, event, wait_class, wait_time, seconds_in_wait
FROM v$session_wait
WHERE sid = ...;

详细见：Oracle 等待事件详解。

---

11. 等待事件

11.1 PL/SQL 相关

- PL/SQL lock timer
- PL/SQL execution elapsed time
- enq: TX - row lock
- library cache lock
- library cache pin

11.2 查询

SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE event LIKE 'PL/SQL%'
ORDER BY time_waited DESC;

---

12. 内存

12.1 PGA

SELECT name, value FROM v$pgastat;
SELECT program,pga_used_mem,pga_alloc_mem FROM v$process;

12.2 UGA

SELECT session_num, name, value FROM v$sesstat ...

详细见：Oracle 内存调优 SGA PGA。

---

13. 解析

13.1 统计

SELECT name, value 
FROM v$sysstat 
WHERE name LIKE '%parse%';

-- parse count (hard)
-- parse count (total)
-- parse time elapsed

13.2 高硬解析

SELECT sql_text, parse_calls, executions
FROM v$sql
WHERE parse_calls > 100
ORDER BY parse_calls DESC FETCH FIRST 10 ROWS ONLY;

详细见：Oracle SQL 解析与执行过程详解。

---

14. Library Cache

SELECT namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratio
FROM v$librarycache;

-- 失败
SELECT namespace, gets, gethits, invalidations
FROM v$librarycache
WHERE gethitratio < 0.9;

---

15. 应用场景

15.1 慢过程

- DBMS_PROFILER 定位行
- AWR TOP SQL
- ASH 实时

15.2 高 CPU

- TOP SQL
- PL/SQL 计算
- SQL 优化

15.3 锁

- ASH 阻塞
- V$SESSION
- V$LOCK

15.4 内存

- PGA
- UGA
- 大集合

---

16. 监控脚本

16.1 TOP PL/SQL

SELECT sql_text, executions, elapsed_time, cpu_time, buffer_gets
FROM v$sql
WHERE command_type = 47
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

16.2 包调用

SELECT owner, object_name, executions, loads, invalidations
FROM v$db_object_cache
WHERE type = 'PACKAGE BODY'
ORDER BY executions DESC FETCH FIRST 20 ROWS ONLY;

16.3 会话等待

SELECT s.sid, s.serial#, s.username, s.event, s.wait_class, 
       s.seconds_in_wait, s.sql_id
FROM v$session s
WHERE s.username IS NOT NULL AND s.status = 'ACTIVE'
ORDER BY s.seconds_in_wait DESC;

---

17. 最佳实践

1. AWR：基线
2. ASH：实时
3. SQL Monitor：实时 SQL
4. Profiler：行级
5. HPROF：层次
6. 10046：详细
7. 等待事件：瓶颈
8. PGA：内存
9. 解析：优化
10. 监控：持续

---

18. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Profiling” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html