oracle v$sqlarea 分析SQL語句使用資源情況，oraclesqlarea

oracle v$sqlarea 分析SQL語句使用資源情況，oraclesqlarea

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： 

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

2.查看某條SQL語句的資源消耗： 

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

查找前10條性能差的sql語句 

1. SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea  
2. 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 

1. SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,   
2. ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,   
3. ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,   
4. SQL_TEXT   
5. FROM V$SQLAREA   
6. WHERE EXECUTIONS>0   
7. AND BUFFER_GETS >0   
8. 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越高表示讀內存多，磁盤少是比較理想的狀態，因此越高越好 
另外兩個越高讀磁盤次數越多，因此低點好 

選出最占用資源的查詢 
   

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