Oracle SQL资源消耗相关视图

共计 6027 个字符，预计需要花费 16 分钟才能阅读完成。

一、常用视图说明

Oracle SQL 语句资源消耗监控，最常用的系统视图有：

v$sql

v$sqlarea

v$sqltext

v$session

v$sql 和 v$sqlarea 基本相同，记录了共享 SQL 区（share pool）中 SQL 统计信息，如内存消耗、IO（物理磁盘读和逻辑内存读）、排序操作、哈希 ID 等数据。不同之处在于 v$sql 为每一条 SQL 保留一个条目，而 v$sqlarea 中根据 sql_text（需要注意，该处存储的为当前 SQL 指针的前 1000 个字符，也就是说这里记录的 SQL 可能是不完整的！）进行 group by，统计列进行 sum()，通过 version_count 计算子指针的个数。

  然而，文本（sql_text）相同的 SQL 语句在数据库中意义可能完全不同。比如数据库中存在两个用户 User1 和 User2，这两个用户各拥有一张数据表 EMP。那么当两个用户发出查询 select count(*) from emp; 时各自访问自己 SCHEMA 中的表 EMP，而两者表内容不同所以其资源消耗肯定也不同。此时，在 v$sql 中会有这两条完全一样的 SQL 各自的统计信息，而在 v$sqlarea 中 sql_text 相同的 2 个指针会合并起来，执行次数、DISK_READS、BUFFER_GETS 等统计信息都会累加（sum），version_count 会显示为 2，这就是 v$sqlarea 的聚合作用。

v$sqltext 中没有统计信息，然而却存储着完整的 SQL 语句及其哈希 ID 等。

对于这三者，我们可以使用视图 v$fixed_view_definition 来查看视图的源表，如下：

SELECT view_definition FROM v$fixed_view_definition WHERE view_name=’GV$SQL’;

SELECT view_definition FROM v$fixed_view_definition WHERE view_name=’GV$SQLAREA’;

SELECT view_definition FROM v$fixed_view_definition WHERE view_name=’GV$SQLTEXT’;

注：视图名为 V$SQL 但该视图的源又是 GV$SQL，所以直接使用 GV$SQL，其他两个也如此。

通过以上 3 条语句可以发现，V$SQL 数据来源 X$KGLCURSOR_CHILD，其实数据还是来源于 X$KGLOB；而 V$SQLAREA 数据来源 X$KGLCURSOR_CHILD_SQLID 本质是对 X$KGLCURSOR_CHILD 按照 sql_id 等字段分组汇总后的结果；V$SQLTEXT 数据来源 X$KGLNA。

v$session 主要用来确定会话相关信息，如通过 SID 和 SERIAL# 来唯一确定一个 session（SID 可能会重复）、会话拥有者用户名 USERNAME、会话状态（active: 正在执行 SQL 语句、inactive: 等待操作、killed: 被杀死）、会话由哪个客户端发起（MACHINE、TERMINAL）、正在执行什么 SQL（通过 SQL_ADDRESS、SQL_HASH_VALUE、SQL_ID、SQL_CHILD_NUMBER 确定，有这些再借助 v$sqltext 就能知道）、甚至上一次执行的 SQL 是什么（通过 PREV_SQL_ADDRESS 等确定）、锁等待相关信息（如所在表、文件、块、被锁行）等。

高资源消耗 SQL 查找定位

1）查看读硬盘多或占用内存可能多的 SQL：

select sql_text, disk_reads, buffer_gets, parsing_schema_name, executions

from v$sqlarea

order by disk_reads desc;

说明：单纯从 V$sqlarea 中是无法查出每个 SQL 消耗的内存量的，但我们可以借助磁盘读次数间接反映可能的消耗内存量较大的 SQL 语句，然后再借助执行计划（如 v$sql_plan 视图）具体查看。

利用系统视图 v$sqlarea，其中 disk_reads 是磁盘读次数，也是主要字段，剩余字段均为参考字段。其中，buffer_gets 是内存读次数，parsing_schema_name 是首次编译者模式名（一般与 user 名相同），executions 是语句执行次数。

