A surprise question about the shared pool

显示全部楼层 · 2005-10-30 17:05:33

Error:
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN
DBMS_OUTPUT.ENABLE(100...","PL/SQL MPCODE","BAMIMA: Bam Buffer&quot

ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN
DBMS_OUTPUT.GET_LINES(...","PL/SQL MPCODE","BAMIMA: Bam Buffer&quot

ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN
DBMS_APPLICATION_INFO....","PL/SQL MPCODE","BAMIMA: Bam Buffer&quot

Version: (MTS)
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
Shared pool info:
POOL NAME
MBytes
------------ ------------ ----------
shared poolfree memory87.4657211 (total is 230M)
large pool free memory128.500313
java poolfree memory.03125
Event 1:
Just for any simple statement, it would spend more shared pool, such as :
SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy') FROM DUAL

CURSOR
NO340456
it use about 340k shared pool,
Event 2:
alter system flush shared pool;
for this alter command, can not release the shared pool, but before it is Ok.
Why?

千问 · 2005-10-30 17:05:33

select count(*) from v$open_cursor是多少……

千问 · 2005-10-30 17:05:33

Thanks,
Now is for 18289;

千问 · 2005-10-30 17:05:33

Thanks for any helping.

千问 · 2005-10-30 17:05:33

盯住一些大的包看看是否可以改善..
还有程序使用绑定变量可以一定程度减少这个问题的出现..

千问 · 2005-10-30 17:05:33

Yes, I think now the key issue is Why such as these simple it would spend such more shared pool space? and Why the alter system can not release the shared pool? Bu it is ok normally. Thanks.

千问 · 2005-10-30 17:05:33

最初由 thinkless 发布
[B]Thanks,
Now is for 18289; [/B]

Here is the problem……
Check your application.
Close cursor in application……

If must keep cursoropen in application, please control the total number of opening cursors , or you need toincrease shared_pool_size.
No other choise.
The shared pool space will not release when the space was occupy by OPENING cursors…… 。Flush shared pool will not help when many cursors is opening……

千问 · 2005-10-30 17:05:33

Thanks rollingpig, you give me a useful hint.
But normally, the total opened cursor about 25000, and the sessions about 600, and the parameter open_cursors is 600.
Anyway, this is a helpful msg and can do any explanation on it, thanks.

千问 · 2005-10-30 17:05:33

open_cursors specifies the maximum number of open cursors a session can have at once

千问 · 2005-10-30 17:05:33

yes, chj733, you are right, so we just 50 sessions, still not exceed the maximun limitation.