Re: ORA-4031
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <tracy.rahmlow@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 May 2006 22:27:03 +0800
Are those ORA-4031's accompanied with "large pool" or "shared pool" parameter
in alert log?
If large pool, set the large_pool_size to higher value, despite the autotuning
feature.
If shared pool, then please run this as SYS. In 10gR1 it should show you into
how many shared subpools your SP has been divided to.
select count(*) from x$kghlu where kghlushrpool = 1;
If it's more than 1 then you might be hitting a bug/issue with shared subpools
and it would be advisable to set _kghdsidx_count=1 and bounce the instance.
That seems to be Oracle support's latest standard advice whenever having shared
pool related issues...
(btw, in 10gR2 the x$kghlu shows 4X more entries as shared pool has been split
up even further - protected by the same amount of latches though..)
Tanel.
----- Original Message -----
From: Tracy Rahmlow
To: oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, May 09, 2006 2:28 AM
Subject: ORA-4031
This weekend we upgraded from 8.1.7.4 32-bit to 10.1.0.5 64-bit (AIX 5.2) and
we are experiencing a large number of 4031 errors(these were extremely rare
under 8). We have contacted Oracle support as a sev 1 issue, but I thought I
would drop a note to see if anyone has experienced anything like this while we
wait. Under 8 our shared pool was approximately 250m. We initially set the
shared pool to 1.2g under 10 and that was not enough. In the interim, we
decided to increase the shared pool to 3g to see if we can buy some time while
we continue to wait (and that has been successful so far). We do have some
literal sql and some opportunities for improvement, but I would think we should
experience "somewhat" similar behavior in terms of memory usage with the
upgrade vs. the prior version. The amount of memory seems very excessive to
me. In addition, we are not using the memory autotuning featur e. Anybody
know of a bug related to excessive shared pool usage or any other thoughts?
Thanks
__________________________________________________
Tracy Rahmlow | Lead Database Analyst
Information Systems
Ameriprise Auto & Home Insurance
AMEX Assurance Company
IDS Property Casualty Insurance Company
3500 Packerland Drive | De Pere, WI 54115-9070
Office: 920.330.5164 | Fax: 920.330.5350
tracy.rahmlow@xxxxxxxx
ameriprise.com/autohome
Ameriprise made the following
annotations on 05/08/06, 11:29:55
------------------------------------------------------------------------------
******************************************************************************
"This message and any attachments are solely for the intended recipient and
may contain confidential or privileged information. If you are not the intended
recipient, any disclosure, copying, use, or distribution of the information
included in this message and any attachments is prohibited. If you have
received this communication in error, please notify us by reply e-mail and
immediately and permanently delete this message and any attachments. Thank you."
******************************************************************************
==============================================================================
Other related posts:
