Oracle调优与深入之04031处理过程

第第页Oracle调优与深入之04031处理过程Oracle调优与深入之04031处理过程

发表于：2023-06-22来源：：点击数：标签：

一、错误提示：>EXP-00008:ORACLEerror4031encountered>ORA-04031:unabletoallocate4096bytesofsharedmemory("sharedpool","BEGIN:EXEC_STR:=SYS.DBMS...","PL/SQLMPCODE","BAMIMA:BamBuffe二、错误原因：共享内存太小，存在

一、错误提示：

>EXP-00008:ORACLEerror4031encountered

>ORA-04031:unabletoallocate4096bytesofsharedmemory

("sharedpool","BEGIN:EXEC_STR:=SYS.DBMS...","PL/SQLMPCODE","BAMIMA:BamBuffe

二、错误原因：

共享内存太小，存在一定碎片，没有有效的利用保留区，造成无法分配合适的共享区。

三、解决步骤：

1.查看当前环境

SQL>showsga

TotalSystemGlobalArea566812832bytes

FixedSize73888bytes

VariableSize28811264bytes

DatabaseBuffers536870912bytes

RedoBuffers1056768bytes

showparametershared_pool

NAMETYPEVALUE

shared_pool_reserved_sizestring1048576

shared_pool_sizestring20971520

SQL>selectsum(free_space)fromv$shared_pool_reserved;

SUM(FREE_SPACE)

1048576

我们可以看到没有合理利用保留区

SQL>SELECTSUM(RELOADS)/SUM(PINS)FROMV$LIBRARYCACHE;

SUM(RELOADS)/SUM(PINS)

.008098188

不算太严重

SQL>SELECTround((B.Value/A.Value)*100,1)hardpaseperc

FROMV$SYSSTATA,

V$SYSSTATB

WHEREA.Statistic#=171

ANDB.Statistic#=172

ANDROWNUM=1;

hardpaseperc

26.5

2.查看保留区使用情况

SQL>SELECTFREE_SPACE,

FREE_COUNT,

REQUEST_FAILURES,

REQUEST_MISSES,

LAST_FAILURE_SIZE

FROMV$SHARED_POOL_RESERVED;

FREE_SPACEFREE_COUNTREQUEST_FAILURESREQUEST_MISSESLAST_FAILURE_SIZE

1048576114604132

最近一次申请共享区失败时该对象需要的共享区大小4132

selectnamefromv$db_object_cachewheresharable_mem=4132;

name

dbms_lob

--dbms_lob正是exp时申请保留区的对象

3.查看导致换页的应用

SQL>select*fromx$ksmlruwhereksmlrsiz>0;

ADDRINDXINST_IDKSMLRCOMKSMLRSIZKSMLRNUMKSMLRHONKSMLROHVKSMLRSES

50001A8801BAMIMA:BamBuffer410064DBMS_DDL402745060730DEB9C

50001ACC11BAMIMA:BamBuffer4108736DBMS_SYS_SQL1909768749730D0838

50001B1021BAMIMA:BamBuffer41121576STANDARD2679492315730D7E20

50001B5431BAMIMA:BamBuffer41241536DBMS_LOB853346312730DA83C

50001B9841BAMIMA:BamBuffer41283456DBMS_UTILITY4041615653730C5FC8

50001BDC51BAMIMA:BamBuffer41323760begin:1:=dbms_lob.getLeng...2942875191730CFFCC

50001C2061stateobjects41841088000

50001C6471librarycache4192488EXU8VEW2469165743730C1C68

50001CA881stateobjects4196160730C0B90

50001CEC91stateobjects421636080730D0838

3.分析各共享池的使用情况

SQL>selectKSPPINM,KSPPSTVL

fromx$ksppi,

x$ksppcv

wherex$ksppi.indx=x$ksppcv.indx

andKSPPINM='_shared_pool_reserved_min_alloc';

KSPPINMKSPPSTVL

_shared_pool_reserved_min_alloc4400--(门值)

我们看到INDX=5,DBMS_LOB造成换页(就是做exp涉及到lob对象处理造成的换页情况),换出

最近未使用的内存,但是换出内存并合并碎片后在共享区仍然没有合适区来存放数据,说明共享

区小和碎片过多，然后根据_shared_pool_reserved_min_alloc的门值来申请保留区,而门值为4400,

所以不符合申请保留区的条件,造成4031错误。我们前面看到保留区全部为空闲状态,所以我们可以

减低门值，使更多申请共享内存比4400小的的对象能申请到保留区，而不造成4031错误。

4.解决办法：

1).增大shared_pool（在不DOWN机的情况下不合适）

2).打patch（在不DOWN机的情况下不合适）

3).减小门值（在不DOWN机的情况下不合适）

因为LAST_FAILURE_SIZE_shared_pool_reserved_min_alloc所以表明没有有效的使用保留区

SQL>altersystemset"_shared_pool_reserved_min_alloc"=4000;

altersystemset"_shared_pool_reserved_min_alloc"=4000

*

ERRORatline1:

ORA-02095:specifiedinitializationparametercannotbemodified

--9i的使用方法altersystemset"_shared_pool_reserved_min_alloc"=4000scope=spfile;

4).使用altersystemflushshared_pool;(不能根本性的解决问题)

5).使用dbms_shared_pool.keep

5.由于数据库不能DOWN机，所以只能选择3)和4)

运行dbmspool.sql

SQL>@/home/oracle/products/8.1.7/rdbms/admin/dbmspool.sql

找出需要keep到共享内存的对象

SQL>selecta.OWNER,

,

a.sharable_mem,

a.kept,

a.EXECUTIONS,

b.address,

b.hash_value

fromv$db_object_cachea,

v$sqlareab

wherea.kept='NO'and

((a.EXECUTIONS>1000

anda.SHARABLE_MEM>50000)

ora.EXECUTIONS>10000)

andSUBSTR(b.sql_text,1,50)=SUBSTR(,1,50);

OWNERNAMESHARABLE_MEMKEPEXECUTIONSADDRESSHASH_VALUE

—

SELECTCOUNT(OBJECT_ID)98292NO10320774814BF81893309624

FROMALL_OBJECTS

WHEREOBJECT_NAME=:b1

ANDOWNER=:b2

STANDARD286632NO13501

DBMS_LOB98292NO103750

DBMS_LOB47536NO2886542

DBMS_LOB11452NO2864757

DBMS_PICKLER10684NO2681194

DBMS_PICKLER5224NO2663860

SQL>executedbms_shared_pool.keep('STANDARD');

SQL>executedbms_shared_pool.keep('74814BF8,1893309624','C');

SQL>executedbms_shared_pool.keep('DBMS_LOB');

SQL>executedbms_shared_pool.keep('DBMS_PICKLER');

SQL>selectOWNER,name,sharable_mem,kept,EXECUTIONSfromv$db_object_cachewherekept='YES'ORDERBYsharable_mem;

SQL>altersystemflushshared_pool;

Systemaltered.

SQL>SELECTPOOL,BYTESFROMV$SGASTATWHERENAME='freememory';

POOLBYTES

sharedpool7742756

largepool614400

javapool32768

[oracle@ali-solutionoracle]$sh/home/ora