Oracle sql共享池$sqlarea分析SQL资源使用情况

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

遇到需要排查一个系统使用 sql 的情况，可以通过查询 Oracle 的 $sql、$ssssion、$sqlarea 进行统计排查

排查时可以先看一下 $sql 和$session的基本信息

 select *
   from v$sql a, v$session b
  where b.SQL_ADDRESS = a.ADDRESS
    and b.SQL_HASH_VALUE = a.HASH_VALUE
    and b.SQL_CHILD_NUMBER = a.CHILD_NUMBER

$sqlarea 表列意思：

SQL_TEXT // 当前正在执行的游标的 sql 文本的前 1000 个字符
SQL_FULLTEXT //CLOB 类型 整个 sql 文本，不用借助于 V$SQL_TEXT 视图来查看整个文本
SQL_ID // 库缓存中的 SQL 父游标的标志
SHARABLE_MEM // 子游标使用的共享内存的大小,bytes
PERSISTENT_MEM // 子游标生存时间中使用的固定内存的总量,bytes
RUNTIME_MEM // 在子游标执行过程中需要的固定内存大小，bytes
SORTS // 子游标发生的排序数量
LOADED_VERSIONS // 显示上下文堆是否载入，1 是，0 否
USERS_OPENING // 执行这个 sql 的用户数
FETCHES // sql 取数据的次数
EXECUTIONS // 自从被载入共享池后，sql 执行的次数
FIRST_LOAD_TIME // 父游标产生的时间戳
PARSE_CALLS // 解析调用的次数
DISK_CALLS // 读磁盘的次数
DIRECT_WRITES // 直接写的次数
BUFFER_GETS // 直接从 buffer 中得到数据的次数
APPLICATION_WAIT_TIME // 应用等待时间，毫秒
CONCURRENCY_WAIT_TIME // 并发等待时间，毫秒
USER_IO_WAIT_TIME // 用户 IO 等待时间
ROWS_PROCESSED SQL // 解析 sql 返回的总行数
OPTIMIZER_MODE // 优化器模式
OPTIMIZER_COST // 优化器对于 sql 给出的成本
PARSING_USER_ID // 第一个创建这个子游标的用户 id
HASH_VALUES // 解析产生的哈希值
CHILD_NUMBER // 该子游标的数量
SERVICE // 服务名
CPU_TIME // 该子游标解析，执行和获取数据使用的 CPU 时间，毫秒
ELAPSED_TIME //sql 的执行时间，毫秒
INVALIDATIONS // 该子游标的无效次数
MODULE // 第一次解析该语句时, 通过 DBMS_APPLICAITON_INFO.SET_ACTION 设置的模块名
ACTION // 第一次解析该语句时, 通过 DBMS_APPLICAITON_INFO.SET_ACTION 设置的动作名
IS_OBSOLETE // 标记该子游标过期与否，当子游标过大时会发生这种情况
is_bind_sensitive // 不仅指出是否使用绑定变量窥测来生成执行计划，而且指出这个执行计划是否依赖于窥测到的值。如果是，这个字段会被设置为 Y，否则会被设置为 N。
is_bind_aware // 表明游标是否使用了扩展的游标共享。如果是，这个字段会被设置为 Y，如果不是，这个字段会被设置为 N。如果是设置为 N，这个游标将被废弃，不再可用。
is_shareable // 表明游标能否被共享。如果可以，这个字段会被设置为 Y，否则，会被设置为 N。如果被设置为 N，这个游标将被废弃，不再可用。

去 Oracle 的 sql 共享池 $sqlarea 查询 sql 的执行次数，分析 SQL 执行性能

EXECUTIONS：所有子游标执行 SQL 的次数
DISK_READS：所有子游标执行 SQL 需要读的硬盘次数
BUFFER_GETS：所有子游标执行 SQL 需要读的内存次数
ELAPSED_TIME：所有子游标执行 SQL 需要的时间

分析 SQL 执行性能

SELECT SQL_TEXT,
       SQL_FULLTEXT,
       ELAPSED_TIME,
       DISK_READS,
       BUFFER_GETS,
       EXECUTIONS,
       Round(ELAPSED_TIME / EXECUTIONS ,2),
       ROUND(DISK_READS / EXECUTIONS, 2),
       ROUND(BUFFER_GETS / EXECUTIONS , 2),
       ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) 
  FROM V$SQLAREA 
 WHERE EXECUTIONS > 0
   AND BUFFER_GETS > 0
   AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
   ORDER BY Round(ELAPSED_TIME / EXECUTIONS ,2) desc;

然后解释一下这些意义：
Round(ELAPSED_TIME / EXECUTIONS ,2)：求每个游标执行 SQL 需要的时间

ROUND(DISK_READS / EXECUTIONS, 2)：求每个游标执行 SQL 需要读磁盘的次数

ROUND(BUFFER_GETS / EXECUTIONS , 2)：求每个游标执行 SQL 需要读内存的次数

ROUND((BUFFER_GETS – DISK_READS) / BUFFER_GETS, 2)：SQL 命中率

所有归纳一下，就是去 $sqlarea 共享池查询统计，然后分析 sql，上述数据越低就说明 SQL 执行速度越高，性能越好。

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

：