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: ORA-4031

Re: ORA-4031

From: <Heidi_Schmidt_at_gillette.com>
Date: Wed, 6 Sep 2000 09:18:13 -0400
Message-Id: <10611.116298@fatcity.com>


We had a similar error but it was ORA-04030 that pointed to system memory being exhausted.
I am assuming you are on a UNIX OS and that you are running perl there. On NT it is much trickier to pinpoint a problem. I don't know of any accurate memory diagnostic tools out there for NT

I went looking at the ulimit for the user on AIX 4.3.3.0 platform The systems admin and I upped the ulimit from 256M for data soft limit to 512M for the hard limit which the soft limit can be raised to That took care of the immediate need.

Then went to explain plan the code. There was the key. Besides trying to take an average on the start and stop date(s), and having 6 million rows for the major table being joined there were several small coding tricks they didn't take advantage of at the start. Once we sent the code through explain plan and saw how much nesting, full table scans, and such we then started to tune their code.

My suggestion is to do both memory tuning of the system file on UNIX and checking of the code.
For the system file (if you have one.. on Solaris and HP-UX there is one) Make sure shmmax and other shared memory segment parameters are tuned for the system.
I heard that Toad is a good tool to use for the database (tuning and maintenance and such)
I am aiming to give it a whirl in the next few weeks. Further aside...
I tried getting SQL Station to work but it is a) intrusive with requiring you to create some sort of repository in the target database b) not user install friendly

Good luck.

     Heidi

                                                                                         
                    Kader Ben                                                            
                    <kaderb_at_yahoo        To:     Multiple recipients of list ORACLE-L    
                    .com>                <ORACLE-L_at_fatcity.com>                          
                    Sent by:             cc:                                             
                    root_at_fatcity.        Subject:     ORA-4031                           
                    com                                                                  
                                                                                         
                                                                                         
                    09/05/00                                                             
                    05:32 PM                                                             
                    Please                                                               
                    respond to                                                           
                    ORACLE-L                                                             
                                                                                         
                                                                                         



Hi friends,
  I have a perl script that load file content into a table, since this morning I am facing with the following error:

FATAL ERROR: (DBD::Oracle::db do failed: ORA-04031: unable to allocate 52 bytes of shared memory ("shared pool","insert into grille values ('...","sql area","strdef : prsstr") (DBD ERROR: OCIStmtExecute) at infinit.pm line 123, <FILE> chunk 1.
)

I have increased the shared_pool-size and parameter in init.ora and I still running the same problem. I don't know what I can do else ;)

This is fragment from my init.ora:

shared_pool_size = 36000000
shared_pool_reserved_size = 160000000
shared_pool_reserved_min_alloc = 8000000
sort_area_size = 2000000

Thank you for your help in advance,

Kader



Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
--
Author: Kader Ben
  INET: kaderb_at_yahoo.com

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
Received on Wed Sep 06 2000 - 08:18:13 CDT

Original text of this message

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