Re: Oracle Locks

Hi Yong,

yes, I encountered another cause of library cache deadlocks. It's incremental export on 8.1.6.0. The deadlocks were occuring everywhere at the system with very high frequency (2-3 deadlocks during 10min). The work stoped and support department stood on their heads. Thank Steve again, he helped to determine the actual cause of the deadlocks.

Ed

> Hi,
>
> Since Jasmine didn't tell us the ORA error number, I want to add that in
> addition to the between-session (for lack of a better term) deadlocks
which are
> ORA-60 or ORA-12829, there's another type, self-deadlocks caused and
> experienced by the same session. This is ORA-4020, a library cache
deadlock.
> The way to resolve this deadlock is manually compile invalid triggers
(advice
> from Steve Adams). Also read the new book "Oracle8i Tuning and
Administration:
> The Essential Reference" by Heidi Thorpe, another Oracle expert from
Australia.
>
> There's one more type of deadlock associated with OPS, ORA-99. I have no
> additional comments.
>
> Yong Huang
> yong321_at_yahoo.com
>
> you wrote:
>
> Oracle detects deadlocks between two sessions automatically and rollbacks
> curren
> t sql statement for one of them but, of course, it doesn't solve the
problem.
> Yo
> u need to determine the cause of the deadlock in your particular case.
Have a
> lo
> ok at the user_dump_dest catalog and try to find a trace file which
> appropriates
> your offended session. In that file, you will be able to find a small
table
> (or
> matrix) which shows relations between the two sessions. Have a look at
block
> mo
> des of the sessions, they point the right way for further investigation.
The
> pos
> sible causes of deadlocks are
> 1. unindexed fk fields in a child table
> 2. number of concurent sessions which update a db block is higher than
number
> of
> available ITL slots in the block and there are no additional free space
to
> expa
> nd the next slot
> 3. you have high competition on a set of particular rows for example, an
> arbitra
> l table
> 4. ....
>
> The solution of the problem depends on the cause of the deadlock. Take a
look
> at
> www.ixora.com.au You will find detail explanations there.
>
> Ed
>
> We are using Oracle 8.0.4 on Windows NT 4.0 with the Siebel Application.
I am
> e
> xperiencing DEADLOCK problems which as a result is causing exports to
fail.
> Can
> anyone make suggestions as to how release these deadlocks apart from
shutdown/
> statup? How can I identify from v$locks which are the culprits?
>
> Any help would be appreciated.
>
> Regards
> Jasmine
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Photos - Share your holiday photos online!
> http://photos.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: yong huang
> INET: yong321_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Jan 05 2001 - 16:00:33 CST