RE: ORA-04031
- From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 Jul 2004 12:58:29 -0500
Oh, sure bring me in as an example.....Larry.
Yes, this does get you some space back but in my case it just bought us some
extra time before we got more 4031 errors
For me the one PROBLEM was cursor_spaced_for_time was set to true.
I also had a lot of leaks happening:
1) DBMS_OUTPUT was put into alot of code (even in a large cursor loop) and
the buffer size was not adjusted causing alot of leaks
you can do this but it has to be set up correctly.
2) leaks from a heterogenous link to sql server and updates across a dblink
- most of this cured by fixing cursor_spaced_for_time.
3) .Net with C# not using dispose for garbage collection. (dispose also
cures cursor problems). .Net in web services seems
to consume ALOT of resources.
3) also check your session_cached_cursor variable. might be set too high.
You might want to do a search on 4031 in the archives and follow all the
help I got.
I still have some sporatic leak issues with the database. But it is very
hard trap the sql to the leak.
Check also to see if cursors are being closed.
Kathy
-----Original Message-----
From: Wolfson Larry - lwolfs [mailto:lawrence.wolfson@xxxxxxxxxx]
Sent: Wednesday, July 14, 2004 10:41 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: ORA-04031
Paula,
Below are some comments from an earlier post on 4031.
You may find that you don't really need the
shared_pool_reserved_size.
Run the script below to see what kind of activity there really is
and decide if it's worth it.
Could you let us know what you find and also what are your
shared_pool_reserved_size and shared_pool_size parameters.
Thanks
Larry
Kathy,
Ummmmmm, I think you missed my point. What I thought I said was we
found the shared_pool_RESERVE wasn't getting used at all or hardly at all.
So we changed it from the default to 1M.
shared_pool_reserved_size string 1024000
shared_pool_size string 514400000
I don't know how long your DB has been up but it looks like you had
85 failures and you still had 25M you never used.
The problem is Only allocations larger than
SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved
list if a chunk of memory of sufficient size is not found on the shared
pool's free lists.
Your query below says you had 25M free for something really big to
stick in the SharedPoolReserve but 4132 was smaller then the
SHARED_POOL_RESERVED_POOL_MIN_ALLOC so you got a 4031 when you had 25M free.
I didn't say anything about changing the SHARED_POOL size.
It looks like you found the problem with .net.
What I'm suggesting is you have 25M you might want to put to better
use somewhere else.
Larry
From Tom Kyte:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:773961292
7578
With 8.0, the large pool came along -- this obviated the need for the
"reserved"
portion of the shared pool. We moved those big allocations from the shared
pool
into the large pool. So, the reserved is not needed.
For the same reason, pinning is pretty much "not needed" either. It makes
some
people feel better but in 99.999% of the cases (eg: system is running
normally,
you use bind variables) pinning is pretty much a waste of programming effort
on
your part as well.
I would suggest not using either really. Ignore their existence.
Subject: RE: 4031 - errors
Kathy, hi how are you?
Got in here a couple days late but we had a sever problem with 4031s
and after looking on metalink I used this script to see how busy the
shared_pool_reserve was.
SPOOL $DBS/Sharedpoolreserved.$ORACLE_SID
COL name FOR A05
COL REQUEST_FAILURES FOR 999,999 HEA "REQUEST|FAILURES"
COL LAST_FAILURE_SIZE FOR 999,999,999 HEA "LAST |FAILURE |SIZE "
select name
,free_space
,avg_free_size
,used_space
,avg_used_size
,request_failures
,last_failure_size
,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24 MI SS') DATE_AN_TIME
from db
,v$shared_pool_reserved
;
SPOOL OFF
name is just the our INTERNAL DB name like CHICago, JACKsonville,
DALLas etc. to tell one PROD from another.
We had a lot of free space and minimized the SPR to give more memory
to SP. We didn't have any more problems
Larry
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
Paula_Stankus@xxxxxxxxxxxxxxx
Sent: Wednesday, July 14, 2004 9:28 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: ORA-04031
Platform:
Oracle 9.2.0.5
Solaris 2.9
ArcSDE 8.3
I am running a DSS - geodatabase with 30 concurrent users. I am getting =
ORA-04031 errors.
I have verified that last_failure_size > shared_pool_reserved_min_alloc. =
According to Note: 146599.1 it states that I should increase the =
hidden parameter "_shared_pool_reserved_min_alloc" to lower the number =
of objects being cached. =20
It is currently set at 4400 - how much lower would I need to go????
It also states I should consider increasing the =
shared_pool_reserved_size and shared_pool_size but these parameters seem =
adequate to me:
40M for shared_pool_size
4M for shared_pool_reserved_size
I have gone through NOTE: 1012046.6 "Calculating Shared Pool size" and =
based on that my shared_pool_size is more than adequate.
How can I more specifically size the shared pool, shared reserved pool =
as I know that if I size too large then I can start incurring overhead.
**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
This transmission contains information solely for intended recipient and may
be privileged, confidential and/or otherwise protect from disclosure. If
you are not the intended recipient, please contact the sender and delete all
copies of this transmission. This message and/or the materials contained
herein are not an offer to sell, or a solicitation of an offer to buy, any
securities or other instruments. The information has been obtained or
derived from sources believed by us to be reliable, but we do not represent
that it is accurate or complete. Any opinions or estimates contained in
this information constitute our judgment as of this date and are subject to
change without notice. Any information you share with us will be used in
the operation of our business, and we do not request and do not want any
material, nonpublic information. Absent an express prior written agreement,
we are not agreeing to treat any information confidentially and will use any
and all information and reserve the right to publish or disclose any
information you share with us.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
