Re: Strange applicaton problem of :Rebuild of indices

"nirav" <shivam71_at_gmail.com> wrote in message news:1193539460.625936.74210_at_o80g2000hse.googlegroups.com...
>> Does part of the load process also include
>> a bulk delete of a reasonably large fraction
>> of the table.
>>
>> When the process hangs, what event is it
>> waiting on, and is it a series of short waits
>> or a single very long wait, and does it appear
>> to recover automatically after some time ?
>>
>> --
>> Regards
>>
>> Jonathan Lewishttp://jonathanlewis.wordpress.com
>>
>> Author: Cost Based Oracle:
>> Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>>
>> The Co-operative Oracle Users'
>> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Hi Jonathan,
>
> Thank you very much for responding.
>
> The load process does not involve deleting a large fraction of data in
> tables. It is just loading the tables. -The exact details of what the
> code does , I could not yet discuss with developer, I shall be able to
> post that on Tuesday. But this is my understanding.
>
> The hang is a single long hang- at least from the perspective of the
> QA or tester -they report that the screen is frozen since last 45
> minutes etc.
>
> I shall be running sql_trace for this on Tuesday and be able to report
> what event it is waiting on.
>
> No, it does not disapper automatically. When this happens, all indices
> on the tables need to be dropped and rebuilt and then things get
> normal.
>
> Best regards,
> Nirav
>
>
>

You won't necessarily need the whole trace, just a few calls to the appropriate row of v$session_wait when the process is hung.

Also, take a look at the alert log (if you haven't already done so) in case it has any clues.

Are there any bitmap indexes on the tables ? Are there several load processes taking place concurrently ? Are there any referential integrity constraints ?

But I'll refrain from further comment until you've had some time to talk to the developers, and have the critical wait information.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html