oracle性能調整—診斷latch競爭

　　概念
    Latch是簡單的低層次的序列化技術用以保護SGA中的共享數據結構比如並發用戶列表和buffer cache裡的blocks信息一個服務器進程或後台進程在開始操作或尋找一個共享數據結構之前必須獲得對應的latch在完成以後釋放latch不必對latch本身進行優化如果latch存在競爭表明SGA的一部分正在經歷不正常的資源使用

　　）Latch的作用
    A序列化訪問保護SGA中的共享數據結構保護共享內存的分配
    B序列化執行避免同時執行某些關鍵代碼避免互相干擾

　　）Latch請求的兩種類型
    Awillingtowait請求的進程經過短時間的等待後再次發出請求直到獲得latch
    Bimmediate如果沒有獲得latch請求的進程不等待而是繼續處理其他指令
    檢查Latch競爭
    檢查latch free是不是主要的wait event
    Select * from v$system_event order by time_waited;

　　檢查latch的使用情況
    Select * from v$latch:
    與willingtowait請求有關的列getsmissessleepswait_timecwait_timespin_gets
    與immediate請求有關的列immediate_getsimmediate_misses

　　Gets: number of successful willingtowait requests for a latch;
    Misses: number of times an initial wilingtowait request was unsuccessful;
    Sleeps: number of times a process waited after an initial willingtowait request;
    Wait_time: number of milliseconds waited after willingtowait request;
    Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleepingthe overhead of context switches due to OS time slicing and page faults and interrupts;
    Spin_gets: gets that misses first try but succeed after spinning

　　Immediate_gets: number of successful immediate requests for each latch;
    Immediate_misss: number of unsuccessful immediate requests for each latch;

　　一般無需調整latch但是下列的措施是有用的
    A對處於競爭中的latch做進一步的調查
    B如果競爭主要存在於shared pool和library cache中可以考慮調整應用
    C如果進一步的調查顯示需要調整shared pool和buffer cache就進行調整

　　Select * from v$latch where name like %shared pool% or name like %library cache%

　　如果競爭是在shared pool或library cache上表示下列集中情況

　　A不能共享的sql應檢查他們是否相似考慮以變量代替sql中的常量
    Select sql_text from v$sqlarea where executions= order by upper(sql_text);
    B共享sql被重新編譯考慮library cache的大小是否需要調整
    SELECT sql_textparse_callsexecutions FROM v$sqlarea where parse_calls>;
    Clibrary cache不夠大

From:http://tw.wingwit.com/Article/program/Oracle/201311/16824.html