关于ORA-04031得一些资料
阿飞
故相关得资料偏重与9205
从好多资料查都是一些bug,但是也有可以调整一些参数进行调整以减少改错误的发生.
资料来源:metalink
Subject: ora-4031 / Continuos Growth of 'miscellaneous' in v$sgastat
when STATISTICS_LEVEL is set to TYPICAL or ALL
Applies to:
ALL PRODUCT ACCESS - Version:
This problem can occur on any platform.
Symptoms
The memory allocations under "miscellaneous" category in the shared
pool have a continuos growth.
The growth can be seen in v$sgastat overtime. The following query goes
into the PERFSTAT repository to confirm the behavior.
select s.snap_id,to_char(s.SNAP_TIME,'dd-mon hh24:mi') timestamp
,trunc(bytes/1048576) "miscellaneous (in Mb)"
from STATS$SGASTAT g, STATS$SNAPSHOT s
where g.name = 'miscellaneous'
and s.snap_id=g.snap_id
order by snap_id;
SNAP_ID TIMESTAMP miscellaneous (in Mb)
---------- ------------ ---------------------
3487 02-jul 00:25 93
3488 02-jul 02:19 99
3489 02-jul 02:50 99
3490 02-jul 10:04 105
3491 02-jul 10:35 105
3492 02-jul 13:35 106
3493 02-jul 14:09 106
The growth eventually can cause ORA-04031.
Changes
STATISTICS_LEVEL set to TYPICAL or ALL
Cause
Bug 3349324 indicates a possible leak caused by the shared pool
advisors when doing truncates.
and/or
Bug 3519807 Querying V$Segment_Statistics Gets Ora-4031
Solution
set in the init.ora
STATISTICS_LEVEL=BASIC
or
_object_statistics=false
_library_cache_advice=false
References
Bug 3349324 - Memory Leak On Truncate Table
STATISTICS_LEVEL
Bug 3519807 - Querying V$Segment_Statistics Gets Ora-4031
-------------------------------------------------------------------------------------------------------------------------
ora-7445 and ora-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is
used
PURPOSE
-------
This note explains the changes in behaviour introduced in 9.2.0.5
concerning
OPEN_CURSORS and SESSION_CACHED_CURSORS in relation with the PL/SQL
cursor
CACHE and why you might experience ORA-4031 and eventually ORA-7445
SCOPE & APPLICATION
-------------------
Support analysts and DBAs.
ORA-7445 and ORA-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is
used
---------------------------------------------------------------------------
In Oracle versions up to and including 9.2.0.4, the size of the PL/SQL
cursor
cache was controlled by the parameter OPEN_CURSORS. This was usually
set
to a high value with the side effect that the PL/SQL cursor cache was
bigger
than actually needed and using more space in the SGA than required.
This could
lead to ORA-4031 and ORA-7445 due to the shared pool beeing exhausted /
fragmented
needlessly.
In 9.2.0.5 and Oracle 10g, the parameter controlling the size of the
PL/SQL
cursor cache was changed to SESSION_CACHED_CURSORS which already
controlled
the session cursor cache.
Generally the SESSION_CACHED_CURSORS is not set and defaults to 50
which is
adequate for most applications. Now, people might be thinking about
increasing
(or set) SESSION_CACHED_CURSORS to the value of OPEN_CURSORS in
9.2.0.5.
This is generally not a good idea.
The whole idea of the change of the parameter controlling the PL/SQL
cursor
cache to SESSION_CACHED_CURSORS was to avoid having a too big cache due
to
a high setting of OPEN_CURSORS.
Hence, by setting SESSION_CACHED_CURSORS to the value of OPEN_CURSORS,
you are
having 2 caches that big and might get even more ORA-4031/ORA-7445.
Please note that the Patch Set 9.2.0.5 README does document this issue
and
also explains that it will not be necessary for the majority of users
to
modify the value of either of these parameters
RELATED DOCUMENTS
-----------------
Patch Set 9.2.0.5 README
Bug 3150705
-------------------------------------------------------------------------------------------------------------------------
Subject: ora-7445 occurs in kghxfrcnt() after ora-4031 is raised
Oracle Server - Enterprise Edition - Version: 9.2.0.5.0
This problem can occur on any platform.
SymptomsORA-4031 is raised, and during the dump of the ora-4031
diagnostic information an ora-7445 is reported in the alert.log.
Checking the trace file you see ora-4031 diagnostic information being
dumped and then shortly afterward you see an ora-7445 in kghxfrcnt()
with a stack such as the following:-
kghxfrcnt kgldxf kgldmc ksm_4031_dump ksmasg CauseThe ora-7445 is
caused by Bug 3901424
This is a bug from the dump routines for the ora-4031 which occurs
during the library cache dump for the ora-4031 diagnostics. SolutionThe
bug is fixed in 9.2.0.7, 10.1.0.5 and 10.2.0.1...although one-off fixes
are available.
1/ To get the one-off fix go to:-
http://updates.oracle.com/download/3901424.html
2/ for the 9.2.0.7 patchset go to:-
http://updates.oracle.com/download/4163445.html
You will need to login with your Metalink username and password.
The patch will stop the ora-7445 occuring when ora-4031 is raised.
All patches are also available via Metalink under the "Patches &
Updates" section
---------------------------------------------------------------------------------------------------------------------------
Subject: ORA-4031 and DBMS_SHARED_POOL.KEEP remarks and PIND toolkit
overview
PURPOSE
=======
This article contains information about DBMS_SHARED_POOL.KEEP (Keeping)
which can help when dealing with ORA-4031
Anyway we recommend firstly to follow documents:
Note 62143.1 - Understanding and Tuning the Shared Pool
Note 146599.1 - Diagnosing and Resolving Error ORA-04031
and try to reduce pressure put on Library Cache.
It also intends to give some information on PIND toolkit which helps to
resolve some ORA-4031 problems.
PIND toolkit itself is available in
Note 301171.1 Toolkit for dynamic marking of Library Cache objects as
Kept (PIND)
SCOPE & APPLICATION
===================
A. General comments:
------------------------
0.Pin vs Keep
Commonly used word 'pinning' in sense of marking object kept
(unflushable) is confusing/incorrect. In fact pin
(pinning mechanism) is used for maintaining concurrency (as lock) when
acceessing Library Cache objects in the Shared Pool.
1.Contiguous allocations
Since Oracle 7.2 we are not allocating contiguous space in Shared Pool
for loaded big objects.
Rather the object is split into smaller chunks. This dramatically
reduces need for keeping objects.
Note 61623.1 Resolving Shared Pool Fragmentation In Oracle7
2.Bootstrap keeping
There are marked some objects as KEPT by Oracle kernel itself on the
instance startup (bootstrap).
Basic data dictionary tables, its clusters and indexes (i.e. OBJ$,
USER$,...) are marked. In case Java option
is installed also number of java classes is Kept.
@ Bit discussed in Bug:2654664 (KGLHDPKP=flag for hardcoded keep).
Query to show objects marked kept on Bootstrap
@ select KGLNAOWN OWNER, KGLNAOBJ NAME,KGLOBTYP TYPE, to_char(KGLHDFLG
,'Xxxxxxxxxxx') Flags_hexa,
@ decode(bitand(KGLHDFLG,8388608),8388608,'KGLHDKEP
')||decode(bitand(KGLHDFLG,1048576),1048576,'KGLHDPKP') Flags
@ from x$kglob where
decode(bitand(KGLHDFLG,1048576),1048576,'KGLHDPKP')='KGLHDPKP' -- all
bootstrap kept
3.User kept types
We are able to pin following types of objects (Code in parenthesis
means value of argument FLAG of
function DBMS_SHARED_POOL.KEEP).
package/procedure/function (P), sequence (Q), trigger (R), type (T),
java source (JS), java class (JC),
java resource (JR), java shared data (JD), cursor (C)
( $ORACLE_HOME/rdbms/admin/dbmspool.sql )
4.Cursor specifics
Only Cursors of above noted object types are not permanently stored in
the database. Hence cursor has to be firstly
created (parsed) before can be marked as kept.
Note 90997.1 DBMS_SHARED_POOL.KEEP on cursors results in ORA-6570
5.RAC and DBMS_SHARED_POOL
SGA is private for each instance and hence keeping object on one
instance does not affect another.
This implies the object has to be kept using procedure
DBMS_SHARED_POOL.KEEP on all instances.
6.ORA-4031 and Kept objects
Marking object as Kept causes this object cannot be flushed out of the
Shared Pool. ORA-4031 says we didn't find
enough space even if flushed everything what could be flushed. Thus
marking 100% of memory as Kept will cause
ORA-4031 when loading new piece of SQL (i.e. recursive SQL).
7.KEPT object is reloaded
V$DB_OBJECT_CACHE.LOADS is >1 even if KEPT=YES. It may happen that
Library Cache object gets invalidated
and hence has to be reloaded (i.e. drop of referenced object). It's
also possible object has been reloaded
before it has been marked as KEPT. More complicated case is described
in
Note 175659.1 Kept Packages Are Showing Reloads
8.Timing of keeping
Objects which should be Kept into Shared Pool should be marked as soon
as possible after the instance is started.
This should be done before the memory gets fragmented.
B. PIND toolkit:
--------------------
can be downloaded from
Note 301171.1 Toolkit for dynamic marking of Library Cache objects as
Kept (PIND)
Tool for automated keeping/taking snapshot of objects upon
startup/shutdown.
Snapshot of most used objects is taken using shutdown trigger and found
objects pinned using startup trigger.
Cursors are pinned during instance lifetime via DBMS_JOB facility.
Mechanism protects against false snapshots and against marking
significant part of Shared Pool as kept.
Standard DBMS_SHARED_POOL package is utilized for keeping. Basic idea
is based on Note:101627.1. Tested on 9.2, 10.1.
1.Structure:
- Snapshot/Keep functions are called from triggers
PIND_ON_START/PIND_ON_SHUT
- Functions and procedures are embedded into package PIND_OBJ.
- List of object to be kept is (Snapshot) is captured using procedure
SNAP. The list is stored in table PIND_KEPT_OBJ.
Every instance has its own list of objects.
- Objects (except cursors) are marked as kept via procedure KEEP
- Cursors are pinned using procedure SCHED_KEEPC which submits job to
call itself after certain amount of time.
This should ensure SQL cursors are pinned after SQL has been parsed
when application is running for some time.
- Enhanced version of view V$DB_OBJECT_CACHE is used by PIND
(GPIND$DB_OBJECT_CACHE, GPIND$DB_OBJECT_CACHE ).
Added columns allows easily identify and keep SQL statements.
2.Configuration:
- PIND_OBJ.KEEP (P_MAX_PERCT_KEPT)
There is treshold controlling total amount of memory marked as kept to
prevent (6). This limit is expressed
as percentage of really used shared_pool_size. Default value is 5%
(~10-20% of Library Cache) and can be changed
via value of parameter P_MAX_PERCT_KEPT of function PIND_OBJ.KEEP
(change trigger definition).
- PIND_OBJ.SCHED_KEEPC (P_ITER_MAX, P_INTERVAL_HRS)
Procedure used for keeping of cursors is called multiple times with
specified interval.
Default is 4 executions (P_ITER_MAX) with 6 hours interval. Change
following line in trigger definition:
pind_obj.sched_keepc(1,4,6,st_time);
3.Usage:
- install toolkit using script PIND_install.sql. This does trial run of
SNAP and KEEP procedures to test functionality.
- verify there are no errors during the installation
- messages are written into alert.log upon startup and shutdown so
there can be seen how long Snap/Keep procedures took
- follow (b) if you need to change some configuration parameters
- triggers will be fired on each particular RAC instance (as indicated
in point 5)
- to debug the toolkit run 'set serverout on size 1000000' and call
desired procedure from command line
RELATED DOCUMENTS
=================
Note 62143.1 - Understanding and Tuning the Shared Pool
Note 146599.1 - Diagnosing and Resolving Error ORA-04031
Note 61623.1 - Resolving Shared Pool Fragmentation In Oracle7
Note 90997.1 - DBMS_SHARED_POOL.KEEP on cursors results in ORA-6570
Note 175659.1 - Kept Packages Are Showing Reloads
当然最好的方法是升级补丁.另外可以调整一些参数以减少改错误的发生.