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

🧠 Oracle 19C V$SQLAREA 视图详解

1. 视图概述与作用

**V$SQLAREA\ast \ast 是Oracle数据库中一个极其重要的动态性能视图，它提供了\ast \ast 共享池中所有SQL语句的汇总统计信息\ast \ast 。与V$SQL 不同，V$SQLAREA 按照 SQL 文本进行聚合，为同一 SQL 语句的不同子游标提供汇总数据，这使得它成为快速识别高负载 SQL 语句的首选工具。

核心作用：

1. SQL性能监控：快速识别消耗资源最多的SQL语句
2. 负载分析：分析数据库工作负载特征和模式
3. 问题诊断：诊断由低效SQL引起的性能问题
4. 容量规划：基于SQL执行模式进行资源规划
5. 共享池管理：监控共享池中SQL语句的分布和使用情况

2. 字段含义详解

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

字段名 (Column Name)	数据类型 (Datatype)	描述 (Description)
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符。基于SQL文本的哈希值，是性能调优的关键字段。
SQL_TEXT	VARCHAR2(1000)	SQL语句的前1000个字符。显示SQL文本内容。
SQL_FULLTEXT	CLOB	SQL语句的完整文本。用于获取长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	逻辑读次数（一致性读）。关键性能指标，高值可能表明索引缺失或SQL低效。
ROWS_PROCESSED	NUMBER	处理的总行数。
SORTS	NUMBER	排序操作次数。高值可能表明需要优化ORDER BY或GROUP BY。
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等。
SHARABLE_MEM	NUMBER	共享内存大小（字节）。
PERSISTENT_MEM	NUMBER	持久内存大小（字节）。
RUNTIME_MEM	NUMBER	运行时内存大小（字节）。
VERSION_COUNT	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)	是否可共享。
CON_ID	NUMBER	容器ID。多租户环境中标识所属容器。

3. 使用场景

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

1. 性能问题诊断：快速识别消耗最多资源的SQL语句
2. 日常性能监控：定期检查高负载SQL语句
3. 应用部署验证：新应用部署后检查SQL性能
4. 索引优化：识别可能受益于新索引的SQL语句
5. 绑定变量检查：通过VERSION_COUNT识别可能的绑定变量问题
6. 共享池分析：了解共享池中SQL的分布特征

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

4.1 库缓存与游标管理

V$SQLAREA 的数据来源于库缓存（Library Cache），这是共享池的核心组件。库缓存使用复杂的哈希算法来管理和检索SQL语句。

父游标与子游标概念：

• 父游标：基于SQL文本标识，对应V$SQLAREA中的一条记录
• 子游标：基于执行环境（绑定变量类型、NLS设置等）标识，对应V$SQL中的多条记录
• V$SQLAREA 对同一父游标的所有子游标统计信息进行聚合

4.2 SQL执行统计收集

Oracle 在SQL执行过程中自动收集各种统计信息：

• 执行次数：每次执行递增
• 资源消耗：累计CPU时间、逻辑读、物理读等
• 等待事件：记录各种等待时间
• 内存使用：跟踪游标的内存占用

4.3 自适应游标共享

从Oracle 11g开始引入的特性：

• 绑定敏感游标：优化器意识到不同的绑定变量值可能需要不同的执行计划
• 绑定感知游标：游标已经根据绑定变量值调整了执行行为
• 相关字段：IS_BIND_SENSITIVE, IS_BIND_AWARE

5. 相关视图

视图名称	主要用途描述
V$SQL	提供详细的子游标级别信息
V$SQLSTATS	SQL语句的性能统计，专注于执行统计
V$SQLTEXT	SQL语句的完整文本内容
V$SQL_BIND_CAPTURE	SQL语句的绑定变量信息
V$SQL_PLAN	SQL语句的执行计划详情
V$SQL_SHARED_CURSOR	解释为什么不能共享游标的原因
DBA_HIST_SQLSTAT	AWR历史SQL统计信息

6. 基表信息

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

• X$KGLCURSOR_PARENT：父游标信息
• X$KGLTABLE：库缓存对象表

这些X$表存储了共享池中SQL语句的实时统计信息，Oracle不建议直接查询这些底层表。

7. 常用查询 SQL

7.1 高负载SQL查询（按CPU时间）

SELECT 
    sql_id,
    substr(sql_text, 1, 50) as sql_text,
    executions,
    round(cpu_time/1000000, 2) as cpu_sec,
    round(elapsed_time/1000000, 2) as elapsed_sec,
    buffer_gets,
    disk_reads,
    rows_processed
FROM 
    v$sqlarea
WHERE 
    cpu_time > 1000000
ORDER BY 
    cpu_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_per_exec,
    round(buffer_gets/decode(rows_processed,0,1,rows_processed)) as gets_per_row,
    disk_reads
FROM 
    v$sqlarea
WHERE 
    buffer_gets > 100000
ORDER BY 
    buffer_gets DESC;

7.3 执行次数最多的SQL

SELECT 
    sql_id,
    substr(sql_text, 1, 50) as sql_text,
    executions,
    round(elapsed_time/decode(executions,0,1,executions)/1000, 2) as avg_ms_per_exec,
    module,
    parsing_schema_name
FROM 
    v$sqlarea
WHERE 
    executions > 1000
ORDER BY 
    executions DESC;

7.4 识别可能绑定变量问题的SQL

SELECT 
    sql_id,
    substr(sql_text, 1, 50) as sql_text,
    version_count,
    executions,
    round(executions/decode(version_count,0,1,version_count)) as execs_per_version
FROM 
    v$sqlarea
WHERE 
    version_count > 10
    AND executions > 1000
ORDER BY 
    version_count DESC;

7.5 按模块分析SQL负载

SELECT 
    module,
    count(*) as sql_count,
    sum(executions) as total_executions,
    sum(cpu_time)/1000000 as total_cpu_sec,
    sum(buffer_gets) as total_buffer_gets,
    sum(disk_reads) as total_disk_reads
FROM 
    v$sqlarea
WHERE 
    module IS NOT NULL
GROUP BY 
    module
ORDER BY 
    total_cpu_sec DESC;

8. 最佳实践与注意事项

1. 定期监控：建立定期的V$SQLAREA查询机制，识别性能趋势
2. 重点关注：特别关注高CPU时间、高逻辑读、高执行次数的SQL
3. 结合执行计划：使用SQL_ID进一步查询V$SQL_PLAN分析执行计划
4. 历史比较：结合DBA_HIST_SQLSTAT进行历史性能分析
5. 绑定变量：对于VERSION_COUNT高的SQL，检查绑定变量使用
6. 内存考虑：关注SHARABLE_MEM大的SQL，它们可能占用大量共享池内存
7. 多租户环境：在CDB环境中使用CON_ID过滤特定容器的SQL
8. 数据时效性：V$SQLAREA只包含当前在共享池中的SQL，重启后数据丢失

通过正确使用 V$SQLAREA 视图，DBA可以快速识别和优化性能问题SQL，显著提高数据库整体性能。这个视图是Oracle数据库性能调优中不可或缺的工具。

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