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

🧠 Oracle 19C V$SQL 视图详解

1. 视图概述与作用

V$SQL 是 Oracle 数据库中最重要的动态性能视图之一，它提供了共享池中所有 SQL 语句的详细执行统计信息。这个视图是数据库性能调优和SQL优化的核心工具，包含了已经解析并存储在共享池中的每一条SQL语句的执行详情。

核心作用：

1. SQL性能分析：识别高负载、低效的SQL语句
2. 资源消耗监控：监控SQL语句的CPU、内存、I/O消耗
3. 执行计划分析：获取SQL语句的执行计划信息
4. 共享池管理：了解共享池中SQL语句的分布和使用情况
5. 问题诊断：诊断数据库性能问题和异常行为

2. 字段含义详解

下表详细说明了 V$SQL 视图中的关键字段：

字段名 (Column Name)	数据类型 (Datatype)	描述 (Description)
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符。基于SQL文本的哈希值，是性能调优的关键字段。
SQL_TEXT	VARCHAR2(1000)	SQL语句的前1000个字符。显示SQL文本内容。
SQL_FULLTEXT	CLOB	SQL语句的完整文本。用于获取长SQL语句的完整内容。
SQL_TYPE	NUMBER	SQL语句的类型标识。
PARSING_SCHEMA_NAME	VARCHAR2(64)	解析该SQL语句的 schema 名称。
PARSING_USER_ID	NUMBER	解析该SQL语句的用户ID。
PARSING_SCHEMA_ID	NUMBER	解析该SQL语句的 schema ID。
MODULE	VARCHAR2(64)	执行SQL的应用程序模块名（如果设置了DBMS_APPLICATION_INFO）。
ACTION	VARCHAR2(64)	执行SQL的应用程序动作名。
EXECUTIONS	NUMBER	SQL语句的执行次数。重要性能指标。
DISK_READS	NUMBER	SQL语句执行期间的总物理读次数。
DIRECT_WRITES	NUMBER	直接写入次数。
BUFFER_GETS	NUMBER	逻辑读次数（一致性读）。关键性能指标。
ROWS_PROCESSED	NUMBER	处理的总行数。
SORTS	NUMBER	排序操作次数。
CPU_TIME	NUMBER	总CPU时间（微秒）。
ELAPSED_TIME	NUMBER	总执行时间（微秒）。关键性能指标。
APPLICATION_WAIT_TIME	NUMBER	应用程序等待时间。
CONCURRENCY_WAIT_TIME	NUMBER	并发等待时间。
CLUSTER_WAIT_TIME	NUMBER	集群等待时间（RAC环境）。
USER_IO_WAIT_TIME	NUMBER	用户I/O等待时间。
PLAN_HASH_VALUE	NUMBER	执行计划的哈希值。标识特定的执行计划。
OPTIMIZER_COST	NUMBER	优化器估算的执行成本。
OPTIMIZER_MODE	VARCHAR2(10)	优化器模式。如ALL_ROWS, FIRST_ROWS等。
OPTIMIZER_ENV	RAW(32)	优化器环境哈希值。
SHARABLE_MEM	NUMBER	共享内存大小（字节）。
PERSISTENT_MEM	NUMBER	持久内存大小（字节）。
RUNTIME_MEM	NUMBER	运行时内存大小（字节）。
LOADED_VERSIONS	NUMBER	已加载的游标版本数。
OPEN_VERSIONS	NUMBER	打开的游标版本数。
USERS_OPENING	NUMBER	打开此游标的用户数。
FETCHES	NUMBER	总fetch次数。
END_OF_FETCH_COUNT	NUMBER	完成fetch的次数。
FIRST_LOAD_TIME	VARCHAR2(19)	游标首次加载到共享池的时间。
LAST_LOAD_TIME	VARCHAR2(19)	游标最后加载到共享池的时间。
LAST_ACTIVE_TIME	DATE	游标最后活跃的时间。
INVALIDATIONS	NUMBER	游标失效次数。
PARSE_CALLS	NUMBER	解析调用次数。
OBJECT_STATUS	VARCHAR2(19)	游标状态。如VALID, INVALID等。
IS_OBSOLETE	VARCHAR2(1)	是否已废弃。
IS_BIND_SENSITIVE	VARCHAR2(1)	是否对绑定变量敏感。
IS_BIND_AWARE	VARCHAR2(1)	是否已感知绑定变量。
IS_SHAREABLE	VARCHAR2(1)	是否可共享。
CHILD_NUMBER	NUMBER	子游标编号。同一SQL可能有多个子游标。
CHILD_ADDRESS	RAW(8)	子游标地址。
CON_ID	NUMBER	容器ID。多租户环境中标识所属容器。

3. 使用场景

