oracle v$sqlare 分析SQL語句使用資源情況

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