oracle sql tuning 3--常用檢查問題語句

問題:經常碰到一些儲存過程執行很久都跑不出結果，原因多樣，從接觸得最多的SQL語句上，有什麼處理辦法呢?

處理:

1.識別差SQL[預期響應時間>10S,假設每秒處理300個磁碟IO,每秒4000 buffer gets]

SELECT sql_text nl,
       'Executions='||executions  nl,
       'Expected Response Time in Seconds= ', 
       disk_reads / decode(executions, 0, 1,executions) / 300    "Response"  
FROM v$sql
WHERE  disk_reads / decode(executions,0,1, executions)/ 300 > 10
AND executions > 0
ORDER BY hash_value, child_number;

SELECT sql_text nl, 'Executions='|| 
    executions  nl,
   'Expected Response Time in Seconds= ', 
   buffer_gets / 
     decode(executions, 0, 1, executions) 
                / 4000 "Response"  
  FROM v$sql
 WHERE  buffer_gets / 
      decode(executions, 0,1, executions) 
                   /   4000 > 10
   AND executions > 0
ORDER BY hash_value, child_number;

2.識別當前長時間執行的查詢

SELECT username, sql_text, sofar, totalwork, units
  FROM v$sql, v$session_longops
 WHERE sql_address=address
   AND sql_hash_value=hash_value
 ORDER BY address, hash_value, child_number

3.從CLIENT跟蹤使用者SQL

例如：視窗1執行

SQL> insert into test001 values (2,'yyy');

1 row created.
不commit;

視窗2跟蹤視窗1的SQL

SQL> select ses_addr from v$transaction;

SES_ADDR
--------
35F0015C

SQL> select saddr,sql_address,prev_sql_addr,paddr from v$session where saddr='35F0015C';

SADDR     SQL_ADDR PREV_SQL PADDR
-------- -------- -------- --------
35F0015C 00      2FE52A18 35E1C370
SQL> SELECT SQL_TEXT,ADDRESS FROM V$SQL WHERE ADDRESS='2FE52A18';

SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS
--------
insert into test001 values (2,'yyy')
2FE52A18


SQL>

v$process中SPID是作業系統程式號,可以通過作業系統命令netstat 檢視到對應的應用程式

4.檢視當前會話在等待什麼事件

Select s.username,
       s.program,
       s.status,
       se.event,
       se.total_waits,
       se.total_timeouts,
       se.time_waited,
       se.average_wait
  from v$session s, v$session_event se

  Where s.sid = se.sid      
   And se.event not like 'SQl * Net%'      
   And s.status = 'ACTIVE'      
   And s.username is not null
   order by average_wait desc

5.檢視系統啟動以來特定事件的總等待數

select 
event as 等待事件的名稱,
total_waits as 等待事件的總次數,
total_timeouts as 事件的超時總次數,
time_waited as 此事件的總等待時間百分之1秒,
average_wait as 此事件的平均等待時間百分之1秒
from v$system_event

6.會話正在等待的資源或者事件

select
a.SID as 會話識別符號,
a.SEQ# as 標識等待的序列號,
a.event as 被等待的資源或事件,
a.SECONDS_IN_WAIT as 等待事件的秒數,
a.STATE as 狀態
from v$session_wait a

7.效能前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 ;

8.快速發現ORACLE_SERVER效能的成因

檢視佔io較大的正在執行的session

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,
se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st.
p1text,si.physical_reads,
si.block_changes FROM v$session se,v$session_wait st,
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC

看一下這些等待的程式都在忙什麼，語句是否合理

Select sql_address from v$session where sid=;
Select * from v$sqltext where address=;

應觀注一下event這列，這是我們調優的關鍵一列，下面對常出現的event做以簡要的說明：

a、buffer busy waits，free buffer waits這兩個引數所標識是dbwr是否夠用的問題，與IO很大相關的，當v$session_wait中的free buffer wait的條目很小或沒有的時侯，說明你的系統的dbwr程式決對夠用，不用調整;free buffer wait的條目很多，你的系統感覺起來一定很慢，這時說明你的dbwr已經不夠用了，它產生的wio已經成為你的資料庫效能的瓶頸，這時的解決辦法如下：

a.1、增加寫程式，同時要調整db_block_lru_latches引數。

示例：修改或新增如下兩個引數。

db_writer_processes=4
db_block_lru_latches=8

a.2、開非同步IO，IBM這方面簡單得多，hp則麻煩一些，可以與Hp工程師聯絡。

b、db file sequential read，指的是順序讀，即全表掃描，這也是我們應該儘量減少的部分，解決方法就是使用索引、sql調優，同時可以增大db_file_multiblock_read_count這個引數。

c、db file scattered read,這個引數指的是通過索引來讀取，同樣可以通過增加db_file_multiblock_read_count這個引數來提高效能。

d、latch free,與栓相關的了，需要專門調節。

e、其他引數可以不特別觀注。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-611496/，如需轉載，請註明出處，否則將追究法律責任。