ORA-4031錯誤 解決方案

ORA-4031錯誤 解決方案

遇到ORA-4031錯誤時，你的心裡會不會發怵？ORA-4031很容易導致資料庫出現異常崩潰，當Oracle的核心進程無法獲得共用池記憶體時，它們會把資料庫異常宕掉。當然，ORA-4031就像黃燈一樣，它在提醒你需要馬上採取緊急策略了。不是說它一出現，資料庫馬上就會宕機。

話題討論：
1.ORA-4031是Oracle中最令人厭惡的報錯，它的出現有哪幾種原因？

2.一旦出現4031錯誤，也就是說共用池記憶體不足的情況下，我們該如何釋放記憶體呢？你有木有遇到過相應的情形呢？請回憶下當時是如何解決的？

3.有一句俗語叫丟車保帥，執行計畫是SQL核心關鍵所在，是否意味著在釋放記憶體時，不應該覆蓋執行計畫這部分？當執行計畫部分佔用記憶體比較多時，如何最佳化呢？

4.消耗共用池記憶體的主要有兩種對象，一種是SQL，而是預存程序、函數、包等這類可執行對象，當遇到4031錯誤時，您覺得需要優先釋放哪種對象

問題分析:

1、ORA-4031錯誤的原因，一般是大量的hard parse導致了shared pool中的free list中產生大量的記憶體小片段，當一個需要很大記憶體來進行hard parse的sql語句到來時，無法從free list中找到記憶體，即使進行記憶體的釋放，還是不能找到符合的記憶體塊。從而報ORA-4031錯誤。

2. ORA-4031錯誤的解決方案：

1）alter system flush shared_pool;將shared pool中的所有記憶體清空。該方法治標不治本。
2）共用SQL語句：規範SQL語句的書寫；使用綁定變數；找到沒有使用綁定變數的SQL：
  如果在結果中發現一系列僅僅字面值不同的SQL，則可以修改cursor_sharing參數：
  alter system set cursor_sharing = 'force'; 來強制使用綁定變數。
3）使用shared pool中的保留區：
  select request_misses from v$shared_pool_reserved;
  如果結果大於0，則可以調大shared_pool_reserved的大小；
SQL> show parameter shared_pool
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 4M
shared_pool_size                    big integer 0
alter system set shared_pool_reserved=xxM scope=both;
4）使用dbms_shared_pool.keep('對象名')將使用記憶體很大的對象keep在記憶體中：
  先要執行：@?/rdbms/admin/dbmspool.sql
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.

再查出需要keep的對象：
SQL> select owner,name,namespace,type,sharable_mem from v$db_object_cache where sharable_mem>10000
  2  and (type='PACKAGE' or type='PACKAGE BODY' or type='FUNCTION' or type='PROCEDURE') and kept='NO';
OWNER      NAME                      NAMESPACE          TYPE            SHARABLE_MEM
---------- ------------------------- ------------------ --------------- ------------
SYS        DBMS_BACKUP_RESTORE      TABLE/PROCEDURE    PACKAGE                33215
SYSMAN    EMD_COLLECTION            BODY              PACKAGE BODY          33233
SYS        DBMS_SHARED_POOL          BODY              PACKAGE BODY          12644
SYS        SYS$RAWTOANY              TABLE/PROCEDURE    FUNCTION              12640
SYSMAN    EMD_MAINTENANCE          TABLE/PROCEDURE    PACKAGE                29030
SYSMAN    EMD_MAINTENANCE          BODY              PACKAGE BODY          62930
SYSMAN    MGMT_JOB_ENGINE          BODY              PACKAGE BODY          218914
SYSMAN    EM_PING                  BODY              PACKAGE BODY          29086
SYS        DBMS_BACKUP_RESTORE      BODY              PACKAGE BODY          95519
SYSMAN    EMD_LOADER                TABLE/PROCEDURE    PACKAGE                12641
SYSMAN    EMD_LOADER                BODY              PACKAGE BODY          71861
SYS        PRVT_HDM                  BODY              PACKAGE BODY          43624
SYSMAN    MGMT_JOB_ENGINE          TABLE/PROCEDURE    PACKAGE                24938
SYS        STANDARD                  BODY              PACKAGE BODY          24960
SYSMAN    EM_SEVERITY_REPOS        BODY              PACKAGE BODY          33236
SYS        PRVT_ADVISOR              TABLE/PROCEDURE    PACKAGE                12640
SYSMAN    MGMT_GLOBAL              TABLE/PROCEDURE    PACKAGE                29902
SYS        DBMS_STANDARD            TABLE/PROCEDURE    PACKAGE                24929
SYS        DBMS_ADVISOR              BODY              PACKAGE BODY          25000
SYS        PRVT_HDM                  TABLE/PROCEDURE    PACKAGE                16732
SYS        PRVT_ADVISOR              BODY              PACKAGE BODY          66780
SYS        DBMS_RCVMAN              TABLE/PROCEDURE    PACKAGE                43295
SYS        STANDARD                  TABLE/PROCEDURE    PACKAGE              438648
SYS        DBMS_RCVMAN              BODY              PACKAGE BODY          375759

