面试宝典:介绍下Oracle数据库动态性能视图 V$SQL_DIAG_REPOSITORY

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 659 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

Oracle 19C V$SQL_DIAG_REPOSITORY 动态性能视图全面详解

1. 视图概述与核心作用

V$SQL_DIAG_REPOSITORY 是 Oracle 19C 中用于存储和管理SQL诊断信息的动态性能视图。它是SQL诊断框架（SQL Diagnostic Framework）的核心组件，专门用于收集、存储和检索SQL性能问题的诊断数据，为SQL性能调优和问题诊断提供全面的数据支持。

核心作用：

存储SQL诊断和性能调优的相关信息
提供SQL性能问题的历史诊断记录
支持SQL性能分析和调优决策
管理SQL诊断任务和结果
提供SQL性能基线和比较数据

2. 主要使用场景

SQL性能诊断：分析SQL性能问题的根本原因
历史问题追踪：查看历史SQL性能问题的诊断记录
性能基线比较：比较不同时间点的SQL性能表现
调优效果评估：评估SQL调优措施的效果
趋势分析：分析SQL性能随时间的变化趋势
问题重现：重现特定的SQL性能问题场景

3. 字段详解

以下是 V$SQL_DIAG_REPOSITORY 视图的主要字段及其详细说明：

字段名	数据类型	含义说明	重要程度
DIAG_ID	NUMBER	诊断记录的唯一标识符	高
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符	高
TASK_ID	NUMBER	诊断任务的ID	高
TASK_NAME	VARCHAR2(64)	诊断任务的名称	高
DIAG_TIME	DATE	诊断时间	高
DIAG_TYPE	VARCHAR2(32)	诊断类型（如PERFORMANCE, ERROR）	高
DIAG_STATUS	VARCHAR2(16)	诊断状态（如COMPLETED, FAILED）	高
DIAG_DESCRIPTION	VARCHAR2(4000)	诊断描述	中
FINDINGS	CLOB	诊断发现的问题	高
RECOMMENDATIONS	CLOB	诊断建议	高
EXECUTION_CONTEXT	CLOB	执行上下文信息	中
PERFORMANCE_METRICS	CLOB	性能指标数据	高
BASELINE_COMPARISON	CLOB	与基线的比较结果	中
DIAG_PARAMETERS	CLOB	诊断参数设置	中
ERROR_MESSAGE	VARCHAR2(4000)	错误消息（如果诊断失败）	中
CREATED_BY	VARCHAR2(128)	创建者	中
LAST_UPDATED	DATE	最后更新时间	中
CON_ID	NUMBER	容器ID（多租户环境）	中

4. 相关视图与基表

基表：

V$SQL_DIAG_REPOSITORY 基于内存中的X$表和数据字典表实现，主要是：

X$KQLDIAG：SQL诊断信息的内部表
X$KQLDIAGTASK：SQL诊断任务的内部表
WRH$_SQL_DIAG：AWR中的SQL诊断历史表
WRM$_SQL_DIAG_TASK：AWR中的SQL诊断任务表

这些表是Oracle SQL诊断框架的核心组件，存储在SYSAUX表空间中。

5. 底层原理与内部机制

SQL诊断框架架构：

数据收集：自动收集SQL执行性能数据
诊断分析：分析SQL性能问题并生成诊断报告
结果存储：将诊断结果存储在诊断仓库中
报告生成：生成详细的诊断报告和建议

数据收集机制：

自动收集：Oracle自动收集SQL性能数据
手动触发：DBA可以手动触发SQL诊断任务
实时监控：实时监控SQL执行性能
历史分析：基于AWR数据历史性能分析

诊断分析过程：

问题识别：识别SQL性能问题的特征
根本原因分析：分析问题的根本原因
建议生成：生成针对性的优化建议
影响评估：评估问题对系统性能的影响

存储管理：

仓库存储：诊断信息存储在SYSAUX表空间中
保留策略：根据保留策略管理诊断数据
空间管理：自动管理诊断仓库的空间使用
数据压缩：使用压缩技术优化存储效率

6. 常用查询SQL

查询1：查看最近的SQL诊断记录

SELECT diag_id, sql_id, diag_time, diag_type, 
       diag_status, diag_description
FROM v$sql_diag_repository
ORDER BY diag_time DESC
FETCH FIRST 10 ROWS ONLY;

查询2：查找特定SQL的诊断历史

SELECT diag_id, diag_time, diag_type,
       diag_status, diag_description
FROM v$sql_diag_repository
WHERE sql_id = '&sql_id'
ORDER BY diag_time DESC;

查询3：分析诊断类型分布

SELECT diag_type, diag_status,
       COUNT(*) diag_count,
       MIN(diag_time) first_diag,
       MAX(diag_time) last_diag
FROM v$sql_diag_repository
GROUP BY diag_type, diag_status
ORDER BY diag_count DESC;

查询4：查看诊断任务的详细信息

SELECT task_id, task_name,
       COUNT(DISTINCT sql_id) sql_count,
       COUNT(*) diag_count,
       MIN(diag_time) first_diag,
       MAX(diag_time) last_diag
FROM v$sql_diag_repository
GROUP BY task_id, task_name
ORDER BY diag_count DESC;

查询5：查找失败的诊断任务

SELECT diag_id, sql_id, diag_time,
       diag_type, error_message
FROM v$sql_diag_repository
WHERE diag_status = 'FAILED'
ORDER BY diag_time DESC;

查询6：多租户环境下的诊断分析

SELECT con_id, diag_type, diag_status,
       COUNT(*) diag_count,
       COUNT(DISTINCT sql_id) sql_count
FROM gv$sql_diag_repository
GROUP BY con_id, diag_type, diag_status
ORDER BY con_id, diag_count DESC;

查询7：关联SQL文本查看诊断记录

SELECT d.diag_id, d.sql_id, d.diag_time,
       s.sql_text, d.diag_type, d.diag_status
FROM v$sql_diag_repository d
JOIN v$sql s ON d.sql_id = s.sql_id
WHERE d.diag_time > SYSDATE - 7
ORDER BY d.diag_time DESC;

查询8：分析诊断建议的模式

SELECT diag_type,
       COUNT(*) total_diag,
       SUM(CASE WHEN recommendations LIKE '%INDEX%' THEN 1 ELSE 0 END) index_recommendations,
       SUM(CASE WHEN recommendations LIKE '%STATISTICS%' THEN 1 ELSE 0 END) stats_recommendations,
       SUM(CASE WHEN recommendations LIKE '%HINT%' THEN 1 ELSE 0 END) hint_recommendations
FROM v$sql_diag_repository
WHERE recommendations IS NOT NULL
GROUP BY diag_type
ORDER BY total_diag DESC;