Saturday, April 6, 2013

Nasty ORA 4030 in 11.2.0.3.5 using text index ( makes me grumpy! )

I had a batch program chew up 16 gb of PGA and run for extended time doing updates to ( my only ) table that has a text index on it ( customer last name ) ... used for "soundex" lookups.

It choked eventually and terminated with ORA 4030 ... have not seen one of those in a really long time.

Apparently Oracle has had a fix identified and available from August 2012 that ( somehow ) they have not seen as important enough to include in last several rounds of database PSU's.  Wow ... thanks so much Oracle support!

Looks like the bug / 4030 will only hit things that do large numbers of changes ( inserts also ? dunno ) to text index column ... but still ... not happy here.

Problem Key   
ORA-4030 
[404008]     
[druhopen:hash]   
[druhopen:hash table]

Testing of the fix one-off looks ok so far and again ... found a while back ( p14367567_112030_Linux-x86-64.zip ).

The following are the bugs fixed by this patch:
  14367567: EXCESSIVE PGA MEMORY GROWTH LEADING TO HIGH SWAPPING AND PERFORMANCE IMPACTS

No comments:

Post a Comment