Re: Locks

In article <7v7acc$t7b$1_at_autumn.news.rcn.net>,   "Jerry Gitomer" <jgitomer_at_hbsrx.com> wrote:
> Have you analyzed the table either estimating or computing
> statistics since the index was dropped?
> I prefer to compute rather than estimate, but that is personal
> preference. Run
>
> ANALYZE TABLE <big_table> COMPUTE STATISTICS;
>
> hth
> jerry gitomer
>
>>Perhaps this post is worthless, but what is a good way to try
> and find
> >out why a database starts running slowly. I take a look at the
> the
> >locks view and see about 40 locks on a certain table. It is
> possible
> >that many users are updating this table and creating a bottle
> neck. I
> >also see tons of updates happening in the v$session view.
> >
> >My question is, how do I actually pin point the problem?
> Everything was
> >running fine yesterday. Then, some jerk accidentally dropped a
> promary
> >key index on a major table. It took 45 minutes to re-create
> this index,
> >and everything has been going slow since.
> >
> >I am guessing that all users are on re-entering their work, but
> how can
> >I be sure that this is what is causing the problems??
> >

I had the same thought about analyzing the table but here are some other possibilities to check if analyzing does not fix the problem.

Was the index that support enforcement of the PK constraint built in the right location (tablespace)? And with the right parameters?

Are you sure that the code you used to rebuild the index looks the same as the index that was dropped? You can capture the SQL being ran by sessions using the table from v$sql to see what columns they are using. If it is a multi-column primary key perhaps some of the columns were re-arranged and the source code not updated.

To partially answer your question about how to find problems, You can use the dynamic wait tables to try to pinpoint a problem. These are v$system_event (for system wide wait event statistics), v$session_event (for per session wait events) and v$session_wait (for what a session is waiting on). You can see the Reference manual for some information on these tables, and the Performance manual for some more. But for a quick cource see www.orapub.com for the paper Direct Contention Identification Using Oracle's Session Wait Virtual Tables.

Before trying to trace through these views I would look at the lockwait column of v$session and if you have lockwaited sessions. If you do you can probably track them down faster than trying to use the wait tables. Oracle provides a couple of scripts in $ORACLE_HOME/rdbms/admin for finding who is blocking who.

Good luck.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 27 1999 - 15:09:08 CDT