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

最新推荐文章于 2025-09-03 12:06:42 发布

原创最新推荐文章于 2025-09-03 12:06:42 发布 · 813 阅读

30 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述

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

1. 视图概述与核心作用

V$SQLSTATS 是 Oracle 19C 中一个重要的动态性能视图，它提供了 SQL 语句执行统计信息的快速访问接口。这个视图专门为性能监控和优化设计，相比传统的 V$SQL 视图，它提供了更高效的访问方式和更稳定的数据持久性。

核心作用：

提供 SQL 语句执行的聚合统计信息
支持高性能的 SQL 监控和诊断
减少对共享池的访问竞争
提供比 V$SQL 更稳定的统计信息视图
支持基于各种指标的 SQL 性能分析

2. 主要使用场景

SQL 性能监控：实时监控系统中执行的 SQL 语句性能
资源消耗分析：识别消耗大量资源的 SQL 语句
性能基线建立：建立 SQL 性能基线用于比较分析
容量规划：评估 SQL 工作负载对系统资源的需求
故障诊断：诊断性能问题和异常行为
AWR 报告补充：为自动工作负载仓库报告提供基础数据

3. 字段详解

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

字段名	数据类型	含义说明	重要程度
SQL_ID	VARCHAR2(13)	SQL 语句的唯一标识符	高
SQL_TEXT	VARCHAR2(1000)	SQL 语句的前1000个字符	高
SQL_FULLTEXT	CLOB	SQL 语句的完整文本	高
SQL_TYPE	NUMBER	SQL 语句类型	中
SQL_PROFILE	VARCHAR2(128)	使用的 SQL Profile 名称	中
EXECUTIONS	NUMBER	执行次数	高
FETCHES	NUMBER	获取次数	中
END_OF_FETCH_COUNT	NUMBER	完成获取的次数	中
SORTS	NUMBER	排序次数	中
LOADED_VERSIONS	NUMBER	加载的版本数	低
OPENED_VERSIONS	NUMBER	打开的版本数	低
USERS_OPENING	NUMBER	打开的用户数	低
USERS_EXECUTING	NUMBER	正在执行的用户数	中
PARSE_CALLS	NUMBER	解析调用次数	高
DISK_READS	NUMBER	磁盘读取次数	高
DIRECT_WRITES	NUMBER	直接写入次数	中
BUFFER_GETS	NUMBER	缓冲区获取次数	高
APPLICATION_WAIT_TIME	NUMBER	应用等待时间	高
CONCURRENCY_WAIT_TIME	NUMBER	并发等待时间	高
CLUSTER_WAIT_TIME	NUMBER	集群等待时间	高
USER_IO_WAIT_TIME	NUMBER	用户 I/O 等待时间	高
PLSQL_EXEC_TIME	NUMBER	PL/SQL 执行时间	中
JAVA_EXEC_TIME	NUMBER	Java 执行时间	中
ROWS_PROCESSED	NUMBER	处理的行数	高
CPU_TIME	NUMBER	CPU 时间（微秒）	高
ELAPSED_TIME	NUMBER	总耗时（微秒）	高
INVALIDATIONS	NUMBER	失效次数	中
LAST_LOAD_TIME	VARCHAR2(19)	最后加载时间	中
LAST_ACTIVE_TIME	DATE	最后活动时间	高
MODULE	VARCHAR2(64)	执行模块名称	中
ACTION	VARCHAR2(64)	执行动作名称	中
CON_ID	NUMBER	容器 ID（多租户环境）	中

4. 相关视图与基表

基表：

V$SQLSTATS 基于内存中的 X$ 表实现，主要是：

X$KGLCURSOR_CHILD_STATS：子游标统计信息的内部表
X$KGLCURSOR：游标信息的内部表
X$KGLTABLE：对象句柄表

这些 X$ 表是 Oracle 内部数据结构的外部化表示，存储在 SGA 的共享池中。

5. 底层原理与内部机制

数据收集机制：

共享池管理：SQL 语句在共享池中解析和执行时，Oracle 会收集相关统计信息
统计信息聚合：V$SQLSTATS 提供了对共享池中 SQL 统计信息的聚合视图
内存结构：统计信息存储在 SGA 的共享池中的内部数据结构中
持久化机制：相比 V$SQL，V$SQLSTATS 的统计信息在游标失效后仍然保持

性能优势：

减少竞争：通过减少对共享池的访问来降低竞争
优化访问：使用专门优化的访问路径获取统计信息
数据持久性：统计信息在游标老化出共享池后仍然可用
高效查询：针对常见的性能监控查询进行了优化

数据更新机制：

当 SQL 语句执行时，相关统计信息会实时更新
统计信息存储在共享池的内部结构中
V$SQLSTATS 提供对这些统计信息的快速访问接口
数据在实例关闭时丢失，但可以通过 AWR 进行持久化

6. 常用查询SQL

查询1：查找消耗最多CPU时间的SQL

SELECT sql_id, sql_text, cpu_time/1000000 cpu_seconds,
       elapsed_time/1000000 elapsed_seconds,
       executions, buffer_gets, disk_reads
FROM v$sqlstats 
WHERE cpu_time > 0
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

查询2：识别高磁盘读的SQL语句

SELECT sql_id, sql_text, disk_reads,
       disk_reads/NULLIF(executions,0) reads_per_exec,
       buffer_gets, elapsed_time/1000000 elapsed_seconds
FROM v$sqlstats 
WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

查询3：分析SQL执行效率

SELECT sql_id, sql_text, executions,
       buffer_gets/NULLIF(executions,0) avg_buffer_gets,
       disk_reads/NULLIF(executions,0) avg_disk_reads,
       rows_processed/NULLIF(executions,0) avg_rows,
       elapsed_time/NULLIF(executions,0)/1000000 avg_elapsed_secs
FROM v$sqlstats 
WHERE executions > 10
ORDER BY avg_buffer_gets DESC;

查询4：监控并发等待严重的SQL

SELECT sql_id, sql_text, concurrency_wait_time/1000000 concurrency_secs,
       application_wait_time/1000000 application_secs,
       user_io_wait_time/1000000 user_io_secs
FROM v$sqlstats 
WHERE concurrency_wait_time > 1000000
ORDER BY concurrency_wait_time DESC;

查询5：查找频繁执行的SQL

SELECT sql_id, sql_text, executions,
       last_active_time, module, action
FROM v$sqlstats 
WHERE executions > 1000
ORDER BY executions DESC;

查询6：分析SQL解析情况

SELECT sql_id, sql_text, parse_calls,
       parse_calls/NULLIF(executions,0) parse_ratio,
       loaded_versions, opened_versions
FROM v$sqlstats 
WHERE parse_calls > executions * 1.1
ORDER BY parse_ratio DESC;

查询7：多租户环境下的SQL监控

SELECT con_id, sql_id, sql_text, 
       SUM(executions) total_executions,
       SUM(cpu_time)/1000000 total_cpu_seconds
FROM gv$sqlstats 
WHERE con_id > 1
GROUP BY con_id, sql_id, sql_text
ORDER BY total_cpu_seconds DESC;