— 查看监控数据保留时间
SELECT * FROM dba_hist_wr_control;– 调整监控数据保留时间
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 7*24*60, — 7天
interval => 60 — 60分钟
);– 手动清理监控数据
EXEC DBMS_SQLTUNE.PURGE_SQL_MONITOR(
sql_id => ‘sql_id_value’
);

— 创建测试表
CREATE TABLE fgsales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount NUMBER,
region VARCHAR2(50)
);– 插入测试数据
INSERT INTO fgsales VALUES (1, SYSDATE – 30, 1000, ‘North’);INSERT INTO fgsales VALUES (2, SYSDATE – 20, 2000, ‘South’);INSERT INTO fgsales VALUES (3, SYSDATE – 10, 1500, ‘East’);INSERT INTO fgsales VALUES (4, SYSDATE, 3000, ‘West’);COMMIT;– 执行需要监控的SQL语句
SELECT /*+ MONITOR */ region, SUM(amount) AS total_sales
FROM fgsales
GROUP BY region;– 查看SQL ID
SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE ‘%region, SUM(amount)%’ AND rownum = 1;– 查看实时SQL监控信息
SELECT * FROM v$sql_monitor
WHERE sql_id = ‘sql_id_value’;

SQL> SELECT /*+ MONITOR */ region, SUM(amount) AS total_sales
2 FROM fgsales
3 GROUP BY region;REGION TOTAL_SALES
——- ———–
North 1000
South 2000
East 1500
West 3000

SQL> SELECT sql_id, sql_text FROM v$sql
2 WHERE sql_text LIKE ‘%region, SUM(amount)%’ AND rownum = 1;SQL_ID SQL_TEXT
————- ————————————————–
pqr678 SELECT /*+ MONITOR */ region, SUM(amount) AS total_sales FROM fgsales GROUP BY region

SQL> SELECT sql_id, status, elapsed_time, cpu_time, buffer_gets
2 FROM v$sql_monitor
3 WHERE sql_id = ‘pqr678’;SQL_ID STATUS ELAPSED_TIME CPU_TIME BUFFER_GETS
————- ——— ———— ——– ————
pqr678 DONE 12345 6789 15

— 查看长时间运行的SQL语句
SELECT sql_id, status, elapsed_time, cpu_time, buffer_gets
FROM v$sql_monitor
WHERE status = ‘EXECUTING’
ORDER BY elapsed_time DESC;– 查看详细的监控报告
SELECT * FROM table(DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id => ‘sql_id_value’,
type => ‘HTML’,
report_level => ‘ALL’
));– 分析执行计划和资源使用
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => ‘sql_id_value’,
format => ‘ALLSTATS LAST’
));

— 检查实时SQL监控是否启用
SELECT * FROM v$parameter WHERE name = ‘control_management_pack_access’;– 查看监控数据是否存在
SELECT COUNT(*) FROM v$sql_monitor;– 手动启用监控
ALTER SESSION SET statistics_level = ‘ALL’;– 检查监控数据收集情况
SELECT * FROM v$sql_monitor_statname;