24 rows selected.
5）增加shared_pool_size的大小：
SQL> select component,current_size from v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                          75497472
large pool                                                            4194304
java pool                                                            4194304
streams pool                                                                0
DEFAULT buffer cache                                                130023424
KEEP buffer cache                                                          0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                    0
DEFAULT 4K buffer cache                                                    0
DEFAULT 8K buffer cache                                                    0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
ASM Buffer Cache                                                            0

13 rows selected.

sga_max_size：SGA允許的最大值，修改必須重啟；
sga_target：必須小於sga_max_size, 表示當前SGA的最大值；
alter system set shared_pool_size=xxM scope=both;

3. 使用V$SHARED_POOL_ADVICE來設定shared pool的大小
V$SHARED_POOL_ADVICE displays information about estimated parse time in the shared pool for different pool sizes. The sizes range from 10% of the current shared pool size or the amount of pinned library cache memory (whichever is higher) to 200% of the current shared pool size, in equal intervals. The value of the interval depends on the current size of the shared pool.
Column Datatype Description
SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER Shared pool size for the estimate (in megabytes)
SHARED_POOL_SIZE_FACTOR NUMBER Size factor with respect to the current shared pool size
ESTD_LC_SIZE NUMBER Estimated memory in use by the library cache (in megabytes)
ESTD_LC_MEMORY_OBJECTS NUMBER Estimated number of library cache memory objects in the shared pool of the specified size
ESTD_LC_TIME_SAVED NUMBER Estimated elapsed parse time saved (in seconds), owing to library cache memory objects being found in a shared pool of the specified size. This is the time that would have been spent in reloading the required objects in the shared pool had they been aged out due to insufficient amount of available free memory.
ESTD_LC_TIME_SAVED_FACTOR NUMBER Estimated parse time saved factor with respect to the current shared pool size
ESTD_LC_LOAD_TIME NUMBER Estimated elapsed time (in seconds) for parsing in a shared pool of the specified size
ESTD_LC_LOAD_TIME_FACTOR NUMBER Estimated load time factor with respect to the current shared pool size
ESTD_LC_MEMORY_OBJECT_HITS NUMBER Estimated number of times a library cache memory object was found in a shared pool of the specified size

可以使用下面的SQL語句來預估shared pool的大小：
select 'Shared Pool' component,shared_pool_size_for_estimate estd_sp_size,estd_lc_time_saved_factor parse_time_factor,case when current_parse_time_elapsed_s + adjustment_s<0then 0 else current_parse_time_elapsed_s + adjustment_s end response_timefrom (select shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,a.estd_lc_time_saved,e.value/100current_parse_time_elapsed_s,c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s from v$shared_pool_advice a,(select * from v$sysstat where name='parse time elapsed') e,(select estd_lc_time_saved from v$shared_pool_advice where shared_pool_size_factor=1) c);COMPONENT  ESTD_SP_SIZE  PARSE_TIME_FACTOR  RESPONSE_TIME--------------    -----------------  -------------------------  -------------Shared Pool          64            .9989                          294.37Shared Pool          72                1                            257.37Shared Pool          80            1.0009                        226.37Shared Pool          88            1.0016                        201.37Shared Pool          96            1.0022                        181.37Shared Pool          104            1.0027                        166.37Shared Pool          112            1.0029                        156.37Shared Pool          120            1.0032                        149.37Shared Pool          128            1.0033                        144.37Shared Pool          136            1.0034                        141.37Shared Pool          144            1.0034                        139.37
11 rows selected.