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

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

1. 视图概述与核心作用

V$SQL_CURSOR 是 Oracle 19C 中用于监控和管理SQL游标状态信息的动态性能视图。它提供了关于当前数据库中所有活动SQL游标的详细信息，包括游标的状态、资源使用情况、生命周期等关键信息。

核心作用：

• 提供SQL游标的实时状态和统计信息
• 监控游标的内存使用情况和资源消耗
• 支持游标生命周期管理和性能分析
• 诊断游标相关的性能问题和内存泄漏
• 提供游标缓存和共享池管理的关键信息

2. 主要使用场景

1. 游标状态监控：实时监控数据库中所有活动游标的状态
2. 内存管理：分析游标的内存使用情况和资源消耗
3. 性能诊断：诊断与游标相关的性能问题和瓶颈
4. 资源优化：优化游标缓存和共享池的内存配置
5. 泄漏检测：检测和诊断游标内存泄漏问题
6. 系统调优：基于游标统计信息进行系统参数调优

3. 字段详解

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

字段名	数据类型	含义说明	重要程度
SADDR	RAW(8)	游标状态对象的地址	高
SID	NUMBER	会话标识符	高
SERIAL#	NUMBER	会话序列号	高
USER_NAME	VARCHAR2(30)	用户名	中
ADDRESS	RAW(8)	父游标的内存地址	高
HASH_VALUE	NUMBER	SQL语句的哈希值	高
SQL_ID	VARCHAR2(13)	SQL语句的唯一标识符	高
CHILD_NUMBER	NUMBER	子游标编号	高
CURSOR_TYPE	NUMBER	游标类型代码	高
STATUS	VARCHAR2(16)	游标状态	高
SQL_TEXT	VARCHAR2(1000)	SQL文本的前1000个字符	中
LAST_SQL_ACTIVE_TIME	DATE	最后SQL活动时间	中
LAST_CALL_ACTIVE_TIME	DATE	最后调用活动时间	中
PARSING_USER_ID	NUMBER	解析用户ID	中
PARSING_SCHEMA_ID	NUMBER	解析模式ID	中
PARSING_SCHEMA_NAME	VARCHAR2(30)	解析模式名称	中
KEPT_VERSIONS	NUMBER	保持的版本数	中
LOADED_VERSIONS	NUMBER	加载的版本数	中
OPEN_VERSIONS	NUMBER	打开的版本数	中
USERS_OPENING	NUMBER	打开的用户数	中
EXECUTIONS	NUMBER	执行次数	高
LOADS	NUMBER	加载次数	中
INVALIDATIONS	NUMBER	失效次数	中
PARSE_CALLS	NUMBER	解析调用次数	高
DISK_READS	NUMBER	磁盘读取次数	高
BUFFER_GETS	NUMBER	缓冲区获取次数	高
ROWS_PROCESSED	NUMBER	处理的行数	高
CPU_TIME	NUMBER	CPU时间（微秒）	高
ELAPSED_TIME	NUMBER	总耗时（微秒）	高
OPTIMIZER_COST	NUMBER	优化器成本估算	中
OPTIMIZER_MODE	VARCHAR2(10)	优化器模式	中
OPTIMIZER_ENV	RAW(2000)	优化器环境	低
SHARABLE_MEM	NUMBER	可共享内存大小	高
PERSISTENT_MEM	NUMBER	持久内存大小	高
RUNTIME_MEM	NUMBER	运行时内存大小	高
CON_ID	NUMBER	容器ID（多租户环境）	中

4. 相关视图与基表

相关视图：

1. V$SQL：SQL执行统计信息
2. V$SQLAREA：SQL区域的共享游标统计信息
3. V$SQLSTATS：SQL统计信息
4. V$SESSION：会话信息
5. V$OPEN_CURSOR：打开的游标信息
6. GV$SQL_CURSOR：集群环境下所有实例的游标信息

基表：

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

• X$KGLCURSOR：游标信息的内部表
• X$KGLTABLE：对象句柄表
• X$KSMSC：游标状态信息的内部表
• X$KSMSS：游标统计信息的内部表

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

5. 底层原理与内部机制

游标管理机制：

1. 游标生命周期：游标从创建、使用到销毁的完整生命周期管理
2. 内存管理：游标内存的分配、使用和释放机制
3. 状态跟踪：实时跟踪游标的状态变化和执行统计
4. 资源共享：游标在共享池中的共享和重用机制

内存管理原理：

1. 共享池存储：游标信息存储在SGA的共享池中
2. 内存结构：使用复杂的内存结构管理游标状态和信息
3. LRU算法：使用LRU算法管理游标内存的分配和回收
4. 内存优化：优化内存使用以提高性能和减少碎片

统计收集机制：

1. 实时统计：在游标执行过程中实时收集统计信息
2. 性能监控：监控游标的性能指标和资源使用情况
3. 状态更新：实时更新游标的状态和执行信息
4. 内存跟踪：跟踪游标的内存使用和分配情况

