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

原创于 2025-09-03 12:07:41 发布 · 974 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

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

584 篇文章

订阅专栏

在这里插入图片描述

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

1. 视图概述与核心作用

V$SQLTEXT 是 Oracle 19C 中存储 SQL 语句文本内容的动态性能视图。它专门用于提供存储在共享池中的 SQL 语句的完整文本内容，是 SQL 性能分析和调试的重要工具。

核心作用：

提供共享池中 SQL 语句的完整文本内容
支持 SQL 语句的检索和识别
辅助 SQL 性能分析和调优
提供 SQL 语句的历史记录查询
支持应用程序 SQL 行为的分析

2. 主要使用场景

SQL 文本检索：查找特定 SQL 语句的完整文本内容
性能问题诊断：分析问题 SQL 的完整语句结构
应用程序调试：识别应用程序生成的 SQL 语句
安全审计：审计系统中执行的 SQL 语句内容
SQL 模式分析：分析 SQL 语句的编写模式和风格
绑定变量分析：查看 SQL 语句中使用的绑定变量

3. 字段详解

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

字段名	数据类型	含义说明	重要程度
ADDRESS	RAW(8)	父游标的内存地址	高
HASH_VALUE	NUMBER	SQL 语句的哈希值	高
SQL_ID	VARCHAR2(13)	SQL 语句的唯一标识符	高
COMMAND_TYPE	NUMBER	SQL 命令类型代码	中
PIECE	NUMBER	文本片段的序号	高
SQL_TEXT	VARCHAR2(64)	SQL 文本的片段内容	高
CON_ID	NUMBER	容器 ID（多租户环境）	中

4. 相关视图与基表

基表：

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

X$KGLCURSOR：游标信息的内部表
X$KGLTABLE：对象句柄表
X$KGLTR：文本存储的内部表

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

5. 底层原理与内部机制

数据存储机制：

共享池存储：SQL 文本存储在 SGA 的共享池中
分片存储：长 SQL 语句被分成多个 64 字节的片段存储
指针结构：使用 ADDRESS 和 HASH_VALUE 指向 SQL 语句的存储位置
内存管理：SQL 文本随游标的老化而从共享池中清除

SQL 文本管理：

文本分片：超过 64 字节的 SQL 语句被分成多个 PIECE
顺序存储：PIECE 字段表示文本片段的顺序（从 0 开始）
重组机制：需要通过按 PIECE 排序来重组完整 SQL 文本
内存优化：共享池使用 LRU 算法管理 SQL 文本内存

性能特征：

直接访问：直接访问共享池中的 SQL 文本内容
实时性：反映当前共享池中的 SQL 语句
内存效率：使用分片存储优化内存使用
检索效率：通过哈希值和地址快速定位 SQL 文本

6. 常用查询SQL

查询1：获取完整 SQL 语句文本

SELECT a.sql_id, a.piece, a.sql_text
FROM v$sqltext a
WHERE a.sql_id = '&sql_id'
ORDER BY a.piece;

查询2：查找包含特定文本的 SQL 语句

SELECT s.sql_id, s.sql_text, t.sql_fulltext
FROM v$sql s
CROSS APPLY (
    SELECT LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS sql_fulltext
    FROM v$sqltext t
    WHERE t.sql_id = s.sql_id
) t
WHERE t.sql_fulltext LIKE '%&search_text%'
AND ROWNUM <= 10;

查询3：重组长 SQL 语句的完整文本

SELECT sql_id, 
       LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) AS full_sql_text
FROM v$sqltext
WHERE sql_id = '&sql_id'
GROUP BY sql_id;

查询4：查找最近执行的 SQL 语句

SELECT s.sql_id, s.last_active_time,
       (SELECT LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece)
        FROM v$sqltext t
        WHERE t.sql_id = s.sql_id) AS sql_text
FROM v$sql s
WHERE s.last_active_time > SYSDATE - 1/24
ORDER BY s.last_active_time DESC
FETCH FIRST 5 ROWS ONLY;

查询5：分析 SQL 语句的类型分布

SELECT command_type, 
       COUNT(DISTINCT sql_id) as sql_count,
       MIN(piece) as min_pieces,
       MAX(piece) as max_pieces,
       AVG(piece) as avg_pieces
FROM v$sqltext
GROUP BY command_type
ORDER BY sql_count DESC;

查询6：查找使用绑定变量的 SQL

SELECT DISTINCT s.sql_id, s.sql_text
FROM v$sqltext s
WHERE s.sql_text LIKE '%:%'
  AND (s.sql_text LIKE '%:B%' OR s.sql_text LIKE '%:V%')
  AND s.piece = 0
ORDER BY s.sql_id;

查询7：多租户环境下的 SQL 文本查询

SELECT con_id, sql_id, piece, sql_text
FROM gv$sqltext
WHERE con_id = &container_id
  AND sql_id = '&sql_id'
ORDER BY piece;

查询8：分析 SQL 语句长度分布

SELECT sql_length_range, COUNT(*) as sql_count
FROM (
    SELECT sql_id,
           CASE 
               WHEN COUNT(*) <= 5 THEN 'SHORT (1-5 pieces)'
               WHEN COUNT(*) <= 20 THEN 'MEDIUM (6-20 pieces)'
               ELSE 'LONG (20+ pieces)'
           END as sql_length_range
    FROM v$sqltext
    GROUP BY sql_id
)
GROUP BY sql_length_range
ORDER BY sql_count DESC;