Message-Id: <10612.116386@fatcity.com> From: "Mark Leith" Date: Thu, 7 Sep 2000 11:46:34 +0100 Subject: RE: ORA-4031 If that is "BAD" English, I should have gotten an A in my GCSE exam:) Your English is fine my friend. -----Original Message----- From: root@fatcity.com [mailto:root@fatcity.com]On Behalf Of Davide Bellesi Sent: 07 September 2000 12:05 To: Multiple recipients of list ORACLE-L Subject: Re: ORA-4031 Hi, (.. i want to apologize for my BAD english) have you cheked the fragmentation of your shared pool ?? Are there hundreds of concurrent users not using bind variables ?? :) I think you've already checked that, so : I faced with this kind of error during a bulk insert on a datawarehouse (Oracle 8.1.5, HP-UX ) My "little" INSERT used 300M in the shared pool. After doubling the shared pool, my 'little' INSERT used 600 M. It seemed to be a BUG ..... maybe platform specific I can ask to Oracle if this fits you requests Hope this helps Davide -----Original Message----- Sent: Wed, 06 Sep 2000 06:34:38 -0800 To: ORACLE-L@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 Sent by: cc: root@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, 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@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@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 also send the HELP command for other information (like subscribing). -- Author: INET: Heidi_Schmidt@gillette.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@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 also send the HELP command for other information (like subscribing). ___________________________________________________________________________ Visit http://www.visto.com/info, your free web-based communications center. Visto.com. Life on the Dot. -- Author: Davide Bellesi INET: davide.bellesi@visto.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@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L