oracle v$sqlare 分析SQL语句使用资源情况

墨蓝 2021-06-26 16:06 278阅读 0赞

## `V$SQLAREA` ##

本视图持续跟踪所有`shared pool`中的共享`cursor`，在`shared pool`中的每一条SQL语句都对应一列。本视图在分析SQL语句资源使用方面非常重要。

`V$SQLAREA`中的信息列

`HASH_VALUE`：SQL语句的`Hash`值。  
`ADDRESS`：SQL语句在`SGA`中的地址。  
这两列被用于鉴别SQL语句，有时，两条不同的语句可能`hash`值相同。这时候，必须连同`ADDRESS`一同使用来确认SQL语句。  
`PARSING_USER_ID`：为语句解析第一条`CURSOR`的用户  
`VERSION_COUNT`：语句cursor的数量  
`KEPT_VERSIONS`：  
`SHARABLE_MEMORY`：cursor使用的共享内存总数  
`PERSISTENT_MEMORY`：cursor使用的常驻内存总数  
`RUNTIME_MEMORY`：cursor使用的运行时内存总数。  
`SQL_TEXT`：SQL语句的文本（最大只能保存该语句的前1000个字符）。  
`MODULE,ACTION`：使用了`DBMS_APPLICATION_INFO`时`session`解析第一条`cursor`时的信息

## `V$SQLAREA`中的其它常用列 ##

`SORTS`: 语句的排序数  
`CPU_TIME`: 语句被解析和执行的CPU时间  
`ELAPSED_TIME`: 语句被解析和执行的共用时间  
`PARSE_CALLS`: 语句的解析调用(软、硬)次数  
`EXECUTIONS`: 语句的执行次数  
`INVALIDATIONS`: 语句的cursor失效次数  
`LOADS`: 语句载入(载出)数量  
`ROWS_PROCESSED`: 语句返回的列总数

## `V$SQLAREA`中的连接列Column View Joined Column(s) ##

HASH_VALUE, ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESS 
    HASH_VALUE, ADDRESS V$SQLTEXT, V$SQL, V$OPEN_CURSOR HASH_VALUE, ADDRESS 
    SQL_TEXT V$DB_OBJECT_CACHE NAME

示例：  
1.查看消耗资源最多的SQL：

SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls  
    FROM V$SQLAREA  
    WHERE buffer_gets > 10000000 OR disk_reads > 1000000  
    ORDER BY buffer_gets + 100 * disk_reads DESC;

2.查看某条SQL语句的资源消耗：

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls  
    FROM V$SQLAREA  
    WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');

查找前10条性能差的sql语句

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea  
    order BY disk_reads DESC )where ROWNUM<10 ;

说明:  
`EXECUTIONS`表示同一条SQL语句一共执行了多少次，`SORTS`表示排序的次数，`DISK_READS`表示物理读的数量。

DISK_READS NUMBER

The sum of the number of disk reads over all child cursors

SORTS NUMBER

Sum of the number of sorts that were done for all the child cursors

EXECUTIONS NUMBER

Total number of executions, totalled over all the child cursors  
分析性能差的sql

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,   
    ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,   
    ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,   
    SQL_TEXT   
    FROM V$SQLAREA   
    WHERE EXECUTIONS>0   
    AND BUFFER_GETS >0   
    AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

查询共享池中已经解析过的SQL语句及其相关信息  
–`EXECUTIONS` 所有子游标的执行这条语句次数  
–`DISK_READS` 所有子游标运行这条语句导致的读磁盘次数  
–`BUFFER_GETS` 所有子游标运行这条语句导致的读内存次数  
–`Hit_radio` 命中率  
–`Reads_per_run` 每次执行读写磁盘数

笼统的说`EXECUTIONS，BUFFER_GETS，Hit_radio`越高表示读内存多，磁盘少是比较理想的状态，因此越高越好  
另外两个越高读磁盘次数越多，因此低点好

选出最占用资源的查询

select b.username username,a.disk_reads reads,a.executions exec,  
        a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,  
        a.sql_text statement  
        from v$sqlarea a,dba_users b  
        where a.parsing_user_id=b.user_id  
        and a.disk_reads>100000