首页 / 数据库 / MySQL / 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          37575924 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                                                            013 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.更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址