oracle v$sqlarea 分析SQL語句使用資源情況 確認是否綁定變數

標籤：

-如何確定系統中是否存在綁定變數的情況：
首先建立一個表，用於存放整理過得資料：
create table t1 as select sql_text from v$sqlarea;
----V$SQLAREA本視圖持續跟蹤所有shared pool中的共用cursor，
--在shared pool中的每一條SQL語句都對應一列。本視圖在分析SQL語句資源使用方面非常重要。
/**
*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表示物理讀的數量。
分析效能差的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>10000 

  
給表增加一個欄位：
alter table t1 add sql_text_wo_constants varchar2(1000);
建立函數 remove_constants:
create or replace function
remove_constants(p_query in varchar2) return varchar2 as 
 l_query long;
 l_char varchar2(1);
 l_in_quotes boolean default false;
begin 
 for i in 1 .. length(p_query) loop
     l_char :=substr(p_query,i,1);
     if(l_char ="" and l_in_quotes) then
     l_in_quotes= false;
     else  if(l_char ="" and not l_in_quotes) then
     l_in_quotes= true;
     l_query:=l_query||‘#‘;
     end if;
     if(not l_in_quotes) then
     l_query := l_query||l_char;
     end if;
  end loop;
  l_query := translate(l_query,‘0123456789‘,‘@@@@@@@@@@‘);
  for i in 0..8 loop
      l_query := replace(l_query,lpad(‘@‘,10-i,‘@‘),‘@‘);
      l_query := replace(l_query,lpad(‘ ‘,10-i,‘ ‘),‘ ‘);
      end loop;
      return upper(l_query);
 end;
 /
 ----下面是如何使用這個函數
 將v$sql視圖中的資料用remove_constants處理後，更新到t1表中：
 update t1 set sql_text_wo_constants = remove_constants(sql_text);
 --查出除了謂語條件不同的SQL語句和它們的執行次數
 select sql_text_wo_constants,count(*) from t1 
 group by sql_text_wo_constants having count(*)>100 order by 2;
 ---使用一個迴圈執行1000次某條SQL，每次執行時只有謂語不同：
ed
begin 
 for i in 1..1000 loop
 execute immediate ‘select *from t where rm=‘||i;
 end loop;
 end;

select sql_text_wo_constants,count(*) from t1
group by sql_text_wo_constants 
having count(*)>100
order by 2;

 

 

V$SQLAREA 

　　本視圖持續跟蹤所有shared pool中的共用cursor，在sharedpool中的每一條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： 
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語句的資源消耗： 
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語句 
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 childcursors 

SORTS NUMBER 
Sum of the number of sorts that were done for all the childcursors 

EXECUTIONS NUMBER 
Total number of executions, totalled over all the childcursors 
分析效能差的sql 
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越高表示讀記憶體多，磁碟少是比較理想的狀態，因此越高越好 
另外兩個越高讀磁碟次數越多，因此低點好 

選出最佔用資源的查詢 
   Sql代碼  

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  

 

 

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

oracle v$sqlarea 分析SQL語句使用資源情況 確認是否綁定變數