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

ORA-4031错误解决方法(一)