FAQ
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:
1 select uniqueness, sum(bytes) from user_segments a,
user_indexes b where segment_name in
2 (select index_name from v$object_usage where used='NO')
3 and a.segment_name=b.index_name
4* group by uniqueness
SQL> /

UNIQUENES SUM(BYTES)

--------- ----------------
NONUNIQUE 36,488,478,720

UNIQUE 272,760,832

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.

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.

Can someone share your experience/opinion on this?
Thanks

--
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Lex de Haan at Feb 5, 2005 at 7:19 am
    see comments in line ...
    Lex.


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

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    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
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Zhu chao at Feb 5, 2005 at 7:55 am
    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
    wrote:
    see comments in line ...

    Lex.

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

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    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
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Hemant K Chitale at Feb 5, 2005 at 8:23 am
    I have to ask about
    " Unique index can be dropped if it was created with a unique index
    without specifying a unique constraint on the column."
    Either you have miss-typed or transposed "constraint" and "index"
    or you or I have misunderstood them.

    From early versions onwards, and 9i still supports this, it has
    always been possible to create a Unique Index __without__ defining it
    as a Constraint. Such an Index is still used to enforce Uniqueness [ie
    Non-Duplicates]
    on Inserts/Updates. In fact, in earlier versions, the actual text of
    the message for ORA-0001 used to be something different, something
    like "... duplicate value not allowed ..."

    In your database [and I know I do have in a number of my databases], you
    might have a Unique Index created as an Index, but __not__ defined as
    a Constraint.

    If the Index is not used in Queries, that's just too bad ! Queries are
    incorrectly
    written.
    If, however, you drop the Index, you are eliminating all the Index's automatic
    action in preventing Duplicate values !

    Furthermore I believe that the note on FKs actually states that if you have a
    well-defined application, you do not need exclusive table locks.
    However, the absence of a Unique Index, even if not placing a table lock,
    would still require a full scan of the parent table !

    Hemant
    At 08:53 PM Saturday, zhu chao wrote:
    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."

    Regards
    Zhu Chao
    www.cnoug.org
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
  • Zhu chao at Feb 5, 2005 at 8:26 am
    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
    wrote:
    see comments in line ...

    Lex.

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

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    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
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Feb 5, 2005 at 9:04 am
    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
    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
    wrote:
    see comments in line ...

    Lex.

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

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org

    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
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Feb 5, 2005 at 11:20 am
    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"
    To:
    Cc:
    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
    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
  • Hemant K Chitale at Feb 5, 2005 at 7:49 am
    How long did you keep monitoring running ? On some versions/platforms,
    the information is updated at probably 3 hours. You might want to check
    v$object_usage
    after a couple of days.

    DON'T drop those UNIQUE Indexes. They are probably enforcing Uniqueness
    on Inserts/Updates but not being used in Selects [bad design or bad
    queries ?!]

    Not sure what you mean by
    "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."
    Why is the Presence/Absence of Unique Indexes used for FK lookups "no
    longer an issue" ?

    Hemant
    At 05:52 PM Saturday, zhu chao wrote:

    1 select uniqueness, sum(bytes) from user_segments a,
    user_indexes b where segment_name in
    2 (select index_name from v$object_usage where used='NO')
    3 and a.segment_name=b.index_name
    4* group by uniqueness
    SQL> /
    UNIQUENES SUM(BYTES)
    --------- ----------------
    NONUNIQUE 36,488,478,720
    UNIQUE 272,760,832
    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.

    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.

    Can someone share your experience/opinion on this?
    Thanks

    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
  • Zhu chao at Feb 5, 2005 at 9:02 am
    Hi, Hemant,

    The index monitoring now has been enable for about two days. I
    plan to file change request and drop those unused indexes one by one.
    This is going to take a long time, but in case we really see something
    getting bad, we can easily find out what caused the problem.

    I also plan to keep those unique index, even if they are not used
    in SQL execution plan. Thanks for your confirm. Your second reply also
    remind me that FK related indexes are all unique, so I just keep all
    the unique index and it will solve both the FK issue and unique
    constraint issue.

    THanks very much.


    On Sat, 05 Feb 2005 20:44:14 +0800, Hemant K Chitale
    wrote:
    1. How long did you keep monitoring running ? On some versions/platforms,
    the information is updated at probably 3 hours. You might want to check
    v$object_usage
    after a couple of days.

    2. DON'T drop those UNIQUE Indexes. They are probably enforcing Uniqueness
    on Inserts/Updates but not being used in Selects [bad design or bad
    queries ?!]

    3. Not sure what you mean by
    "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."
    Why is the Presence/Absence of Unique Indexes used for FK lookups "no
    longer an issue" ?

    Hemant
    At 05:52 PM Saturday, zhu chao wrote:

    1 select uniqueness, sum(bytes) from user_segments a,
    user_indexes b where segment_name in
    2 (select index_name from v$object_usage where used='NO')
    3 and a.segment_name=b.index_name
    4* group by uniqueness
    SQL> /
    UNIQUENES SUM(BYTES)
    --------- ----------------
    NONUNIQUE 36,488,478,720
    UNIQUE 272,760,832
    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.

    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.

    Can someone share your experience/opinion on this?
    Thanks

    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
    --
    Regards
    Zhu Chao
    www.cnoug.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Põder at Feb 5, 2005 at 10:42 am
    Hi,

    You might also want to check from v$segment_statistics whether anyone is
    actually using your indes (logical reads, segment scans) if your statistics
    level allows segment-level statistics gathering.

    Also you could sample from V$SQL_PLAN using object_owner and object_name
    columns to see whether any cursor in library cache actually uses the index
    in its execution. When joining v$sql_plan back to v$sql, you could sample
    how often this query is executed and how much resources does it take. Btw,
    you could even try the same query with NO_INDEX hint in 10g to see how much
    resources would the query take when particular index is not available.

    The v$sql_plan method doesn't show use index usage for foreign key
    enforcement though.

    Tanel.
    Hi, Hemant,
    The index monitoring now has been enable for about two days. I
    plan to file change request and drop those unused indexes one by one.
    This is going to take a long time, but in case we really see something
    getting bad, we can easily find out what caused the problem.
    I also plan to keep those unique index, even if they are not used
    in SQL execution plan. Thanks for your confirm. Your second reply also
    remind me that FK related indexes are all unique, so I just keep all
    the unique index and it will solve both the FK issue and unique
    constraint issue.

    THanks very much.
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Põder at Feb 5, 2005 at 10:57 am

    The v$sql_plan method doesn't show use index usage for foreign key
    enforcement though.
    And when sampling from v$sql_plan, you should do the sampling over different
    workload periods (day, night, month end, payroll cycle etc), otherwise you
    migh miss out some cached statements which are used on specific time periods
    only (due shared pool aging).

    Tanel.
  • Jonathan Lewis at Feb 5, 2005 at 11:06 am
    v$segment_statistics will only give you a clue about
    your index use, after all, the index will have to be
    traversed for every insert, update, delete and RI
    check. This means you will see logical I/Os, even
    when no execution plans take advantage of the
    index.

    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: "Tanel Põder"
    To: "ORACLE-L"
    Sent: Saturday, February 05, 2005 3:39 PM
    Subject: Re: v$object_usage: anyone have bad experience with it?

    Hi,

    You might also want to check from v$segment_statistics whether anyone is
    actually using your indes (logical reads, segment scans) if your statistics
    level allows segment-level statistics gathering.
  • Yechiel Adar at Feb 6, 2005 at 5:04 am

    Hi, Hemant,
    The index monitoring now has been enable for about two days.
    I would not count two day a good period. What about end of month, end of
    quarter and end of year processing?

    Yechiel Adar
    Mehish Computer Services
  • Jonathan Lewis at Feb 5, 2005 at 11:27 am
    The index monitoring feature simply flags
    an index as used when the optimizer generates
    an execution plan that includes that index.

    This means that any use of the index that is
    not instigated by the optimization process
    is simply 'forgotten'.

    It also means that when the optimizer produces
    a 'bad plan', you may have a misleading report
    identifying a 'used, therefore useful' index.

    For a good strategy on index monitoring,
    you might like to read the following article,
    which has some useful comments on the
    issue:

    http://www.dizwell.com/html/useful_indexes.html

    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: "zhu chao"
    To: "ORACLE-L"
    Sent: Saturday, February 05, 2005 9:52 AM
    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:
    1 select uniqueness, sum(bytes) from user_segments a,
    user_indexes b where segment_name in
    2 (select index_name from v$object_usage where used='NO')
    3 and a.segment_name=b.index_name
    4* group by uniqueness
    SQL> /

    UNIQUENES SUM(BYTES)

    --------- ----------------
    NONUNIQUE 36,488,478,720

    UNIQUE 272,760,832

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 5, '05 at 4:55a
activeFeb 6, '05 at 5:04a
posts14
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase