Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Strange 4031 issue.

Re: Strange 4031 issue.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 2 Nov 2006 21:52:15 -0000
Message-ID: <005801c6fec9$292d8b10$0200a8c0@Primary>

The amount of memory free is nearly irrelevant if you want to allocate a single large chunk. It is common for the heap to end up as a very large collection of very small pieces, and if a lot of those pieces are pinned in some way, it may not be possible to free enough adjacent pieces to produce a single chunk large enough.

27K is about the size of the 'session parameters' on some platforms, so if you keep seeing the same size chunk causing a failure then perhaps that's what it is. You could try increasing your reserved_shared_pool so that it is large enough to hold sessions * 27KB plus a few dozen MB.

Cross-check you timestamps on the traces with the large amounts of free memory. A hunt for free memory can clear a lot of stuff from the shared pool, and it can take some time to reload. Do you any of the reports of large percentages of free memory show up a few minutes after a trace file is dumped ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

9.2.0.6  

    Getting multiple 4031s on SQLLDR processes. Shows up as
-rw-r--r-- 1 dba 14755 Oct 31 14:08 p_j013_834.trc
-rw-r--r-- 1 dba 14880 Oct 31 14:06 p_j004_29359.trc
-rw-r--r-- 1 dba 14887 Oct 31 13:58 p_j008_23565.trc
-rw-r--r-- 1 dba 14763 Oct 31 13:56 p_j000_22346.trc
-rw-r--r-- 1 dba 14764 Oct 31 13:53 p_j011_19931.trc
-rw-r--r-- 1 dba 14766 Oct 31 13:51 p_j005_18595.trc
 

    like it came from dba_jobs.
We've been monitoring SP every 15 minutes and it looks like there's generally lots of memory available.  

DB   TIME                 Shared Pool Size       Free Bytes Percent Free

---- -------------------- ---------------- ---------------- ------------
XXXX 25-OCT-2006 07:15:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 07:30:00 167,772,160 8,299,488 4.9468803 XXXX 25-OCT-2006 07:30:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 07:45:00 167,772,160 14,386,336 8.5749244 XXXX 25-OCT-2006 07:45:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 08:00:00 167,772,160 12,982,504 7.7381753 XXXX 25-OCT-2006 08:00:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 08:15:00 167,772,160 25,204,720 15.0231838 XXXX 25-OCT-2006 08:15:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 08:30:00 167,772,160 8,200,736 4.8880195 XXXX 25-OCT-2006 08:30:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 08:45:00 167,772,160 13,062,288 7.7857303 XXXX 25-OCT-2006 08:45:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 09:00:00 167,772,160 10,246,144 6.1071777 XXXX 25-OCT-2006 09:00:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 09:15:00 167,772,160 9,018,072 5.3751897 XXXX 25-OCT-2006 09:15:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 09:30:00 167,772,160 28,939,400 17.2492266 XXXX 25-OCT-2006 09:30:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 09:45:00 167,772,160 21,258,496 12.671051 XXXX 25-OCT-2006 09:45:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 10:00:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 10:00:00 167,772,160 52,412,056 31.2400198 XXXX 25-OCT-2006 10:15:00 167,772,160 28,882,048 17.2150421 XXXX 25-OCT-2006 10:15:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 10:30:00 167,772,160 15,986,504 9.5286989 XXXX 25-OCT-2006 10:30:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 10:45:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 10:45:00 167,772,160 51,772,144 30.8586025 XXXX 25-OCT-2006 11:00:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 11:00:00 167,772,160 34,621,608 20.6360865 XXXX 25-OCT-2006 11:15:00 167,772,160 8,650,376 5.1560258 ?? XXXX 25-OCT-2006 11:15:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 11:30:00 167,772,160 9,714,240 5.7901382 ?? XXXX 25-OCT-2006 11:30:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 11:45:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 11:45:00 167,772,160 57,708,392 34.3968821 XXXX 25-OCT-2006 12:00:00 167,772,160 28,971,008 17.2680664 XXXX 25-OCT-2006 12:00:00 167,772,160 37,005,832 22.0571947

  Vendor keeps saying nothing special happens at times we experience 4031s or dips in available memory  

I summarize what's in SP and I don't see anything really big  

TYPE                         NAME

MEMORY KEPT
---------------------------- ----------------------------------------

Then we get 4031s on sqlplus jobs, but connected users don't see this. OOPS! Actually we did see ONE 4031 from connected user (out of 330) at this time.  

And same size every time:
ORA-04031: unable to allocate 27512 bytes of shared memory  

We're tracking all errors:
create or replace trigger log_errors_trig after servererror on database
declare

var_user     varchar2(30);
var_osuser   varchar2(30);
var_machine  varchar2(64);
var_process  varchar2(09);
var_program  varchar2(48);
var_sqltext  varchar2(4000);
sql_text     ora_name_list_t;
n            number;

begin  

Saw Karen Morton suggested log_buffer might be too small. It's 512K here, but haven't increased it.  

Did see someone complain about SP stress when running sqlldr in 8.1.5 Is there a parm that can cause problem?  

Saw someting about perfstat reporting miscellanrous growth over time, but don't have perfstat installed here (yet).  

I'll see if I can get script from vendor.  

  Any ideas?    

    Thanks
    Larry      



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 resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.



--

http://www.freelists.org/webpage/oracle-l

--

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.13.22/512 - Release Date: 01/11/2006

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 02 2006 - 15:52:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US