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错误的解决方法：
再查出需要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<0 then 0 else current_parse_time_elapsed_s + adjustment_s end response_time from ( select shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,a.estd_lc_time_saved,e.value/100 current_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.37 Shared Pool 72 1 257.37 Shared Pool 80 1.0009 226.37 Shared Pool 88 1.0016 201.37 Shared Pool 96 1.0022 181.37 Shared Pool 104 1.0027 166.37 Shared Pool 112 1.0029 156.37 Shared Pool 120 1.0032 149.37 Shared Pool 128 1.0033 144.37 Shared Pool 136 1.0034 141.37 Shared Pool 144 1.0034 139.37
11 rows selected.
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。