Part1事情缘由
朋友找到我，说是节点2的磁盘快满了，使用率已经99%，定位到最后发现是trc日志太多了，希望我能定位一下原因
##查看磁盘
df -Th
文件系统 类型 容量 已用 可用 已用% 挂载点
/dev/mapper/ol-root xfs 191G 187G 3.7G 99% /
##通过du的方式一直定位到具体路径
cd /u01/app/oracle/diag/rdbms/of5/of52/trace
##从大到小排序
du -ah . | sort -rh | head -20
可以发现他的好多trc文件都是60M
发现1：从大按小排序，会发现都是of2_ora_*.trc的文件，最大是 943M，最小是60M，数量巨多，才会导致空间不足
发现2：这种报错一般是由数据库的报错引起的，alert里边会记录比较粗略的报错，而更具体的报错会记录到trc日志里
下一步，需要尽快分别查看两种日志里的不同信息
Part2告警原文解读
##alert_of2.log的持续报警内容
2026-06-25T16:09:02.700855+08:00
Memory Notification: Library Cache Object loaded into SGA
Heap size 513193K exceeds notification threshold (51200K)
Details in trace file /oracle/app/oracle/diag/rdbms/of/of2/trace/of2_ora_51965.trc
提供了事件的概略和记载具体内容的trc信息
##找到对应trc文件（基本都是ora）
Memory Notification: Library Cache Object loaded into SGA
Heap size 513193K exceeds notification threshold (51200K)
******************************************************
HEAP DUMP heap name="KGLH0^698cdb37" desc=0x1561c2e68
extent sz=0xfe8 alt=32767 het=56 rec=9 flg=0x2 opc=2
parent=0x60147e70 owner=0x1561c2e18 nex=(nil) xsz=0xfe8 heap=(nil)
fl2=0x26, nex=(nil), idx=3, dsxvers=1, dsxflg=0x0
dsx first ext=0x399340730
dsx empty ext bytes=0 subheap rc link=0x3993407a8,0x3993407a8
dsx heap size=535687520, dsx heap depth=1
pdb id=0, src pdb id=0
(dumping heap without holding latch continuously, so extent chunks not
guaranteed to exactly match chunks on free lists)
EXTENT 0 addr=0x7984661d8
Chunk 7984661e8 sz= 3696 perm "perm " alo=704
7984661e8 sz= 680 perm "perm "
798466490 sz= 24 cpmlst "idndef : qcuAll"
Chunk 798467058 sz= 120 freeable "kkocfbCardEst: "
Chunk 7984670d0 sz= 240 free " "
根据alert找到了关键的trc日志，trc日志里是更详细的内容
##关键内容
HEAP DUMP heap name="KGLH0^698cdb37"
KGLH0 是 Kernel General Library Heap 0 的缩写。
符号后面的 698cdb37 是一个 十六进制哈希值，它是 该内存堆所关联的 SQL 父游标句柄（Handle）地址的哈希值。
这个哈希值起到了“身份证”的作用，用于在共享池中唯一标识这个特定的 KGLH0 内存堆及其对应的 SQL 父游标
这部分内容是喂给dp告诉我的
我查了一下
| 字段 | 数值 | 含义 |
|---|---|---|
| Heap size | 512,612K | 对象单次分配的堆内存，约 500MB |
| threshold | 51,200K | 内部参数 _kgl_large_heap_warning_threshold，默认 50MB |
| 比值 | 10倍 | 超出阈值一个数量级 |
| 严重程度 | ORA-04031 前兆 | 不直接宕机，但持续超大会导致共享池压力 |
这个是触发问题的阈值
SELECT a.ksppinm "Parameter",
b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/' and a.ksppinm like '_kgl_large_heap_warning_threshold';
Parameter Default Value Session Value Instance Value IS_SESSION_MODI IS_SYSTEM_MODIFIABLE
----------------------------------- --------------------------- ----------------------------------- ----------------------------------- --------------- ---------------------------
_kgl_large_heap_warning_threshold TRUE 52428800 52428800 FALSE IMMEDIATE
这个是根据报警日志查出来的关键数值（name="KGLH0^698cdb37"）
SELECT
owner,
name,
type,
sharable_mem/1048576 AS sharable_mb,
loads,
executions,
locks,
pins
FROM v$db_object_cache
WHERE hash_value = TO_NUMBER('698cdb37', 'XXXXXXXX');
OWNER NAME TYPE SHARABLE_MB LOADS EXECUTIONS LOCKS PINS
------- --------------------------------------- -------------------- ------------ ------- ---------- ---------- ----------
RTSCTXb65aa0883a0905f56cb270f2d96ebdfe RUNTIME STATISTICS 500 1 0 0 0
RTSCTXec06d2c62ed083da58959b32d71ea8b4 RUNTIME STATISTICS 118.94 1 0 0 0
Part3根因确认：RTSCTX
3.1RTSCTX 是什么
RTSCTX = Real-Time SQL Monitoring Context
Oracle 11g+ 引入的 SQL Monitor 功能，当 SQL 满足以下任一条件时自动启用：
并行执行（Parallel Query / PDML / DDL）
单次执行超过 5 秒（CPU + I/O）
手动加了 /*+ MONITOR */ hint
RTSCTX 是 SQL Monitor 在共享池中创建的运行时统计上下文对象，存储每一步执行计划的统计信息（实际行数、内存使用、I/O、CPU 等）。
3.2为什么会膨胀到 500MB
| 因素 | 对 RTSCTX 大小的影响 |
|---|---|
| 执行计划行数 > 300 | 每行都要单独统计，基础开销大 |
| 并行度 > 32 | 每个 PX slave 独立统计，成倍放大 |
| 大量子查询/UNION ALL/CTE | 子计划嵌套，统计结构呈指数增长 |
| XMLTable / JSON_Table | 动态展开导致计划行数暴增 |
Part4解决方案
4.1确认 RTSCTX 还在共享池
SET LINESIZE 200
COL name FORMAT A70
COL type FORMAT A30
SELECT
owner,
name,
type,
ROUND(sharable_mem/1048576,2) AS sharable_mb,
loads,
executions,
locks,
pins
FROM v$db_object_cache
WHERE name LIKE '%RTSCTX%'
AND sharable_mem > 52428800 -- 只显示 >50MB 的
ORDER BY sharable_mem DESC;
OWNER NAME TYPE SHARABLE_MB LOADS EXECUTIONS LOCKS PINS
------- --------------------------------------- -------------------- ------------ ------- ---------- ---------- ----------
RTSCTXb65aa0883a0905f56cb270f2d96ebdfe RUNTIME STATISTICS 500 1 0 0 0
RTSCTXec06d2c62ed083da58959b32d71ea8b4 RUNTIME STATISTICS 118.94 1 0 0 0
4.2从 v$sql_monitor 找嫌疑 SQL
SET LINESIZE 200
COL sql_text FORMAT A100
COL username FORMAT A15
COL status FORMAT A20
SELECT
sql_id,
sql_exec_id,
status,
username,
TO_CHAR(sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
ROUND(elapsed_time/1000000,2) AS elapsed_sec,
ROUND(cpu_time/1000000,2) AS cpu_sec,
buffer_gets,
disk_reads,
direct_writes,
px_servers_allocated,
SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql_monitor
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;
SQL_ID SQL_EXEC_ID STATUS USERNAME START_TIME ELAPSED_SEC CPU_SEC BUFFER_GETS DISK_READS DIRECT_WRITES PX_SERVERS_ALLOCATED
------------- ----------- -------------------- --------------- ------------------- ----------- ---------- ----------- ---------- ------------- --------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
3s58vfpqakggj 33554432 DONE (ERROR) SOAPRD12C_SOAIN 2026-06-24 14:00:36 4153.36 882.87 182677262 942188 1
FRA
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN
4xm1ruvkx3awx 33554438 DONE SYS 2026-06-24 21:59:59 4076.87 3705.31 544817715 345098 8657
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN
重点关注：
elapsed_time 最大的 SQL
px_servers_allocated > 1 的并行 SQL
status = 'EXECUTING' 的正在执行的 SQL
4.3获取 SQL 完整文本和执行计划
-- 3.1 完整 SQL 文本
SET LONG 100000
SELECT sql_fulltext
FROM v$sql
WHERE sql_id = '4xm1ruvkx3awx'
AND ROWNUM = 1;
##问题SQL
SQL_FULLTEXT
--------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(128) := :job_name; job_subname
VARCHAR2(128) := :job_subname; job_owner VARCHAR2(128) := :job_owner; job_sta
rt TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH T
IME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :win
dow_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHA
R2(14) := :chainid; credential_owner VARCHAR2(128) := :credown; credential_na
me VARCHAR2(128) := :crednam; destination_owner VARCHAR2(128) := :destown; de
stination_name VARCHAR2(128) := :destnam; job_dest_id varchar2(14) := :jdestid;
log_id number := :log_id; BEGIN DECLARE
ename VARCHAR2(30);
exec_task BOOLEAN;
BEGIN
-- check if tuning pack is enabled
exec_task := prvt_advisor.is_pack_enabled(
dbms_management_packs.TUNING_PACK);
-- check if we are in a pdb,
-- since auto sqltune is not run in a pdb
IF (exec_task AND -- tuning pack enabled
sys_context('userenv', 'con_id') <> 0 AND -- not in non-cdb
sys_context('userenv', 'con_id') <> 1 ) THEN -- not in root
exec_task := FALSE;
END IF;
-- execute auto sql tuning task
IF (exec_task) THEN
ename := dbms_sqltune.execute_tuning_task(
'SYS_AUTO_SQL_TUNING_TASK');
END IF;
-- check whether we are in non-CDB or a PDB
-- auto SPM evolve only runs in a non-CDB or a PDB, not the root.
IF (sys_context('userenv', 'con_id') = 0 OR
sys_context('userenv', 'con_id') > 2) THEN
exec_task := TRUE;
ELSE
exec_task := FALSE;
END IF;
-- execute auto SPM evolve task
IF (exec_task) THEN
ename := dbms_spm.execute_evolve_task('SYS_AUTO_SPM_EVOLVE_TASK');
END IF;
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF;
END;
可以看到的是，上述是数据库的系统自动化任务导致的，关键字就是
ename := dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
ename := dbms_spm.execute_evolve_task('SYS_AUTO_SPM_EVOLVE_TASK');
4.4查历史数据（SQL 已执行完毕）
SELECT
sql_id,
COUNT(*) AS ash_samples,
MIN(TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI:SS')) AS first_seen,
MAX(TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI:SS')) AS last_seen,
MAX(session_id) AS sid,
MAX(module) AS module,
MAX(program) AS program
FROM v$active_session_history
WHERE sample_time BETWEEN
TO_DATE('2026-06-25 16:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2026-06-25 16:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND sql_id IS NOT NULL
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;
SQL_ID ASH_SAMPLES FIRST_SEEN LAST_SEEN SID MODULE PROGRAM
------------- ----------- ------------------- ------------------- ---------- ---------------------------------------------------------------- ------------------------------------------------
6tcmhybcqxggy 55 2026-06-25 16:00:54 2026-06-25 16:18:14 14456 JDBC Thin Client JDBC Thin Client
5j5cv6bbjxa5n 5 2026-06-25 16:08:52 2026-06-25 16:16:56 13821 JDBC Thin Client JDBC Thin Client
747rsqhwvkddf 4 2026-06-25 16:13:34 2026-06-25 16:13:34 13661 JDBC Thin Client JDBC Thin Client
b9nc692k7bk3j 4 2026-06-25 16:02:19 2026-06-25 16:08:20 5190 JDBC Thin Client JDBC Thin Client
bdytm6dm7xp86 3 2026-06-25 16:00:19 2026-06-25 16:00:46 9268 sqlplus oracle (PPA7)
2krunxnb6ngwp 3 2026-06-25 16:04:49 2026-06-25 16:16:55 11783 JDBC Thin Client oracle (PPA6)
52c5bbq1rgz8g 3 2026-06-25 16:00:54 2026-06-25 16:12:05 14456 JDBC Thin Client JDBC Thin Client
577a9vh480tru 3 2026-06-25 16:03:39 2026-06-25 16:09:16 10050 JDBC Thin Client JDBC Thin Client
bxch676qry6fg 3 2026-06-25 16:00:07 2026-06-25 16:02:26 3459 JDBC Thin Client JDBC Thin Client
fxgzjqxc8w9ac 3 2026-06-25 16:02:18 2026-06-25 16:02:18 10207 JDBC Thin Client JDBC Thin Client
0az7czjdw8z7j 3 2026-06-25 16:02:29 2026-06-25 16:09:50 9425 rman rman
1jwtf3zsrvrb4 2 2026-06-25 16:00:54 2026-06-25 16:10:03 12406 JDBC Thin Client JDBC Thin Client
6tycnr02c01au 2 2026-06-25 16:00:09 2026-06-25 16:02:18 4715 JDBC Thin Client JDBC Thin Client
by0gt8vx4hk66 2 2026-06-25 16:01:56 2026-06-25 16:01:57 8323 JDBC Thin Client JDBC Thin Client
79kdnjbaa39vn 2 2026-06-25 16:04:49 2026-06-25 16:16:55 11625 JDBC Thin Client JDBC Thin Client
5scn766bwfqvf 2 2026-06-25 16:04:49 2026-06-25 16:16:55 10994 JDBC Thin Client JDBC Thin Client
7qd215rsryu1u 2 2026-06-25 16:09:45 2026-06-25 16:09:47 9425 rman rman (TNS V1-V3)
4q1q8pnq56v62 2 2026-06-25 16:06:20 2026-06-25 16:10:19 3463 JDBC Thin Client JDBC Thin Client
8h9j0ppwp65at 2 2026-06-25 16:04:49 2026-06-25 16:12:53 11624 JDBC Thin Client oracle (PPA6)
8q02yr8m0ac4s 2 2026-06-25 16:02:20 2026-06-25 16:03:03 11783 JDBC Thin Client JDBC Thin Client
4.5SQL Monitor 历史报告
-- dba_hist_reports 没有 sql_id 列，KEY1 存的是 SQL_ID
SELECT
report_id,
key1 AS sql_id,
TO_CHAR(period_start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(period_end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
SUBSTR(report_summary, 1, 200) AS summary
FROM dba_hist_reports
WHERE component_name = 'sqlmonitor'
AND period_start_time > SYSDATE - 7
ORDER BY period_start_time DESC
FETCH FIRST 20 ROWS ONLY;
REPORT_ID SQL_ID START_TIME END_TIME
---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------------------
SUMMARY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
50871 a06uhrv7h1ycz 2026-06-25 11:23:07 2026-06-25 11:23:16
<report_repository_summary><sql sql_id="a06uhrv7h1ycz" sql_exec_start="06/25/2026 11:23:07" sql_exec_id="16777252"><status>DONE (ALL ROWS)</status><sql_text>SELECT PAYLOAD_DATA FROM DSGC
50865 6b1j5n69nm9mf 2026-06-25 10:36:20 2026-06-25 10:36:24
<report_repository_summary><sql sql_id="6b1j5n69nm9mf" sql_exec_start="06/25/2026 10:36:20" sql_exec_id="16792816"><status>DONE (ALL ROWS)</status><sql_text>select SERV_NO,REQ_FROM,INST_STATUS,CREATIO
50863 a95n8p4vpxmps 2026-06-25 07:19:31 2026-06-25 07:19:36
<report_repository_summary><sql sql_id="a95n8p4vpxmps" sql_exec_start="06/25/2026 07:19:31" sql_exec_id="16779533"><status>DONE</status><sql_text>begin prvt_hdm.execute_slave_actions; end;</sql_text><
50864 drktaf71uygnb 2026-06-25 07:19:31 2026-06-25 07:19:36
<report_repository_summary><sql sql_id="drktaf71uygnb" sql_exec_start="06/25/2026 07:19:31" sql_exec_id="16779533"><status>DONE</status><sql_text>BEGIN
sys.prvt_hdm.auto_execute(
50862 c2p32r5mzv8hb 2026-06-25 00:36:16 2026-06-25 00:36:25
<report_repository_summary><sql sql_id="c2p32r5mzv8hb" sql_exec_start="06/25/2026 00:36:16" sql_exec_id="16777234"><status>DONE</status><sql_text>BEGIN prvt_advisor.delete_expired_tasks; END;</sql
50856 gmx1aj1pmdkt9 2026-06-24 14:32:45 2026-06-24 14:32:52
<report_repository_summary><sql sql_id="gmx1aj1pmdkt9" sql_exec_start="06/24/2026 14:32:45" sql_exec_id="16777217"><status>DONE (ALL ROWS)</status><sql_text>SELECT COUNT(*)
FROM DLV
</sql_te
可以看到步骤5和步骤6有重合的地方，重合的地方就就有概率的问题SQL
4.6告警日志历史
SELECT
TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS alert_time,
message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%Memory Notification%'
AND originating_timestamp > SYSDATE - 30
ORDER BY originating_timestamp DESC;
没找到有用的
Part5解决方案
5.1调大参数
-- 查看当前 SQL Monitor 相关参数
-- 需要 SYS 权限
SELECT a.ksppinm AS parameter, b.ksppstvl AS value
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm LIKE '%sqlmon%'
ORDER BY a.ksppinm;
PARAMETER VALUE
-------------------------- --------------------
_sqlmon_binds_xml_format default
_sqlmon_max_plan 1920
_sqlmon_max_planlines 300
_sqlmon_recycle_time 5
_sqlmon_threshold 5
-- 限制每个 SQL 最大计划行数（默认无限制）
ALTER SYSTEM SET "_sqlmon_max_planlines"=300 SCOPE=SPFILE;
-- 需要重启生效
-- 调大告警阈值，减少噪音（治标）
ALTER SYSTEM SET "_kgl_large_heap_warning_threshold"=107374182400 SCOPE=SPFILE;
-- 1G，需要重启
5.2关闭自动任务
或者关闭
##问题解决
-- ① 自动 SQL 调优：分析 AWR 里的慢 SQL，尝试给出优化建议
ename := dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
-- ② 自动 SPM 进化：检查已有的 SQL Plan Baseline，看新执行计划是否更优
ename := dbms_spm.execute_evolve_task('SYS_AUTO_SPM_EVOLVE_TASK');
-- 关闭 SQL Tuning Advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
/
-- 关闭 Auto Space Advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
END;
BEGIN
DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK', 'OFF');
END;
/
Part6优化SQL
查找共享池中 >50MB 的 RTSCTX 对象
SELECT
owner, name, type,
ROUND(sharable_mem/1048576,2) AS sharable_mb,
loads, executions, locks, pins
FROM v$db_object_cache
WHERE name LIKE '%RTSCTX%'
AND sharable_mem > 52428800
ORDER BY sharable_mem DESC;
OWNER NAME
-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------
TYPE SHARABLE_MB LOADS EXECUTIONS LOCKS PINS
------------------------------ ----------- ---------- ---------- ---------- ----------
RTSCTXb65aa0883a0905f56cb270f2d96ebdfe
RUNTIME STATISTICS 500 1 0 0 0
RTSCTXec06d2c62ed083da58959b32d71ea8b4
RUNTIME STATISTICS 118.95 1 0 0 0
查找当前 v$sql_monitor 中耗时最长的 10 条 SQL
SELECT
sql_id,
sql_exec_id,
status,
username,
TO_CHAR(sql_exec_start, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
ROUND(elapsed_time/1000000,2) AS elapsed_sec,
px_servers_allocated,
SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql_monitor
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
SQL_ID SQL_EXEC_ID STATUS USERNAME START_TIME ELAPSED_SEC PX_SERVERS_ALLOCATED
------------- ----------- -------------------- --------------- ------------------- ----------- --------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
4xm1ruvkx3awx 33554433 DONE SYS 2026-04-08 22:00:04 4345.24
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN
4xm1ruvkx3awx 33554435 DONE SYS 2026-04-16 22:00:04 4175.08
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN
3s58vfpqakggj 33554432 DONE (ERROR) SOAPRD12C_SOAIN 2026-06-24 14:00:36 4153.36
FRA
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN
4xm1ruvkx3awx 33554438 DONE SYS 2026-06-24 21:59:59 4076.87
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN
ASH 采样 — 告警时间段前后活跃 SQL
SELECT
sql_id,
COUNT(*) AS samples,
MIN(TO_CHAR(sample_time, 'HH24:MI:SS')) AS first_seen,
MAX(TO_CHAR(sample_time, 'HH24:MI:SS')) AS last_seen,
MAX(module) AS module
FROM v$active_session_history
WHERE sample_time BETWEEN
TO_DATE('&alert_start', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('&alert_end', 'YYYY-MM-DD HH24:MI:SS')
AND sql_id IS NOT NULL
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 15 ROWS ONLY;
SQL_ID SAMPLES FIRST_SE LAST_SEE MODULE
------------- ---------- -------- -------- ----------------------------------------------------------------
6tcmhybcqxggy 64 16:00:54 16:21:16 JDBC Thin Client
5j5cv6bbjxa5n 5 16:08:52 16:16:56 JDBC Thin Client
b9nc692k7bk3j 4 16:02:19 16:08:20 JDBC Thin Client
747rsqhwvkddf 4 16:13:34 16:13:34 JDBC Thin Client
52c5bbq1rgz8g 3 16:00:54 16:12:05 JDBC Thin Client
577a9vh480tru 3 16:03:39 16:09:16 JDBC Thin Client
bxch676qry6fg 3 16:00:07 16:02:26 JDBC Thin Client
2krunxnb6ngwp 3 16:04:49 16:16:55 JDBC Thin Client
4q1q8pnq56v62 3 16:06:20 16:20:20 JDBC Thin Client
30tm9yrtkb8wr 3 16:18:51 16:18:53 sqlplus@ (TNS V1-V3)
bdytm6dm7xp86 3 16:00:19 16:00:46 sqlplus@ (TNS V1-V3)
fxgzjqxc8w9ac 3 16:02:18 16:02:18 JDBC Thin Client
0az7czjdw8z7j 3 16:02:29 16:09:50 rman@ (TNS V1-V3)
1jwtf3zsrvrb4 2 16:00:54 16:10:03 JDBC Thin Client
5scn766bwfqvf 2 16:04:49 16:16:55 JDBC Thin Client
SQL Monitor 历史报告（最近7天）
ELECT
report_id,
key1 AS sql_id,
TO_CHAR(period_start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time
FROM dba_hist_reports
WHERE component_name = 'sqlmonitor'
AND period_start_time > SYSDATE - 7
ORDER BY period_start_time DESC
FETCH FIRST 10 ROWS ONLY;
REPORT_ID SQL_ID START_TIME
---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------
50871 a06uhrv7h1ycz 2026-06-25 11:23:07
50872 a06uhrv7h1ycz 2026-06-25 11:23:01
50873 9tf8gxb4q1d2k 2026-06-25 11:22:35
50870 a06uhrv7h1ycz 2026-06-25 10:46:48
50868 a06uhrv7h1ycz 2026-06-25 10:45:41
50869 a06uhrv7h1ycz 2026-06-25 10:45:06
50867 a06uhrv7h1ycz 2026-06-25 10:45:01
50866 a06uhrv7h1ycz 2026-06-25 10:44:06
50865 6b1j5n69nm9mf 2026-06-25 10:36:20
50863 a95n8p4vpxmps 2026-06-25 07:19:31
查询具体信息
SQL> SET LONG 100000
SQL> SELECT sql_fulltext FROM v$sql WHERE sql_id='a06uhrv7h1ycz';
查看当时的执行计划
SQL> select * from table(dbms_xplan.display_cursor('a06uhrv7h1ycz',0));
SQL> select * from table(dbms_xplan.display_cursor('9tf8gxb4q1d2k',0));
SQL> select * from table(dbms_xplan.display_cursor('6b1j5n69nm9mf',0));
SQL> select * from table(dbms_xplan.display_cursor('a95n8p4vpxmps',0));
具体SQL就不写出来了，脱敏一下
Part7额外的事情
我哈发现这套库居然有错误密码，导致的数据库异常
连接次数极少
但等待事件是 library cache lock，这个是连续密码登陆报错的主要现象之一
等待事件平均等待时间
连接花费的时间和百分比
最后定位的原因是监控系统用户连接引起的~
这套库给我的感觉就是，安装路径不规范、没有优化参数、没有巡检后台日志、没有定位问题sql的边缘库
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