6. 常用查询SQL

查询1：查看活动游标的状态信息

SELECT sql_id, cursor_type, status, 
       executions, parse_calls,
       buffer_gets, disk_reads,
       rows_processed
FROM v$sql_cursor
WHERE status = 'OPEN'
ORDER BY buffer_gets DESC;

查询2：分析游标的内存使用情况

SELECT sql_id, 
       shARable_mem/1024 sharable_kb,
       persistent_mem/1024 persistent_kb,
       runtime_mem/1024 runtime_kb,
       (sharable_mem + persistent_mem + runtime_mem)/1024 total_kb
FROM v$sql_cursor
ORDER BY total_kb DESC
FETCH FIRST 10 ROWS ONLY;

查询3：监控游标的执行效率

SELECT sql_id, executions,
       ROUND(buffer_gets/NULLIF(executions,0)) avg_buffer_gets,
       ROUND(disk_reads/NULLIF(executions,0)) avg_disk_reads,
       ROUND(rows_processed/NULLIF(executions,0)) avg_rows,
       ROUND(elapsed_time/NULLIF(executions,0)/1000) avg_elapsed_ms
FROM v$sql_cursor
WHERE executions > 0
ORDER BY avg_buffer_gets DESC;

查询4：查找高资源消耗的游标

SELECT sql_id, sql_text,
       buffer_gets, disk_reads,
       cpu_time/1000000 cpu_seconds,
       elapsed_time/1000000 elapsed_seconds,
       executions
FROM v$sql_cursor
WHERE (buffer_gets > 100000 OR disk_reads > 1000)
  AND ROWNUM <= 10
ORDER BY buffer_gets DESC;

查询5：分析游标的状态分布

SELECT status, COUNT(*) cursor_count,
       SUM(executions) total_executions,
       SUM(buffer_gets) total_buffer_gets
FROM v$sql_cursor
GROUP BY status
ORDER BY cursor_count DESC;

查询6：监控游标的解析行为

SELECT sql_id, parse_calls, executions,
       ROUND(parse_calls/NULLIF(executions,0), 2) parse_ratio,
       invalidations, loads
FROM v$sql_cursor
WHERE executions > 10
  AND parse_calls > executions * 1.1
ORDER BY parse_ratio DESC;

查询7：多租户环境下的游标分析

SELECT con_id, sql_id, status,
       COUNT(*) cursor_count,
       SUM(executions) total_executions,
       SUM(sharable_mem)/1024 total_sharable_kb
FROM gv$sql_cursor
WHERE con_id = &container_id
GROUP BY con_id, sql_id, status
ORDER BY total_sharable_kb DESC;

查询8：诊断游标内存问题

SELECT sql_id, 
       kept_versions, loaded_versions, open_versions,
       users_opening,
       sharable_mem/1024 sharable_kb,
       persistent_mem/1024 persistent_kb
FROM v$sql_cursor
WHERE open_versions > 10 OR users_opening > 5
ORDER BY open_versions DESC;

7. 关键知识点与注意事项

重要知识点：

1. 游标生命周期：理解游标从创建到销毁的完整生命周期
2. 内存管理：游标内存的分配、使用和回收机制
3. 状态管理：游标状态的变化和状态含义
4. 性能监控：游标性能指标的监控和分析方法
5. 多租户支持：在CDB环境中游标管理的特殊性

诊断技巧：

1. 状态分析：分析游标状态对性能的影响
2. 内存分析：分析游标内存使用模式和问题
3. 性能分析：分析游标执行效率和资源消耗
4. 泄漏检测：检测和诊断游标内存泄漏问题
5. 趋势分析：监控游标行为随时间的变化趋势

最佳实践：

1. 定期监控：定期检查游标状态和内存使用情况
2. 性能基线：为关键游标建立性能基线
3. 内存优化：优化游标内存配置和参数设置
4. 泄漏预防：实施游标泄漏检测和预防措施
5. 容量规划：基于游标统计信息进行容量规划

注意事项：

1. 数据实时性：游标信息是实时变化的
2. 内存影响：频繁查询可能对系统性能产生影响
3. 权限要求：需要SELECT ANY DICTIONARY或相应权限
4. 版本差异：字段和行为可能在不同Oracle版本中有差异
5. 资源消耗：监控操作本身会消耗系统资源

相关初始化参数：

1. OPEN_CURSORS：控制每个会话最多能打开的游标数
2. SESSION_CACHED_CURSORS：会话缓存的游标数
3. CURSOR_SHARING：控制游标共享行为
4. CURSOR_SPACE_FOR_TIME：游标空间时间优化

通过深入理解和使用 V$SQL_CURSOR 视图，DBA和开发人员可以有效地监控和管理SQL游标，诊断游标相关的性能问题，优化内存使用，提高数据库系统的稳定性和性能表现。

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