Oracle Scratchpad

April 19, 2010

Index Rebuild ?

Filed under: Index Rebuilds,Indexing,Partitioning,Troubleshooting — Jonathan Lewis @ 7:13 pm GMT Apr 19,2010

While searching on Metalink for clues about an oddity relating to an index rebuild, I came across Bug 6767655  – reported in 10.2.0.3 and fixed in 11.2.  The problem is described as follows:

When having two sessions running concurrently, one doing inserts to a partitioned table, and the other doing partition maintenance operations, there is a chance of the DML session core dumping in kauxs_do_journal(),  i.e.:

ORA-7445: exception encountered: core dump [kauxs_do_journal()+21] [SIGSEGV]  [Address not mapped to object] [0x2] [] []

DIAGNOSTIC ANALYSIS:
The problem seems to be a timing issue – or at least there is a timing component involved. When doing the partition split, an ORA-54 is normally raised, however by running the split in a loop, it will eventually succeed (and thus causing problems for the session running the DML).


This is an example of the type of report on Metalink that really irritates me. Why ? Because of the lack of consistency in the content. The title of my posting is about rebuilding indexes – but this problem is (apparently) not about rebuilding indexes … until you get into the information about rediscovery and workarounds:

RELEASE NOTES:
While doing concurrent DML and rebuilding indexes online there is chance of core dump in kauxs_journal.

REDISCOVERY INFORMATION:
Dump in kauxs_journal while doing concurrent DML and index rebuild online.

WORKAROUND:
Avoid concurrent DML and index rebuild online. Serialize these operations.

Suddenly all references to partitions and partition maintenance have disappeared and the issue is all about index rebuilds. The only way I can link the two together is  by assuming that the problem first appeared to be related to partiiton splits because someone wrote some code that rebuilt index partitions online immediately after the partition split. But I’d really like to have a clear explanation.

Should I worry when I’m not using partitions at all, or does it apply only to locally partitioned indexes, or maybe partitioned indexes on non-partitioned table, or globally partitioned indexes on partitioned tables, global indexes on partitioned tables – and how significant is that “split” ?

I don’t like having to guess … especially in a case like this where the diagnostic analysis says “there’s a timing component – sometimes it works sometimes it fails”. Is my code working today (or in development) because it’s a lucky day, or is it working because the bug doesn’t appear in my particular case.

[Further reading on rebuilding indexes]

3 Comments »

  1. Hmm, i will have to track this, i’ve been doing this kind of work for a while now in our database and we serialized it for a reason we have forgotten now. But i think in our case it was related to partitioned Context indexes.

    Comment by Curtis Ruck — April 19, 2010 @ 7:28 pm GMT Apr 19,2010 | Reply

  2. Hi Jonathan,

    Just looking for your advise to design a table in such a way it should respond within 3 Seconds.
    There is a requirement where User will give a Telephone number as Input. once input is given, The report such as Total Incoming calls | Total Outgoing calls | Total Answered | Total Un Answered | Total Incoming Duration Time | … etc.. This report can be taken in three ways 1. Hours basis 2. Weekly Basis 3. Monthly.. the report should come in 3 Seconds irrespective of Hrs| Week| month. Please advise me what will be better strategy ( partitioning, sub partitioning) so that i can achieve the result.

    Expecting your suggestions at the earliest.

    Thanks
    Prathap S

    Comment by Prathap — October 15, 2013 @ 11:08 am GMT Oct 15,2013 | Reply

    • Prathap,

      I have distinct memories of companies like Vodafone and BT paying me large sums of money over the years to answer questions like this. In part this is because they would take a day or so to brief me in some detail on their requirements and constraints so that I could work out which strategies could simply be ignored, which strategies might sensibly be investigated, and what possibly threats to test for before wasting too much effort doing something that wouldn’t work at the required scale in the right time.

      If you want an answer that matches the quality of your specification and is worth the price you’re prepared to pay then it’s an answer you should already have been able to work out.

      Comment by Jonathan Lewis — October 15, 2013 @ 7:53 pm GMT Oct 15,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers