11GR2 Shared_pool KGLHD 占用大量memory且无法释放

显示全部楼层 · 2008-3-31 15:43:34

http://www.itpub.net/thread-1458369-1-2.html
原讨论贴见上。
版本11.2.0.2 @ RHEL4.8 X86_64
V$SGASTAT
shared pool,KGLHD,2738019744shared pool,free memory,882513336shared pool,KGLH0,588651896
shared pool,SQLA,520359200
shared pool,Checkpoint queue,67117056
shared pool,kglsim object batch,48531840
shared pool,KKSSP,46507096
shared pool,dbwriter coalesce buffer,33587200
shared pool,ASH buffers,33554432

Free虽然很大，但几乎碎片化很重，常发生ORA-04031。
跑类似应用的其他DB KGLHD通常只有100M不到，这台有2G多还在不断吃Memory. 也无法 shrink。
这个KGLHD 查下来似乎是library cache handle, 但handle的size>1/2 size of shared pool 很不正常了，感觉是很多东西无法释放。

千问 · 2008-3-31 15:43:34

学习了

千问 · 2008-3-31 15:43:34

May be due to
Bug 12590856: ORA-4031 WITH LARGE KGLHD AND CCUR ALLOCATION CAUSED DATABASE HANG.
I don't know how this is triggered. Do you have too many PL/SQL procedures/functions/packages? Check this:
select count(*), kglhdnsp from x$kglob group by kglhdnsp order by 1;
Common namespace codes can be found in $ORACLE_HOME/rdbms/admin/catblock.sql.
The best way to solve the problem is to work with Oracle support.
Yong Huang

千问 · 2008-3-31 15:43:34

Thanks.
Yes, we have lots of PL/SQL Procedure/PKGs
This Tuesday night I worked until 4:00 AM and finally find the root cause.
It isoutline.
After I shutdown this DB, I saw a strange memory status: used remain 4599436
free

total used free sharedbuffers cached
Mem:164349764599436 11835540
0 4153843747188
-/+ buffers/cache: 436864 15998112
Swap:81931404688192672

ipcs
------ Shared Memory Segments --------
keyshmidownerpermsbytesnattch status
------ Semaphore Arrays --------
keysemidownerpermsnsems
------ Message Queues --------
keymsqidownerpermsused-bytes messages
So I decided to resart server.
After DB startup, I find the KGLHD is all right~
But when I enabled outline, it started growing qiuckly.
then I disabled outline, it stopped growing, but the memory which had been comsumed can not be released.

千问 · 2008-3-31 15:43:34

Looks like that bug matches your case precisely. Hopefully Oracle can provide a patch soon.
Yong Huang

千问 · 2008-3-31 15:43:34

Karsus 发表于 2011-7-21 16:13
Thanks.Yes, we have lots of PL/SQL Procedure/PKGsThis Tuesday night I worked until 4:00 AM and final ...
请问楼主，是怎么禁用和开启outline的呢？通过“_plan_outline_data”参数吗？时间这么久了，也不知道楼主是否还能看到和回复，请大家知道的也回复下。
还有YONGHUANG提到的那个Bug 12590856，请知道的也回复下。
谢谢各位。

千问 · 2008-3-31 15:43:34

I think you can do:
alter system set USE_STORED_OUTLINES = false;
Parameter _plan_outline_data is about showing the outline section when you run explain plan.
I haven't worked on outlines for a long time. It's deprecated anyway.
Bug 12590856 doesn't show any workaround or Oracle version in which the bug has been fixed. But according to the associated Bug 13250244, if you don't use outlines, or use it but you explicitly set _kghdsidx_count to 1 (i.e. force using one monolithic shared pool without subpools), the problem doesn't happen.