Oracle 性能問題一般解決思路

1、v檢查V$session_wait視圖，查看當(dāng)前等待事件,根據(jù)等待事件，判斷是否由于鎖等引起v通過操作系統(tǒng)命令top/topas檢查耗CPU較高的進(jìn)程，然后定位到對(duì)應(yīng)的SQL語句。v通過SQL語句直接查占IO排名前多少位的SQL語句v查看V$sysstat看看是否hard parse非常多v檢查alert.log是否包含了異常信息或者錯(cuò)誤vSelect event,count(*) from v$session_wait group by event;有問題的主要事件有：venqueue vdb file scattered read vdirect path read 或 direct pa

2、th write vglobal cache cr requestvlibrary cache lock vlibrary cache pin vrow cache lock vENQUEUE事件說明當(dāng)前會(huì)話在等待其他會(huì)話所占用的資源。該會(huì)話將一直等待下去直到它等待的會(huì)話釋放資源，并可能將整個(gè)數(shù)據(jù)庫(kù)hang住。多是DML類型鎖。v查看enqueue的類型 SQ Enqueue for Sequence Numbers TX Enqueue for a Transaction select sid,chr(bitand(p1, -16777216)/16777215) |chr(bitand(p

3、1,16711680)/65535) Name,(bitand(p1, 65535) Modefrom v$session_waitwhere event = enqueue;SID Name Mode- - -64 TX 6 解決方法： SELECT DECODE(REQUEST,0,Holder: ,Waiter: )|SID SESS,ID1,ID2, LMODE, REQUEST, TYPE FROM V$LOCK WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST0) ORDER BY

4、ID1, REQUEST檢查出標(biāo)記為holder的會(huì)話號(hào),并做進(jìn)一步查看原因，可能需要將holder的會(huì)話殺掉。 如果enqueue類型是SQv$session_wait.p2 is DBA_OBJECTS.object_id, so you can find out which sequence the application is competing如果是TX型v$session.row_wait_obj# is DBA_OBJECTS.object_id 該事件是指該會(huì)話在讀很多數(shù)據(jù)塊。一般情況下說明該會(huì)話在做全表掃描或者全索引掃描，當(dāng)這種類型的事件較多時(shí)應(yīng)該檢查這些語句是否沒有用到索引

5、或者表上沒有建合適的索引。direct path read 或 direct path write 該事件說明該會(huì)話在做外部排序。大量的外部排序會(huì)導(dǎo)致數(shù)據(jù)庫(kù)性能很低，應(yīng)該檢查語句是否有問題。可以的話，可以增加PGA_AGGREGATE_TARGETglobal cache cr request 該事件說明RAC環(huán)境中，該會(huì)話在等待另外一個(gè)instance中的會(huì)話釋放資源。如果較多的該事件發(fā)生時(shí)，必須檢查另外的instance等待的事件，看是否有異常語句在運(yùn)行l(wèi)ibrary cache lock 主要是由于編譯存儲(chǔ)過程或者SQL硬解析等引起，多與Library cache pin 一起產(chǎn)生。 查

6、看語句：必須以sys用戶登錄 SELECT decode(lock_a.kgllkreq,0,Holder:,Waiter:),sid,machine,program FROM X$KGLLK LOCK_A ,V$SESSION SES_A WHERE LOCK_A.KGLLKSES = SES_A.SADDR and lock_a.KGLLKHDL in( select KGLLKHDL from X$KGLLK where KGLLKREQ 0) order by KGLLKHDL,kglLKreq library cache pin 與Library cache lock相似，主要是由于

7、ddl操作或者存儲(chǔ)過程編譯等引起。查看語句：必須以sys用戶登錄 SELECT decode(lock_a.kglpnreq,0,Holder:,Waiter:),sid,machine,program FROM X$KGLPN LOCK_A ,V$SESSION SES_A WHERE LOCK_A.KGLPNSES = SES_A.SADDR and lock_a.KGLPNHDL in( select KGLPNHDL from X$KGLPN where KGLPNREQ 0) order by KGLPNHDL,kglpnreqvHP-UNIX top hvIBM AIX topas

8、v獲得占CPU高的進(jìn)程號(hào)后，到數(shù)據(jù)庫(kù)里面執(zhí)行下面的語句獲得session信息Select * from v$session where paddr in(Select addr from v$process where spid =?)vselect * from (select sql_text,round(a.disk_reads + a.buffer_gets)/a.executions),HASH_VALUE from v$sqlarea awhere executions 0order by 2 desc)where rownum 0v如果hard parse比例比較多，需要多檢查是否v對(duì)已經(jīng)存在業(yè)務(wù)，如果不能修改程序的話，可以嘗試將參數(shù)cursor_sharing=similarvV$session_longopsvselect sid,target,sofar,TOTALWORK,time_remaining,ELAPSED_SECONDS from v$session_longopsv將下面的觸發(fā)器建到需要的用戶下面即可vcreate or replace trigger tri_loggon_getip after logon on schemabegind