— 查看结果缓存状态
SELECT * FROM v$result_cache_statistics;– 查看结果缓存内存使用情况
SELECT name, value FROM v$result_cache_memory;– 查看结果缓存对象
SELECT * FROM v$result_cache_objects;– 清除结果缓存
ALTER SYSTEM FLUSH RESULT_CACHE;

— 创建测试表
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);– 插入测试数据
INSERT INTO employees VALUES (1, ‘John’, ‘Doe’, 10, 5000);INSERT INTO employees VALUES (2, ‘Jane’, ‘Smith’, 20, 6000);INSERT INTO employees VALUES (3, ‘Mike’, ‘Johnson’, 10, 5500);INSERT INTO employees VALUES (4, ‘Lisa’, ‘Williams’, 30, 7000);COMMIT;– 执行查询，启用结果缓存
SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) AS avg_salary
2 FROM employees
3 GROUP BY department_id;DEPARTMENT_ID AVG_SALARY
————- ———-
10 5250
20 6000
30 7000

SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) AS avg_salary
2 FROM employees
3 GROUP BY department_id;DEPARTMENT_ID AVG_SALARY
————- ———-
10 5250
20 6000
30 7000

— 为频繁执行的查询添加结果缓存提示
SELECT /*+ RESULT_CACHE */ *
FROM employees
WHERE department_id = 10;– 监控结果缓存命中率
SELECT name, value FROM v$result_cache_statistics
WHERE name IN (‘Cache Hits’, ‘Cache Misses’);– 查看结果缓存对象
SELECT object_name, status, creation_time, block_count
FROM v$result_cache_objects
WHERE type = ‘Result’;

SQL> SELECT name, value FROM v$result_cache_statistics
2 WHERE name IN (‘Cache Hits’, ‘Cache Misses’);NAME VALUE
—————- ———-
Cache Hits 5
Cache Misses 2

— 检查结果缓存是否启用
SELECT * FROM v$parameter WHERE name = ‘result_cache_mode’;– 检查结果缓存大小
SELECT * FROM v$parameter WHERE name = ‘result_cache_max_size’;– 查看结果缓存错误
SELECT * FROM v$result_cache_statistics WHERE name LIKE ‘%Error%’;– 清除结果缓存
ALTER SYSTEM FLUSH RESULT_CACHE;