需要注意的是，v$sqlarea 中 sql_text 可能不完整，若需要完整的则需要借助 hash_value 或 sql_id 结合 v$sqltext 来查看分析。

2）查看执行次数多的 SQL

select sql_text, executions, parsing_schema_name

from v$sqlarea

order by executions desc;

3）查看排序多的 SQL

select sql_text, sorts, parsing_schema_name

from v$sqlarea

order by sorts desc;

该处还应涉及 Library Cache 命中率、内存命中率等内容，暂不总结。

4）查看总消耗时间最多的前 10 条 SQL 语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

5）查看 CPU 消耗时间最多的前 10 条 SQL 语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

6）查看消耗磁盘读取最多的前 10 条 SQL 语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

注：如查执行上面的查寻提示 RA-00942: table or view does not exist

出现这个体会提示并不是说数据库实例中没有 v$sql 对象，而是你当前的用户没有权限。解决办法就是给当前用户赋予 v$sqlarea 的查询权限

sql>grant select any dictionary to myuser;

相关视图重要字段

v$sqlarea

v$sqlareav$sql 和 v$sqlarea 基本类似，而 v$sqlarea 更常用，故仅对 v$sqlarea 常用字段进行说明，如下（个人参考 Oracle 官方文档翻译的，因是最新版本，所以会跟网络上的有些出入）：

SQL_TEXT：SQL 语句的前 1000 个字符；

SQL_FULLTEXT：SQL 语句的所有字符；

SQL_ID：缓存在高速缓冲区（library cache）中的 SQL 父游标的唯一标识 ID（注，类似于 hash_value，不过 hash_value 是 4bytes 而 sql_id 是 8bytes，sql_id 更精确后期可能会替代 hash_value）；

SHARABLE_MEM：SQL 语句及其子游标占用的共享内存大小；

PERSISTENT_MEM：打开 SQL 语句的生命周期内所占用的固定内存大小（包含子游标）；

RUNTIME_MEM：游标执行期间所占用的固定内存大小；

SORTS：语句执行导致的排序次数；

VERSION_COUNT：在缓存中以该语句为父语句的子游标总数；

LOADED_VERSIONS：缓存中载入了这条语句上下文堆（KGL heap 6）的子游标数；

OPEN_VERSIONS：父游标下打开的子游标个数；

USERS_OPENING：打开子游标的用户个数；

FETCHES：SQL 语句的 fetch 数；

EXECUTIONS：包含所有子游标在内该 SQL 语句共执行次数；

USERS_EXECUTING：执行过该语句所有子游标的用户总数；

LOADS：语句被载入的总次数；

FIRST_LOAD_TIME：父游标被首次载入（编译）的时间；

PARSE_CALLS：父游标下所有子游标解析调用次数；

DISK_READS：该语句通过所有子游标导致的读磁盘次数；

DIRECT_WRITES：该语句通过所有子游标导致的直接写入次数；

BUFFER_GETS：该语句通过所有子游标导致的读缓存次数；

APPLICATION_WAIT_TIME：应用等待时间；

USER_IO_WAIT_TIME：用户 I / O 等待时间；

PLSQL_EXEC_TIME：PLSQL 执行时间；

ROWS_PROCESSED：该 SQL 语句处理的总行数；

OPTIMIZER_COST：此查询优化给出的成本数；

PARSING_USER_ID：第一次解析该父语句的用户 ID；

PARSING_SCHEMA_ID：第一次解析该语句 SCHEMA 的 ID；

PARSING_SCHEMA_NAME：解析该语句的 SCHEMA 的 NAME；

KEPT_VERSIONS：指出是否当前子游标被使用 DBMS_SHARED_POOL 包标记为常驻内存；

ADDRESS：当前游标父句柄（唯一指向该游标的一种地址编号）；

HASH_VALUE：该语句在 library cache 中 hash 值；

PLAN_HASH_VALUE：执行计划的 hash 值，可依此确定两个执行计划是否相同（取代每行每字符进行比较的方式）；

CPU_TIME：该语句解析、执行和 fetch（取值）所消耗的 CPU 时间；

