Many thanks for these gentle guys for your prompt
reply.
With your advises I did resolve my problem both by
tuning the shared pool and reducing the size of row to
be inserted (reviewing my code!).
Kader
- "Galleri, Mariana" <MGalleri_at_uniFON.com.ar> wrote:
>
> 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 = 8000000
> sort_area_size = 2000000
>
> Thank you for your help in advance,
Received on Thu Sep 07 2000 - 09:36:09 CDT
