Chinaunix首页 | 论坛 | 博客
  • 博客访问: 38330
  • 博文数量: 6
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 15
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 22:01




分类: Oracle

2015-09-15 14:06:43

原文地址:ORA-4031错误 作者:digdeep126

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中的所有内存清空。该方法治标不治本。
   select sql_fulltext from v$sql where executions=1 order by sql_text;
   alter system set cursor_sharing = 'force'; 来强制使用绑定变量。
3)使用shared pool中的保留区:
   select request_misses from v$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;

SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.

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.

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_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.

SHARED_POOL_SIZE_FOR_ESTIMATENUMBERShared pool size for the estimate (in megabytes)
SHARED_POOL_SIZE_FACTORNUMBERSize factor with respect to the current shared pool size
ESTD_LC_SIZENUMBEREstimated memory in use by the library cache (in megabytes)
ESTD_LC_MEMORY_OBJECTSNUMBEREstimated number of library cache memory objects in the shared pool of the specified size
ESTD_LC_TIME_SAVEDNUMBEREstimated 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_FACTORNUMBEREstimated parse time saved factor with respect to the current shared pool size
ESTD_LC_LOAD_TIMENUMBEREstimated elapsed time (in seconds) for parsing in a shared pool of the specified size
ESTD_LC_LOAD_TIME_FACTORNUMBEREstimated load time factor with respect to the current shared pool size
ESTD_LC_MEMORY_OBJECT_HITSNUMBEREstimated 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
--------------    -----------------  -------------------------   -------------
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.

阅读(1607) | 评论(0) | 转发(0) |