V$SQL 在以下场景中非常重要：

1. 性能问题诊断：识别消耗资源最多的SQL语句
2. SQL调优：找出需要优化的低效SQL
3. 负载分析：分析数据库工作负载特征
4. 容量规划：基于SQL执行模式进行资源规划
5. 应用监控：监控特定应用程序的SQL行为
6. AWR/ASH分析补充：提供实时SQL性能数据

4. 底层原理与相关知识点

4.1 共享池与库缓存

V$SQL 视图的数据来源于库缓存（Library Cache），这是共享池的重要组成部分。库缓存使用哈希表结构来存储和管理SQL语句、执行计划等对象。

4.2 SQL执行过程

1. 解析：检查语法、语义，生成执行计划
2. 绑定：为变量赋值
3. 执行：执行SQL语句
4. 获取：获取结果（针对查询）

V$SQL 记录了这些阶段的详细统计信息。

4.3 游标概念

• 父游标：基于SQL文本标识
• 子游标：基于执行环境（绑定变量值、优化器设置等）标识
• 同一SQL文本可能有多个子游标，对应不同的执行计划

5. 相关视图

视图名称	主要用途描述
V$SQLAREA	SQL语句的汇总统计信息
V$SQLSTATS	SQL语句的性能统计
V$SQLTEXT	SQL语句的完整文本
V$SQL_BIND_CAPTURE	SQL语句的绑定变量信息
V$SQL_PLAN	SQL语句的执行计划
V$SQL_SHARED_CURSOR	解释为什么不能共享游标的原因
V$SQL_OPTIMIZER_ENV	SQL语句的优化器环境

6. 基表信息

V$SQL基于底层的\ast \ast X$ 表**构建，主要是：

• X$KGLCURSOR：库缓存游标信息
• X$KGLTABLE：库缓存对象信息

这些X$表存储了共享池中SQL语句的实时统计信息。

7. 常用查询 SQL

7.1 高负载SQL查询

SELECT 
    sql_id,
    sql_text,
    executions,
    round(elapsed_time/1000000, 2) as elapsed_sec,
    round(cpu_time/1000000, 2) as cpu_sec,
    round(elapsed_time/decode(executions,0,1,executions)/1000000, 4) as avg_elapsed_sec,
    disk_reads,
    buffer_gets,
    rows_processed,
    parse_calls
FROM 
    v$sql
WHERE 
    executions > 0
ORDER BY 
    elapsed_time DESC;

7.2 高逻辑读SQL查询

SELECT 
    sql_id,
    substr(sql_text, 1, 50) as sql_text,
    buffer_gets,
    executions,
    round(buffer_gets/decode(executions,0,1,executions)) as avg_gets,
    disk_reads,
    round(disk_reads/decode(executions,0,1,executions)) as avg_reads
FROM 
    v$sql
WHERE 
    buffer_gets > 100000
ORDER BY 
    buffer_gets DESC;

7.3 SQL执行效率分析

SELECT 
    sql_id,
    executions,
    rows_processed,
    round(rows_processed/decode(executions,0,1,executions)) as avg_rows,
    round(buffer_gets/decode(rows_processed,0,1,rows_processed)) as gets_per_row,
    round(elapsed_time/decode(rows_processed,0,1,rows_processed)/1000) as microseconds_per_row
FROM 
    v$sql
WHERE 
    executions > 0 
    AND rows_processed > 1000
ORDER BY 
    gets_per_row DESC;

7.4 查找特定模块的SQL

SELECT 
    sql_id,
    module,
    action,
    executions,
    round(elapsed_time/1000000, 2) as elapsed_sec,
    buffer_gets
FROM 
    v$sql
WHERE 
    module = '你的应用模块名'
ORDER BY 
    elapsed_time DESC;

7.5 分析SQL解析情况

SELECT 
    sql_id,
    parse_calls,
    executions,
    round(parse_calls/decode(executions,0,1,executions), 2) as parse_ratio,
    invalidations
FROM 
    v$sql
WHERE 
    parse_calls > executions
ORDER BY 
    parse_ratio DESC;

8. 最佳实践与注意事项

1. 定期监控：建立定期的SQL性能监控机制
2. 重点关注：关注高执行次数、高资源消耗的SQL
3. 绑定变量：使用绑定变量减少硬解析和共享池碎片
4. 历史比较：结合AWR报告进行历史性能比较
5. 执行计划分析：结合V$SQL_PLAN分析执行计划
6. 多租户环境：在CDB环境中注意CON_ID字段
7. 数据时效性：V$SQL只包含当前在共享池中的SQL语句

通过正确使用 V$SQL 视图，DBA可以有效地识别和优化性能问题，提高数据库整体性能。

欢迎关注我的公众号《IT小Chen》