Re: v$object_usage: anyone have bad experience with it?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 5 Feb 2005 16:18:18 -0000
Message-ID: <003c01c50b9e$4d85f490$6702a8c0@Primary>

Lex,

You're right. The 'correction' in metalink note 223303.1 is sufficiently incomplete to be wrong.

The sequence is:

acquire mode 4 or 5 on child table

Acquire mode 3 on the parent

update/delete parent row

release child mode 4, or convert child mode 5 to mode 3.

The mode 4 / mode 5 thing depends on
whether the transaction entails changes
to the child table (mode 5) or not (mode 4).

The session can still block at step one, and will be blocking other DML on the child
until step 4.

Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005

Original Message ----- From: "Lex de Haan" <lex.de.haan_at_naturaljoin.nl> To: <zhuchao_at_gmail.com> Cc: <oracle-l_at_freelists.org> Sent: Saturday, February 05, 2005 2:01 PM Subject: RE: v$object_usage: anyone have bad experience with it?

AFAIK, there is still a subtle difference in locking behavior between indexed and unindexed FK columns, when you update the PK in the parent table. Oracle tries to acquire a *table level* share row exclusive lock (SSX) on the child table, and releases it immediately afterwards. If you have an index on the FK column in the child table, the lock requested will be a row share one (SS instead of SSX) which still prevents other transactions to lock the table exclusively, but it *does* allow non-conflicting DML against the parent/child tables.

By the way, the whole thing is a non-issue if you adhere to a very important Relational rule: "you should not update primary keys" ...

additions/corrections welcome, kind regards,

Lex.

Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of zhu chao
Sent: Saturday, February 05, 2005 14:23
To: Lex de Haan
Cc: ORACLE-L
Subject: Re: v$object_usage: anyone have bad experience with it?

Hi Lex,

Actually CPU bottleneck has been solved temporiry . I noticed the redundent index problem while I was doing the tuning job on that host.

Unique index can be dropped if it was created with a unique index without specifying a unique constraint on the column.

And your words about the FK index at 9.2 is no longer accurate, as note 223303.1 said. The document IS wrong in this case. "This new locking behaviour is an expected behaviour for the Oracle code. As only shared locks are involved, it does not prevent DML from being issued against either the child or parent tables. It will prevent operations that require an exclusive table level lock.
However, as it generally considered to be bad design to have an application implementing exclusive table locks, the impact of the change should be minimal."

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Feb 05 2005 - 11:20:58 CST