| Strange applicaton problem of :Rebuild of indices [message #276991] |
Sat, 27 October 2007 14:04  |
orausern
Messages: 826
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 #276994 is a reply to message #276991] |
Sat, 27 October 2007 14:33  |
 |
BlackSwan
Messages: 26766
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 |
orausern
Messages: 826
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 |
orausern
Messages: 826
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 #276998 is a reply to message #276997] |
Sat, 27 October 2007 15:02 |
orausern
Messages: 826
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
