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;

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/cb070f839c368f557b85b9d049c6c469.html