ELAPSED_TIME：该语句解析、执行和 fetch（取值）所经过的时间；

LAST_ACTIVE_TIME：查询计划最后一次执行的时间；

LOCKED_TOTAL：所有子游标被锁的次数；

v$sqltext

ADDRESS：当前游标父句柄（唯一指向该游标的一种地址编号）；

HASH_VALUE：该游标（子游标）在 library cache 中唯一 hash 值；

SQL_ID：缓存游标中该 SQL 的一个唯一标识值；

COMMAND_TYPE：SQL 语句类型，如 select、insert、update 等；

PIECE：排序 SQL 文本的碎片数；

SQL_TEXT：包含一个完整 SQL 中的某一小块 SQL 文本字符（要完整的 SQL 语句需要把这些碎片组合起来）；

v$session

SADDR：session 地址；

SID：session 标识值，常跟 serial# 联合唯一确定一个 session（在杀进程时，有时 SID 会重用，造成误杀。而 serial 会增加但不会重复，sid 在同一个 instance 的当前 session 中是一个 unique key，而 sid ,serial# 则是在整个 instance 生命期内的所有 session 中是 unique key）；

SERIAL#： 会话序列号，用于在一个会话结束而另一个会话重用这该会话的 SID 时，唯一确定一个会话；

AUDSID：审计会话 ID，可以通过 audsid 查询当前 session 的 sid，select sid from v$session where audsid=userenv(‘sessionid’)；

PADDR：进程地址，关联 v$process 的 addr 字段，通过这个可以查询到进程对应的 session；

USER#： 同于 dba_users 中的 user_id，Oracle 内部进程 user# 为 0；

USERNAME：会话拥有者用户名，等于 dba_users 中的 username，Oracle 内部进程的 username 为空；

COMMAND：正在���行的 SQL 语句类型，如 1 为 create table、3 为 select 等；

OWNERID：如果该列值为 2147483644 则值无效，否则值用于会话迁移、并行等；

TADDR：Address of transaction state object；

LOCKWAIT：标识当前查询是否处于锁等待状态，为空则表示无等待；

STATUS：标识 session 状态，Active 正执行 SQL 语句，inactive 等待操作，killed 被标注为杀死；

SERVER：服务器类型，DEDICATED 专用、SHARED 共享等；

SCHEMA#：SCHEMA 标识 ID 值，Oracle 内部进程的 schema# 为 0；

SCHEMANAME：SCHEMA 用户名，Oracle 内部进程的为 sys；

OSUSER：客户端操作系统用户名；

PROCESS：客户端操作系统进程 ID；

MACHINE：操作系统机器名；

TERMINAL：操作系统终端名；

PROGRAM：操作系统应用程序名，如 EXE 或 sqlplus.exe；

TYPE：会话类型，如 BACKGROUND 或 USER；

SQL_ADDRESS：和 SQL_HASH_VALUE 一起使用标识正在执行的 SQL 语句；

SQL_HASH_VALUE：和 SQL_ADDRESS 一起使用标识正在执行的 SQL 语句；

SQL_ID：正在执行的 SQL 语句的标识 ID；

SQL_CHILD_NUMBER：正在执行的 SQL 语句的子 ID；

FIXED_TABLE_SEQUENCE：当 session 完成一个 user call 后就会增加的一个数值，也就是说，如果 session 挂起，它就不会增加。因此可以根据这个字段来监控某个时间点以来的 session 性能情况。例如，一个小时前某个 session 的此字段数值为 10000，而现在是 20000，则表明一个小时内其 user call 较频繁，可以重点关注此 session 的 performance statistics。

ROW_WAIT_OBJ#： 被锁定行所在 table 的 object_id，和 dba_object 中的 object_id 关联可以得到被锁定的 table name；

ROW_WAIT_FILE#： 被锁定行所在的 datafile id，和 v$datafile 中的 file# 关联可以得到 datafile name；

ROW_WAIT_BLOCK#： 被锁定的块 ID；

ROW_WAIT_ROW#： 被锁定的当前行；

LOGON_TIME：登录时间；

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-08/146125.htm