RE: v$object_usage: anyone have bad experience with it?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <zhuchao@xxxxxxxxx>
- Date: Sat, 5 Feb 2005 15:01:58 +0100
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."
And talk back to the redundent index problem. Remove the redundent index
not only released the space, but also reduce the overhead to maintain the
index, so speed up the transactions/reduce the redo size.
We have been knowing the v$object_usage for a long time since 9.2 is
released, but personally I didn't drop index according to the
v$object_usage.
We have hundreds of database with xxxG/xT in size, if drop redundent
index according to v$object_usage is proven to be OK. This can save us
Terabytes of disk space, which can mean a lot of money.
THanks for your help.
On Sat, 5 Feb 2005 13:16:59 +0100, Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx>
wrote:
> see comments in line ...
>
> 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 10:52
> To: ORACLE-L
> Subject: v$object_usage: anyone have bad experience with it?
>
> Hi, all,
>
> We have a database which maxed its CPU capacity. and I did some SQL
> tuning /index creation. I happened to think some index should be
> useless and I enabled index monitoring for some index and they do
> showed used='NO' in v$object_usage. Later I enabled index monitoring
> in all the indexes for this user, and I see:
>
> SQL> select used,count(*) from v$object_usage group by used;
> USE COUNT(*)
> --- --------
> NO 160
> YES 108
>
> and these indexes used 36G space.
>
> LEX: Why do you care about storage, if you say you have a CPU problem?
>
> I plan to drop those indexes. But I am not sure whether there is
> bugs/issues with the v$object_usage that it does not report some used
> index, or under some circumstance, even SQL don't use the index , we
> have to keep these indexes. One possible is unique index. Unique index
> is not used to speedup SQL, but to enforce business logic.
>
> LEX: Indeed -- and if they are associated with UNIQUE or PK
> constraints, you'll find out, because you won't be able to drop them
> if the constraints are enabled...
>
> The other is for the FK related index. But we are running oracle
> 9.2.0.5 and I think it is no longer an issue.
>
> LEX: I think this is a misunderstanding -- there still is different
> locking behavior with and without indexes on your foreign keys. Just
> less difference than before. This is explained very well in the Concepts
manual.
>
> Can someone share your experience/opinion on this?
>
> LEX: I still don't understand how you hope to resolve the perceived
> CPU problem by dropping indexes?
>
> Thanks
>
> --
> Regards
> Zhu Chao
> www.cnoug.org
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards
Zhu Chao
www.cnoug.org
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
