Re: ORA-4031

From: Davide Bellesi <davide.bellesi_at_visto.com>
Date: Thu, 07 Sep 2000 02:06:35 -0700
Message-Id: <10612.116383@fatcity.com>

Hi, (.. i want to apologize for my BAD english)=20 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-----
From: Heidi_Schmidt_at_gillette.com
Sent: Wed, 06 Sep 2000 06:34:38 -0800 To: ORACLE-L_at_fatcity.com
Subject: Re: ORA-4031

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 t= o
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

                                                                         =


               =20

                    Kader Ben                                            =


               =20

                    <kaderb_at_yahoo        To:     Multiple recipients of l=
ist ORACLE-L   =20
                    .com>                <ORACLE-L_at_fatcity.com>          =


               =20

                    Sent by:             cc:                             =


               =20

                    root_at_fatcity.        Subject:     ORA-4031           =


               =20

                    com                                                  =


               =20

                                                                         =


               =20

                                                                         =


               =20

                    09/05/00                                             =


               =20

                    05:32 PM                                             =


               =20

                    Please                                               =


               =20

                    respond to                                           =


               =20

                    ORACLE-L                                             =


               =20

                                                                         =


               =20

                                                                         =


               =20

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 =3D 36000000
shared_pool_reserved_size =3D 160000000
shared_pool_reserved_min_alloc =3D 8000000

sort_area_size =3D 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
also send the HELP command for other information (like subscribing).




--=20
Author:=20
  INET: Heidi_Schmidt_at_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_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
also send the HELP command for other information (like subscribing).



_________________________________________________________________________=

Received on Thu Sep 07 2000 - 04:06:35 CDT