FAQ
Can we check as when Index was last used from the
dictioanry tables. Index are not in Monitoring mode
and Statisitic Level is TYPICAL in the Oracle 9i
database

TIA

Sanjay



Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

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

Search Discussions

  • Mladen Gogala at Feb 15, 2005 at 1:49 pm

    Sanjay Mishra wrote:
    Can we check as when Index was last used from the
    dictioanry tables. Index are not in Monitoring mode
    and Statisitic Level is TYPICAL in the Oracle 9i
    database

    TIA
    Sanjay



    __________________________________
    Do you Yahoo!?
    Yahoo! Mail - Helps protect you from nasty viruses.
    http://promotions.yahoo.com/new_mail
    --
    http://www.freelists.org/webpage/oracle-l
    V$SEGMENT_STATISTICS is populated nevertheless and it contains the
    values since the database startup.
    Also, V$SEGMENT_STATISTICS is very useful to determine candidates for a
    reorg. Here is the query
    that I use for that purpose:

    select owner,object_name,object_type,value
    from v$segment_statistics
    where statistic_name ='ITL waits' and value > 100
    /

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121

    --
    http://www.freelists.org/webpage/oracle-l
  • Sanjay Mishra at Feb 16, 2005 at 7:56 am
    I was reading and found that v$segment_Statistics
    might contain an entry for index if the index is
    subject to any DML. So is it correct that even we got
    some update to the table, then it means index might
    also been updated and so the v$Segment_Statistics got
    an entry for the Index.
    If it is the case then it will be difficult to judge
    as what indexes are really been queried by select
    statement. Any expert comment. It look like that even
    if we put the Index in monitoring mode, still this V$
    view is not accurate to make decisio as if the index
    is really been used by select queries

    I am little confused here as I thought that I can
    assume that if this v$view has an entry for index,
    then it mean, the index is really been used sometime
    back

    TIA

    sanjay
    --- Mladen Gogala
    wrote:
    Sanjay Mishra wrote:
    Can we check as when Index was last used from the
    dictioanry tables. Index are not in Monitoring mode
    and Statisitic Level is TYPICAL in the Oracle 9i
    database

    TIA
    Sanjay



    __________________________________
    Do you Yahoo!?
    Yahoo! Mail - Helps protect you from nasty viruses.
    http://promotions.yahoo.com/new_mail
    --
    http://www.freelists.org/webpage/oracle-l
    V$SEGMENT_STATISTICS is populated nevertheless and
    it contains the
    values since the database startup.
    Also, V$SEGMENT_STATISTICS is very useful to
    determine candidates for a
    reorg. Here is the query
    that I use for that purpose:

    select owner,object_name,object_type,value
    from v$segment_statistics
    where statistic_name ='ITL waits' and value > 100
    /

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121


    --
    http://www.freelists.org/webpage/oracle-l
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Feb 16, 2005 at 8:07 am
    I know this is not really the answer you were waiting for :-)
    but the main purpose of putting indexes (or tables) in monitoring mode in 9i is
    *not* to provide you with information, but rather to enable Oracle to perform
    smarter statistics collection. See the documentation of the DBMS_STATS package.

    By the way, in 10g segment monitoring is the instance-wide default; you cannot
    enable/disable it anymore for individual database objects.

    hope this still helps a little bit,

    Lex.


    Visit my website at http://www.naturaljoin.nl

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Sanjay Mishra
    Sent: Wednesday, February 16, 2005 13:54
    To: mgogala_at_allegientsystems.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Index usage or last used

    I was reading and found that v$segment_Statistics might contain an entry for
    index if the index is subject to any DML. So is it correct that even we got some
    update to the table, then it means index might also been updated and so the
    v$Segment_Statistics got an entry for the Index.
    If it is the case then it will be difficult to judge as what indexes are really
    been queried by select statement. Any expert comment. It look like that even if
    we put the Index in monitoring mode, still this V$ view is not accurate to make
    decisio as if the index is really been used by select queries

    I am little confused here as I thought that I can assume that if this v$view has
    an entry for index, then it mean, the index is really been used sometime back

    TIA

    sanjay
    --- Mladen Gogala
    wrote:
    Sanjay Mishra wrote:
    Can we check as when Index was last used from the dictioanry tables.
    Index are not in Monitoring mode and Statisitic Level is TYPICAL in
    the Oracle 9i database

    TIA
    Sanjay



    __________________________________
    Do you Yahoo!?
    Yahoo! Mail - Helps protect you from nasty viruses.
    http://promotions.yahoo.com/new_mail
    --
    http://www.freelists.org/webpage/oracle-l
    V$SEGMENT_STATISTICS is populated nevertheless and it contains the
    values since the database startup.
    Also, V$SEGMENT_STATISTICS is very useful to determine candidates for
    a reorg. Here is the query that I use for that purpose:

    select owner,object_name,object_type,value
    from v$segment_statistics
    where statistic_name ='ITL waits' and value > 100 /

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121


    --
    http://www.freelists.org/webpage/oracle-l
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Terry Sutton at Feb 16, 2005 at 1:06 pm
    Aren't you confusing ALTER TABLE MONITORING and ALTER INDEX
    MONITORING USAGE? The former is related to dbms_stats, the
    latter not.

    --Terry

    Original Message -----
    From: "Lex de Haan"
    To:;
    Cc:
    Sent: Wednesday, February 16, 2005 5:04 AM
    Subject: RE: Index usage or last used

    I know this is not really the answer you were waiting for :-)
    but the main purpose of putting indexes (or tables) in monitoring mode in 9i
    is
    *not* to provide you with information, but rather to enable Oracle to
    perform
    smarter statistics collection. See the documentation of the DBMS_STATS
    package.

    By the way, in 10g segment monitoring is the instance-wide default; you
    cannot
    enable/disable it anymore for individual database objects.

    hope this still helps a little bit,

    Lex.

    Visit my website at http://www.naturaljoin.nl

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On
    Behalf Of Sanjay Mishra
    Sent: Wednesday, February 16, 2005 13:54
    To: mgogala_at_allegientsystems.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Index usage or last used

    I was reading and found that v$segment_Statistics might contain an entry for
    index if the index is subject to any DML. So is it correct that even we got
    some
    update to the table, then it means index might also been updated and so the
    v$Segment_Statistics got an entry for the Index.
    If it is the case then it will be difficult to judge as what indexes are
    really
    been queried by select statement. Any expert comment. It look like that even
    if
    we put the Index in monitoring mode, still this V$ view is not accurate to
    make
    decisio as if the index is really been used by select queries

    I am little confused here as I thought that I can assume that if this v$view
    has
    an entry for index, then it mean, the index is really been used sometime
    back

    TIA

    sanjay
    --- Mladen Gogala
    wrote:
    Sanjay Mishra wrote:
    Can we check as when Index was last used from the dictioanry tables.
    Index are not in Monitoring mode and Statisitic Level is TYPICAL in
    the Oracle 9i database

    TIA
    Sanjay



    __________________________________
    Do you Yahoo!?
    Yahoo! Mail - Helps protect you from nasty viruses.
    http://promotions.yahoo.com/new_mail
    --
    http://www.freelists.org/webpage/oracle-l
    V$SEGMENT_STATISTICS is populated nevertheless and it contains the
    values since the database startup.
    Also, V$SEGMENT_STATISTICS is very useful to determine candidates for
    a reorg. Here is the query that I use for that purpose: >
    select owner,object_name,object_type,value
    from v$segment_statistics
    where statistic_name ='ITL waits' and value > 100 / >
    --
    Mladen Gogala
    Oracle DBA
    Ext. 121


    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

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

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at Feb 16, 2005 at 1:39 pm
    The alter index monitoring|nomonitoring usage syntax still appears in the
    10g SQL manual though I did not see a monitoring parameter under alter
    table.

    HTH -- Mark D Powell --

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Terry Sutton
    Sent: Wednesday, February 16, 2005 1:01 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Index usage or last used

    Aren't you confusing ALTER TABLE MONITORING and ALTER INDEX
    MONITORING USAGE? The former is related to dbms_stats, the
    latter not.

    --Terry

    Original Message -----
    From: "Lex de Haan"
    To:;
    Cc:
    Sent: Wednesday, February 16, 2005 5:04 AM
    Subject: RE: Index usage or last used

    I know this is not really the answer you were waiting for :-)
    but the main purpose of putting indexes (or tables) in monitoring mode in 9i
    is
    *not* to provide you with information, but rather to enable Oracle to
    perform
    smarter statistics collection. See the documentation of the DBMS_STATS
    package.

    By the way, in 10g segment monitoring is the instance-wide default; you
    cannot
    enable/disable it anymore for individual database objects.

    hope this still helps a little bit,

    Lex.

    Visit my website at http://www.naturaljoin.nl

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On
    Behalf Of Sanjay Mishra
    Sent: Wednesday, February 16, 2005 13:54
    To: mgogala_at_allegientsystems.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Index usage or last used

    I was reading and found that v$segment_Statistics might contain an entry for
    index if the index is subject to any DML. So is it correct that even we got
    some
    update to the table, then it means index might also been updated and so the
    v$Segment_Statistics got an entry for the Index.
    If it is the case then it will be difficult to judge as what indexes are
    really
    been queried by select statement. Any expert comment. It look like that even
    if
    we put the Index in monitoring mode, still this V$ view is not accurate to
    make
    decisio as if the index is really been used by select queries

    I am little confused here as I thought that I can assume that if this v$view
    has
    an entry for index, then it mean, the index is really been used sometime
    back

    TIA

    sanjay
    --- Mladen Gogala
    wrote:
    Sanjay Mishra wrote:
    Can we check as when Index was last used from the dictioanry tables.
    Index are not in Monitoring mode and Statisitic Level is TYPICAL in
    the Oracle 9i database

    TIA
    Sanjay



    __________________________________
    Do you Yahoo!?
    Yahoo! Mail - Helps protect you from nasty viruses.
    http://promotions.yahoo.com/new_mail
    --
    http://www.freelists.org/webpage/oracle-l
    V$SEGMENT_STATISTICS is populated nevertheless and it contains the
    values since the database startup.
    Also, V$SEGMENT_STATISTICS is very useful to determine candidates for
    a reorg. Here is the query that I use for that purpose:

    select owner,object_name,object_type,value
    from v$segment_statistics
    where statistic_name ='ITL waits' and value > 100 /

    --
    Mladen Gogala
    Oracle DBA
    Ext. 121


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 15, '05 at 12:59p
activeFeb 16, '05 at 1:39p
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase