Home » RDBMS Server » Server Administration » Strange applicaton problem of :Rebuild of indices
Strange applicaton problem of :Rebuild of indices [message #276991] Sat, 27 October 2007 14:04 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

We have a strange problem. From the front end a load of data is done into several tables (including one that has a blob in it). Now after this load is done several times , rebuild of indices of these tables must be done else the process hangs...this situation occurs in only in schema, but not in any other schemas that identical...can you plesase guide in this regard as to how I should approach the problem...being a dba I have access only to backend not the front end.

Thanks,
Nirav
Re: Strange applicaton problem of :Rebuild of indices [message #276993 is a reply to message #276991] Sat, 27 October 2007 14:29 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
How you know that indexes hang your process?
After load did you check the validity of your indexes?

One more thing if you don't rebuild indexes but you collect the statistics on those tables?
Re: Strange applicaton problem of :Rebuild of indices [message #276994 is a reply to message #276991] Sat, 27 October 2007 14:33 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/

>rebuild of indices of these tables must be done else the process hangs.

"hang" is a somewhat ambiguous term.

What does SQL_TRACE of this session reveal?

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

Will report the holder & the waiter sessions & must be run while "hang" occurs




[Updated on: Sat, 27 October 2007 14:33] by Moderator

Report message to a moderator

Re: Strange applicaton problem of :Rebuild of indices [message #276995 is a reply to message #276993] Sat, 27 October 2007 14:34 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Dreamzz,

Thank you for response, regarding questions you mentioned:

1)

The developer says that when he drops the indices and rebuilds them, then things start working. so that is the reason for arriving that the conclusion that indices cause the hang.

2)
No after load , i did not check the validity of the indices.
will do that first thing on Tuesday when I talk to the developer.
3) yes stats are collected each night for all schemas in the database.

Thanks again,
Nirav

[Updated on: Sat, 27 October 2007 14:36]

Report message to a moderator

Re: Strange applicaton problem of :Rebuild of indices [message #276996 is a reply to message #276994] Sat, 27 October 2007 14:40 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Anacedent,

Thanks , here are my inputs:

By 'Hang'- I meant the process of importing data to a set of tables hangs forever...

No I did not take the sql_trace.

can you also tell more more about how to go about identifying the 'locking' issues- I mean is there some document etc. that I can read up on this..

With regards,
Nirav
Re: Strange applicaton problem of :Rebuild of indices [message #276997 is a reply to message #276991] Sat, 27 October 2007 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>The developer says that when he drops the indices and rebuilds them, then things start working
The DUHVELOPER has DDL access to Production database??????
I am certainly glad that I don't depend on data from your database.

What Operating System Name & version?
What version of Oracle RDBMS to 4 decimal places?

What can (or can not) SQL*Plus sessions do inside database during the "hang"?

What does alert_SID.log file show during "hang"?
Re: Strange applicaton problem of :Rebuild of indices [message #276998 is a reply to message #276997] Sat, 27 October 2007 15:02 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Anacedent,

No, this description was for Development database only...the rebuild of indices is set up as a nightly job on the Solaris server where the db resides..but inspite of that job running, sometimes the problems are still occuring, so the developer in DEV. instance, had to drop and rebuild the indices twice or thrice to get the data load done.

OS - Solaris 8, RDBMS - 9.2.0.7.(64 BIT)

This hang only affects this process ways of import of the data into the tables- I do not hear any complaints from anyother folks in the team that their work in affected in any way.

No, the alert log does not log any error or any message at all for past three months so nothing is logged into alert log that can give a clue.

Thanks,
Nirav
Re: Strange applicaton problem of :Rebuild of indices [message #276999 is a reply to message #276991] Sat, 27 October 2007 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>can you also tell more more about how to go about identifying the 'locking' issues
I suspect the "hang" is not the results of LOCK in the strictest sense of the word.
I suspect the problem is Enqueue Wait; but this only a wild guess at this time.
The SQL I posted earlier will report Holder session & Waiter session regardless of the reason (Lock, Enqueue Wait, etc.)

First you need to identify who is causing the "hang" & what they are doing to cause the hang; before you can make necessary changes to avoid the hang altogther.

Part of me suspects that index rebuild is NOT really part of the solution (directly).

Re: Strange applicaton problem of :Rebuild of indices [message #277001 is a reply to message #276999] Sat, 27 October 2007 15:49 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
anacedent,

Thanks a lot, I will check these and post on Tuesday. (a day off on Monday). Thank you for the kind help.

Regards,
Nirav
Previous Topic: locks
Next Topic: External Tables / System Tablespace
Goto Forum:
  


Current Time: Tue Dec 06 04:46:35 CST 2016

Total time taken to generate the page: 0.10975 seconds