Oracle SQL调优系列之sql共享池

约定不等于承诺〃 2021-09-17 21:26 326阅读 0赞

遇到需要排查一个系统使用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表列意思：  
引用：https://blog.csdn.net/study4034/article/details/51331120

> 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执行速度越高，性能越好