Re: v$object_usage: anyone have bad experience with it?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <lex.de.haan@xxxxxxxxxxxxxx>, <zhuchao@xxxxxxxxx>
- Date: Sat, 5 Feb 2005 16:18:18 -0000
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@xxxxxxxxxxxxxx>
To: <zhuchao@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts: