Oracle記憶體參數配置及版本問題

標籤：

      Oracle的記憶體配置與Oracle效能息息相關。從總體上講，可以分為兩大塊：共用部分（主要是SGA）和進程獨享部分（主要是PGA）。在 32 位作業系統下 的Oracle版本，不時有項目反饋關於記憶體的錯誤（如ORA-04030、04031錯誤）都是十分令人頭疼的問題。查閱資料瞭解到，ORA-04030的問題一般是PGA資源過度分派造成的（對應的操作是sort/hash_join）。在Oracle中pga_aggregate_target指定了所有session總共使用的最大PGA上限。經測實驗證，32位Oracle版本使用的實體記憶體保持在 1.6G以下為佳(SGA+PGA)，超過 1.7G左右系統開始不穩定，推薦的記憶體配置為：SGA=1200M，PGA=360M；

調整記憶體參數的命令樣本如下：

alter system set sga_max_size=1200M scope=spfile;alter system set sga_target=1200M scope=spfile;alter system set pga_aggregate_target=360M scope=spfile;

 

      另外，建議使用的Oracle版本：10.2.0.5、11.2.0.3/4；對於64位版本，建議先把20%的記憶體留給作業系統，剩餘80%分配給Oracle（其中SGA=實體記憶體*80%*80%，PGA=實體記憶體*80%*20%）。

      曾經在多重專案上發現過奇怪的現象，一個較複雜的SQL，直接執行或查看執行計畫，作業系統中可以看到CPU立刻飆到99%，而且即使等待很長時間（比如2分鐘，對於一個各表資料量小於10K的查詢，哪怕都走全表掃描也應該執行完的，2分鐘實在是太久了），CPU也不會降下來，SQL命令也無法正常結束，只能強制終止該會話或Oracle進程。該SQL訪問的所有表的資料量都不是很大（小於10K），更新統計資料等都沒有效果。我分別在Windows和Linux平台下的測試環境驗證過，問題都能夠重現，當然如果將SQL指令碼簡化也能解決，但沒有明顯的規律、規則，感覺應該是Oracle的bug，最後都是通過升級到最新版本解決的。

 

如分頁SQL指令碼（MV_118_CTLIST_03為視圖）：

SELECT MV_118_CTLIST_03."CTLIST_Name"    , MV_118_CTLIST_03."CTLIST_Depart_LSBMZD_BMMC"    , MV_118_CTLIST_03."CTLIST_Value"    , MV_118_CTLIST_03."CTLIST_Handler_LSZGZD_ZGXM"    , QRY_WORKITEM.STARTEDDATE    , QRY_WORKITEM.COMPLETEDDATE    , QRY_WORKITEM.PROCESSINSTANCEID    , QRY_WORKITEM.ACTIVITYDEFINITIONID    , QRY_WORKITEM.PROCESSDEFINITIONID    , QRY_WORKITEM.ActivityInstanceId    , QRY_WORKITEM.WORKITEMID    , QRY_WORKITEM.WORKTYPEFROM QRY_WORKITEM     JOIN MV_118_CTLIST_03 ON ROOTPROCINSTID = MV_118_CTLIST_03."CTLIST_SPID"    JOIN (SELECT PK          FROM (SELECT PK, rownum rowNumber                FROM (SELECT WORKITEMID AS PK                      FROM QRY_WORKITEM                          JOIN MV_118_CTLIST_03 ON ROOTPROCINSTID = MV_118_CTLIST_03."CTLIST_SPID"                      WHERE QRY_WORKITEM.Participant = ‘5b181b7c-8ea8-45a5-b35d-a90aed0725dc‘                         AND QRY_WORKITEM.State = ‘2‘                         AND QRY_WORKITEM.BIZPROCID = ‘0fad699e-a787-4fb6-bbff-8d3382f6d37f‘                      ORDER BY STARTEDDATE)                WHERE rownum <= 20)          WHERE rowNumber >= 1) tblPK ON workitemid = tblPK.PKWHERE QRY_WORKITEM.Participant = ‘5b181b7c-8ea8-45a5-b35d-a90aed0725dc‘     AND QRY_WORKITEM.State = ‘2‘     AND QRY_WORKITEM.BIZPROCID = ‘0fad699e-a787-4fb6-bbff-8d3382f6d37f‘ ORDER BY STARTEDDATE

Oracle記憶體參數配置及版本問題