RE: Strange 4031 issue.

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <lawrence.wolfson@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 2 Nov 2006 16:47:46 -0500

Hi Larry,

When you get this error, what's the full text of the error message?
Does it say something about "session param values"?

If so, could it be bug #3277525?

-Mark 


--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfson Larry -
lwolfs
Sent: Thursday, November 02, 2006 4:37 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Strange 4031 issue.

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
---------------------------- ----------------------------------------
------------ ---
JAVA CLASS                   sun/io/CharacterEncoding
109,204 YES
PACKAGE BODY                 S2P_INV_CTL
109,978 NO
PACKAGE                      S2P_C
116,318 NO
PACKAGE BODY                 S2P_ORDER_CTL
116,835 NO
PACKAGE                      S2P_RF_UTIL
117,385 NO
JAVA CLASS                   java/util/TimeZoneData
121,338 YES
PACKAGE BODY                 S2P_SKUXLOC
122,842 NO
PACKAGE BODY                 S2P_TASKHDR
123,554 NO
PACKAGE BODY                 S2P_RF_PICK
126,569 NO
CURSOR                       SELECT *   FROM ORDERS  WHERE ORDERKEY =
127,326 NO
                              :b1
PACKAGE BODY                 S2P_RF_UTIL
127,705 NO
PACKAGE BODY                 S2P_TASKDTL
128,138 NO
JAVA CLASS                   /1adcad9a_OracleStatement
132,077 YES
PACKAGE BODY                 S2P_LOC
137,626 NO
PACKAGE BODY                 S2P_ORDERS
150,408 NO
PACKAGE BODY                 S2P_TASK
157,804 NO
CURSOR                       SELECT *   FROM TASKHDR  WHERE TASKHDR.D
158,892 NO
                             EVICE = :b1  AND TASKHDR.STATE = :b2  AN
                             D TASKHDR.STS = :b3 ORDER BY TASKHDR.PIC
                             KEDUPSEQUENCE DESC
PACKAGE BODY                 S2P_STORAGE
161,386 NO
PACKAGE                      S2P_RF_TASK
163,922 NO
PACKAGE BODY                 S2P_RF_AUTO
165,138 NO
PACKAGE BODY                 S2P_TASKTRN
166,442 NO
PACKAGE BODY                 S2P_CONT_INV_CTL
184,500 NO
PACKAGE BODY                 S2P_INVENTORY
188,598 NO
PACKAGE BODY                 S2P_RF_TASK
251,338 NO
CURSOR                       table_1_0_141_0_0_0
353,117 NO
PACKAGE BODY                 S2P_TASKTRN_CTL
359,722 NO
PACKAGE                      STANDARD
438,556 NO
 
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.
************************************************************************
****
--
//www.freelists.org/webpage/oracle-l


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


Other related posts: