| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-4031
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C018CB.E06A8E62
Content-Type: text/plain;
charset="iso-8859-1"
Hi,
I have a similar problem when inserting rows with the sqlldr using direct
path. The tables have bitmap indexes. The files to load have different
number of rows (1000000, 4000000)
I start the process, I control it, and when I see (using the top command)
that the process allocate about 330M it crash.
I opened a tar, I sent the files and the table structure and finally Oracle
Support could reproduce the error and it is a bug.
The workarounds are:
- reduce the size of sort_area_size parameter (512K) - drop the bitmaps indexes and recreate after. - use conventional path
With the first option I could load tables that have about 12 bitmap indexes, but it didn't work for tables with 40 bitmap indexes.
The server is digital unix, SO vers. 4.0F, oracle 8.0.6.1.0 (with 8.0.5.2.1 it happend too) and the application is a datawarehouse.
Hope this help
Mariana.
PD: sorry about my english
-----Mensaje original-----
De: Mark Leith [mailto:mark_at_cool-tools.co.uk]
Enviado el: Jueves 7 de Septiembre de 2000 08:56
Para: Multiple recipients of list ORACLE-L
Asunto: 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-----
Bellesi
Sent: 07 September 2000 12:05
To: Multiple recipients of list ORACLE-L
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_at_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 = 8000000sort_area_size = 2000000
Thank you for your help in advance,
Kader
-- 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-LReceived on Thu Sep 07 2000 - 08:02:28 CDT
(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_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). ___________________________________________________________________________ Visit http://www.visto.com/info, your free web-based communications center. Visto.com. Life on the Dot. -- Author: Davide Bellesi INET: davide.bellesi_at_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_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). -- Author: Mark Leith INET: mark_at_cool-tools.co.uk 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). ------_=_NextPart_001_01C018CB.E06A8E62 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: ORA-4031</TITLE> </HEAD> <BODY> <BR> <P><FONT SIZE=3D2>Hi,</FONT> </P> <P><FONT SIZE=3D2>I have a similar problem when inserting rows with the = sqlldr using direct path. The tables have bitmap indexes. The files to = load have different number of rows (1000000, 4000000)</FONT></P> <P><FONT SIZE=3D2>I start the process, I control it, and when I see =
(using the top command) that the process allocate about 330M it =
crash.</FONT></P> <P><FONT SIZE=3D2>I opened a tar, I sent the files and the table = structure and finally Oracle Support could reproduce the error and it = is a bug.</FONT></P> <P><FONT SIZE=3D2>The workarounds are:</FONT> <BR><FONT SIZE=3D2>- reduce the size of sort_area_size parameter =
(512K)</FONT>
<BR><FONT SIZE=3D2>- drop the bitmaps indexes and recreate = after.</FONT> <BR><FONT SIZE=3D2>- use conventional path</FONT> </P> <P><FONT SIZE=3D2>With the first option I could load tables that have = about 12 bitmap indexes, but it didn't work for tables with 40 bitmap = indexes.</FONT></P> <P><FONT SIZE=3D2>The server is digital unix, SO vers. 4.0F, oracle = 8.0.6.1.0 (with 8.0.5.2.1 it happend too) and the application is a = datawarehouse.</FONT></P> <BR> <P><FONT SIZE=3D2>Hope this help</FONT> </P> <P><FONT SIZE=3D2>Mariana.</FONT> </P> <P><FONT SIZE=3D2>PD: sorry about my english</FONT> </P> <P><FONT SIZE=3D2>-----Mensaje original-----</FONT> <BR><FONT SIZE=3D2>De: Mark Leith [<A = HREF=3D"mailto:mark_at_cool-tools.co.uk">mailto:mark_at_cool-tools.co.uk</A>]<= /FONT> <BR><FONT SIZE=3D2>Enviado el: Jueves 7 de Septiembre de 2000 = 08:56</FONT> <BR><FONT SIZE=3D2>Para: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Asunto: RE: ORA-4031</FONT> </P> <BR> <P><FONT SIZE=3D2>If that is "BAD" English, I should have = gotten an A in my GCSE exam:)</FONT> </P> <P><FONT SIZE=3D2>Your English is fine my friend.</FONT> </P> <BR> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>Bellesi</FONT> <BR><FONT SIZE=3D2>Sent: 07 September 2000 12:05</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> </P> <BR> <P><FONT SIZE=3D2>Hi, (.. i want to apologize for my BAD = english)</FONT> <BR><FONT SIZE=3D2> have you cheked the fragmentation of your = shared pool ??</FONT> <BR><FONT SIZE=3D2>Are there hundreds of concurrent users not using = bind variables ??</FONT> </P> <P><FONT SIZE=3D2>:)</FONT> </P> <P><FONT SIZE=3D2>I think you've already checked that, so :</FONT> </P> <P><FONT SIZE=3D2>I faced with this kind of error during a bulk insert = on a datawarehouse</FONT> <BR><FONT SIZE=3D2>(Oracle 8.1.5, HP-UX )</FONT> </P> <P><FONT SIZE=3D2>My "little" INSERT used 300M in the shared = pool.</FONT> <BR><FONT SIZE=3D2>After doubling the shared pool, my 'little' INSERT = used 600 M.</FONT> </P> <P><FONT SIZE=3D2>It seemed to be a BUG ..... maybe platform = specific</FONT> </P> <P><FONT SIZE=3D2>I can ask to Oracle if this fits you requests</FONT> </P> <P><FONT SIZE=3D2>Hope this helps</FONT> <BR><FONT SIZE=3D2>Davide</FONT> </P> <BR> <BR> <BR> <BR> <BR> <BR> <BR> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>Sent: Wed, 06 Sep 2000 06:34:38 = -0800</FONT> <BR><FONT SIZE=3D2>To: = ORACLE-L_at_fatcity.com</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>We had a similar error but it was ORA-04030 that = pointed to system memory</FONT> <BR><FONT SIZE=3D2>being exhausted.</FONT> <BR><FONT SIZE=3D2>I am assuming you are on a UNIX OS and that you are = running perl there.</FONT> <BR><FONT SIZE=3D2>On NT it is much trickier to pinpoint a problem. I = don't know of any</FONT> <BR><FONT SIZE=3D2>accurate memory diagnostic tools out there for = NT</FONT> </P> <BR> <P><FONT SIZE=3D2>I went looking at the ulimit for the user on AIX = 4.3.3.0 platform</FONT> <BR><FONT SIZE=3D2>The systems admin and I upped the ulimit from = 256M for data soft limit to</FONT> <BR><FONT SIZE=3D2>512M for the hard limit which the soft limit can be = raised to</FONT> <BR><FONT SIZE=3D2>That took care of the immediate need.</FONT> </P> <P><FONT SIZE=3D2>Then went to explain plan the code. There was = the key. Besides trying to</FONT> <BR><FONT SIZE=3D2>take an average on the start and stop date(s), and = having 6 million rows</FONT> <BR><FONT SIZE=3D2>for the major table being joined there were several = small coding tricks</FONT> <BR><FONT SIZE=3D2>they didn't take advantage of at the start.</FONT> <BR><FONT SIZE=3D2>Once we sent the code through explain plan and saw = how much nesting, full</FONT> <BR><FONT SIZE=3D2>table scans, and such we then started to tune their = code.</FONT> </P> <P><FONT SIZE=3D2>My suggestion is to do both memory tuning of the = system file on UNIX and</FONT> <BR><FONT SIZE=3D2>checking of the code.</FONT> <BR><FONT SIZE=3D2>For the system file (if you have one.. on Solaris = and HP-UX there is one)</FONT> <BR><FONT SIZE=3D2>Make sure shmmax and other shared memory segment = parameters are tuned for</FONT> <BR><FONT SIZE=3D2>the system.</FONT> <BR><FONT SIZE=3D2>I heard that Toad is a good tool to use for the = database (tuning and</FONT> <BR><FONT SIZE=3D2>maintenance and such)</FONT> <BR><FONT SIZE=3D2>I am aiming to give it a whirl in the next few = weeks.</FONT> <BR><FONT SIZE=3D2>Further aside...</FONT> <BR><FONT SIZE=3D2>I tried getting SQL Station to work but it is a) = intrusive with requiring</FONT> <BR><FONT SIZE=3D2>you to create some sort of repository in the target = database b) not user</FONT> <BR><FONT SIZE=3D2>install friendly</FONT> </P> <P><FONT SIZE=3D2>Good luck.</FONT> </P> <P><FONT SIZE=3D2> Heidi</FONT> </P> <BR> <BR> <BR> <P><FONT = SIZE=3D2> &nb= sp; Kader Ben</FONT> <BR><FONT = SIZE=3D2> &nb= sp; = <kaderb_at_yahoo = To: Multiple recipients of list</FONT> <BR><FONT SIZE=3D2>ORACLE-L</FONT> <BR><FONT = SIZE=3D2> &nb= sp; = .com> &nb= sp; <ORACLE-L_at_fatcity.com></FONT> <BR><FONT = SIZE=3D2> &nb= sp; Sent = by: &nb= sp; cc:</FONT> <BR><FONT = SIZE=3D2> &nb= sp; = root_at_fatcity. = Subject: ORA-4031</FONT> <BR><FONT = SIZE=3D2> &nb= sp; com</FONT> </P> <BR> <P><FONT = SIZE=3D2> &nb= sp; 09/05/00</FONT> <BR><FONT = SIZE=3D2> &nb= sp; 05:32 PM</FONT> <BR><FONT = SIZE=3D2> &nb= sp; Please</FONT> <BR><FONT = SIZE=3D2> &nb= sp; respond to</FONT> <BR><FONT = SIZE=3D2> &nb= sp; ORACLE-L</FONT> </P> <BR> <BR> <BR> <BR> <P><FONT SIZE=3D2>Hi friends,</FONT> <BR><FONT SIZE=3D2> I have a perl script that load file content = into a</FONT> <BR><FONT SIZE=3D2>table, since this morning I am facing with = the</FONT> <BR><FONT SIZE=3D2>following error:</FONT> </P> <P><FONT SIZE=3D2>FATAL ERROR: (DBD::Oracle::db do failed: = ORA-04031:</FONT> <BR><FONT SIZE=3D2>unable to allocate 52 bytes of shared memory =
("shared</FONT>
<BR><FONT SIZE=3D2>pool","insert into grille values =
('...","sql</FONT>
<BR><FONT SIZE=3D2>area","strdef : prsstr") (DBD ERROR: = OCIStmtExecute)</FONT> <BR><FONT SIZE=3D2>at infinit.pm line 123, <FILE> chunk 1.</FONT> <BR><FONT SIZE=3D2>)</FONT> </P> <P><FONT SIZE=3D2>I have increased the shared_pool-size and parameter = in</FONT> <BR><FONT SIZE=3D2>init.ora and I still running the same problem. I = don't</FONT> <BR><FONT SIZE=3D2>know what I can do else ;)</FONT> </P> <P><FONT SIZE=3D2>This is fragment from my init.ora:</FONT> <BR><FONT SIZE=3D2>shared_pool_size =3D 36000000</FONT> <BR><FONT SIZE=3D2>shared_pool_reserved_size =3D 160000000</FONT> <BR><FONT SIZE=3D2>shared_pool_reserved_min_alloc =3D 8000000</FONT> <BR><FONT SIZE=3D2>sort_area_size =3D 2000000</FONT> </P> <P><FONT SIZE=3D2>Thank you for your help in advance,</FONT> </P> <P><FONT SIZE=3D2>Kader</FONT> </P> <BR> <BR> <P><FONT = SIZE=3D2>__________________________________________________</FONT> <BR><FONT SIZE=3D2>Do You Yahoo!?</FONT> <BR><FONT SIZE=3D2>Yahoo! Mail - Free email you can access from = anywhere!</FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://mail.yahoo.com/" = TARGET=3D"_blank">http://mail.yahoo.com/</A></FONT> <BR><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Author: Kader Ben</FONT> <BR><FONT SIZE=3D2> INET: kaderb_at_yahoo.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P> <BR> <BR> <BR> <P><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Author:</FONT> <BR><FONT SIZE=3D2> INET: Heidi_Schmidt_at_gillette.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P> <BR> <BR> <P><FONT = SIZE=3D2>_______________________________________________________________= ____________</FONT> <BR><FONT SIZE=3D2>Visit <A HREF=3D"http://www.visto.com/info" = TARGET=3D"_blank">http://www.visto.com/info</A>, your free web-based = communications center.</FONT> <BR><FONT SIZE=3D2>Visto.com. Life on the Dot.</FONT> </P> <P><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Author: Davide Bellesi</FONT> <BR><FONT SIZE=3D2> INET: davide.bellesi_at_visto.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Mark Leith</FONT> <BR><FONT SIZE=3D2> INET: mark_at_cool-tools.co.uk</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT =
 |
 |