4031 issue

hwqhx

数据库报错：  ORA-00603: ORACLE server session terminated by fatal error  ORA-00604: error occurred at recursive SQL level 1  ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select name,online$,contents...","sga heap(1,0)","kglsim o  bject batch")

想请问一下，在执行完alter system flush shared_pool之后，为什么执行相关语句还是报同样的错？

谢谢 ！

Maclean Liu(刘相兵

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump heapdump 536870914;
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_7796.trc
SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


在 heapdump 中会有以下信息：

Total reserved free space   =  2557736

UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Unpinned space     =        0  rcr=0 trn=0

PERMANENT CHUNKS:
  Chunk        09e800058 sz=       80    perm      "perm           "  alo=80
Permanent space    =       80

Freeable 和 UNPINNED RECREATABLE CHUNKS 可以被flush out

If objects are used only intermittently, they may be flushed out of the shared pool. Some recreatable objects are very expensive to recreate. Sometimes it is undesirable to flush freeable objects like sequences. It is possible to mark valuable objects to prevent them from being flushed out.

Freeable: The chunk of space can be freed by calling kghfre(). Recreatable: The chunk of space is recreatable. This implies that it is also freeable. A recreatable chunk of space may be explicitly unpinned by the client layer.

Maclean Liu(刘相兵

ODM Finding:

Chunk types:

Normal (freeable) chunks - These chunks are allocated in such a way that the user can explicitly free
the chunk once they have finished with the memory.

Free chunks - These chunks are free and available for reuse should a request come into the pool for
this chunk size or smaller.

Recreatable chunks - This is a special form of "freeable" memory.  These chunks are placed on an
LRU list when they are unpinned.   If memory is needed, we go to the LRU list and free "recreatable"
memory that hasn't been used for a while.

Permanent chunks - These chunks can be allocated in different ways.   Some chunks are allocated
and will remain in use for the "life" of the instance.   Some "permanent" chunks are allocated but can
be used over and over again internally as they are available.

hwqhx

好

想问一下 ：flush shared_pool不能释放 perm chunk和 pin chunk 这个有文档介绍吗？

Maclean Liu(刘相兵

flush shared_pool不能释放 perm chunk和 pin chunk ， 如果共享池碎片过多 那么刷新并不能解决ORA-4031 ，

建议你现在做一个 oradebug dump heapdump 536870914; 然后上传trace

hwqhx

这个heapdump 没有做，现在想问一下这种执行完alter system flush shared_pool之后，执行相关语句还是报同样的错 可以
会是什么原因？

Maclean Liu(刘相兵

action plan:

上传4031 相关的trace , trace路径可以在alert.log 中找到

oradebug setmypid;
oradebug unlimit;
oradebug dump heapdump 536870914;
oradebug tracefile_name;

上传以上生成的trace