ORA-4031

浮萍哥

已于 2024-04-24 19:58:23 修改

阅读量665

点赞数 29

分类专栏： Oracle报错文章标签： oracle

于 2024-04-24 18:03:16 首次发布

本文链接：https://blog.csdn.net/qq_58503141/article/details/138164714

版权

Oracle报错专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、出错原因：

经典的Oracle4031错误也与share pool密切的关系。

Library Cache（库缓存）：

主要缓存的是SQL语句，以及SQL句解析出来的执行计划。

Data Dictionary Cache （字典缓存）：

Oracle数据库的自身信息都存储在数据字典中（比如说：数据库中有多少表，有多少用户，表中有多少列每个表多大等等）

Result Cache:用于存储和检索高速缓存的结果

图中橙色的圆代表一个个内存块在Oracle中称之为chunk,而这些chunk会根据大小的不容被归类挂载一条条chain上，从下往上越来越大。

在这里举个例子：

如果有一条SQL语句解析出来大小为10K，那么就在第8K-12K的内存链上找比如找到一个11K的块那么就将其中的10K丢到Library Cache中，而剩下的1K再挂到相应的空间链里面去。

这里需要特别提醒强调的是——什么时候需要找chunk？答案是在执行硬解析的时候。

由此可见当有大量硬解析的时候，除了要去library cache中找chunk还会产生大量的小碎片，于是就有可能产生这种情况，及有大量足够的空间但是被分割成很多小的碎片，没有适合可用的内存块。这种情况便会产生Oracle经典的4031报错。

可能主要因为以下几个方面：

共享池大小设置不合理：如果共享池的大小设置过小，无法容纳所有需要使用的共享内存结构，就会出现ORA-4031错误
内存碎片化：频繁地创建和销毁大量的共享内存结构可能会导致内存碎片化，从而无法分配连续的内存空间，引发ORA-4031错误
大量的硬解析（Hard Parses）：硬解析会消耗更多的CPU和内存资源，并可能导致内存小碎片的产生，最终导致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 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.

本文参考ORA-4031错误解决方法 - yjbjingcha - 博客园 (cnblogs.com)

浮萍哥

关注

29
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
ORA-4031

经典的Oracle4031错误也与share pool密切的关系。Library Cache（库缓存）：主要缓存的是SQL语句，以及SQL句解析出来的执行计划。Data Dictionary Cache （字典缓存）：Oracle数据库的自身信息都存储在数据字典中（比如说：数据库中有多少表，有多少用户，表中有多少列每个表多大等等）Result Cache:用于存储和检索高速缓存的结果。
复制链接

扫一扫

专栏目录