RE: ora-4031- Advice on what to look at next time

Stephane & Mogens,

Thanks for taking the time to comment.
Mogens - The 4031 event was not set when the problem occurred so eventually the database was restarted to set it (we could not reliably produce the problem in any particular session and the situation was getting rather bad for our users). If / when the next time the problem happens then I'll have a level 3 errorstack to provide to support for interpretation.

Stephane - You comment we may have too much pinned and you're probably right there. Currently our pinning is done by a startup trigger than pins packages, procedures, functions and triggers (this is what I meant by "objects") and I took the "simple to code and ensure it pins everything that could possibly be used by our application in Forms or PLSQL packages). I'm now thinking of altering to pin all the application objects (as before) but only those other objects that are referenced by our application (as from dba_dependencies).

You mention querying v$rowcache - I get the following:   1* select parameter,count,usage,gets,getmisses,modifications,flushes from v$rowcache 09:37:17 SQL> /

PARAMETER                 COUNT USAGE     GETS gmisses  mods FLUSHES
------------------------- ----- ----- -------- ------- ----- -------
dc_free_extents              40    16     7565     197   445     269
dc_used_extents              27     9      192     103   192     192
dc_segments                1048  1031   785209    1032   224     155
dc_tablespaces               14    11    13825      11     0       0
dc_tablespace_quotas          5     3      201       3   201     132
dc_files                     32    24      624      24     0       0
dc_users                    117   110   358671     110     0       0
dc_rollback_segments         15    10     9877       9    25      24
dc_objects                 2371  2370   311300    2319    98      46
dc_global_oids                1     0        0       0     0       0
dc_constraints                1     0        0       0     0       0
dc_object_ids               935   932  3607186     911    53       1
dc_synonyms                 246   241    74577     241     0       0
dc_sequences                 28    25     7393      25  6646    6646
dc_usernames                113   101   121515     101     0       0
dc_database_links             1     0        0       0     0       0
dc_histogram_defs          2666  2662   492074    2669   224     224
dc_outlines                   1     0        0       0     0       0
dc_profiles                   2     1     4014       1     0       0
ifs_acl_cache_entries         1     0        0       0     0       0
dc_users                      1     0        0       0     0       0
dc_sequence_grants           39    38      718      38     0       0
dc_histogram_data             1     0        0       0     0       0
dc_histogram_data_values      1     0        0       0     0       0
dc_user_grants              139   107   130351     107     0       0

25 rows selected.

All our flushes are on sequences - which aren't kept. Is there anything else you can detect from this output? Thanks,
Bruce Reardon

-----Original Message-----
Sent: Monday, 22 April 2002 20:03

>----- Original Message -----
>From: "Reardon, Bruce (CALBBAY)"
>Sent: Sun, 21 Apr 2002 22:23:18
>
>Our production instance started getting ora-4031
>errors around 6pm on Friday 19th.=0D
>I was called by our users around 9am on Sunday 21st
>and problem persisted and I decided to restart the instance
>around 11:30am Sunday 21st.
>What I would like is some advice on what other
>information I should collect=
> next and advice on any settings that may need
>altering.

Bruce,

  You mention that you have all of your application objects (I presume you mean 'packages') and all Oracle standard stuff kept in shared memory. AFAIK it's precisely by pinning packages in memory that you avoid the problem you have. However, pinning EVERYTHING is perhaps a bit too much - you must pin only the biggest chunks to limit fragmentation. If I were you, what I'd check first would probably be V$ROWCACHE to check what is anchored and what is following the ebb, try to spot what causes the problem (what is reloaded most often?) and either pin it too or possibly unkeep some not-so-necessary stuff to get some breathing space.

Stephane Faroult
Oriole Corporation
Performance Tools & Free Scripts
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs   INET: sfaroult_at_oriolecorp.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).