ORA-4031错误 解决方法

遇到ORA-4031错误时，你的心里会不会发怵？ORA-4031很容易导致数据库出现异常崩溃，当Oracle的核心进程无法获得共享池内存时，它

遇到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;