DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Index Monitoring Usage

  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Index Monitoring Usage

    Dear Friends

    I want to know how many queries use the index and how many
    times those queries were executed.

    I have read some time before that the information may be derived from v$sql_plan , v$sql .But how can i get those queries which uses my indexes. As i understand for getting this information i do not require index monitoring always on.

    Info : (DB: Oracle 9i, 9.2.0.1.0, OS: RHEL 2.1 AS)

    Regards

    Jimit

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Describe v$sql_plan and v$sql

    Quote Originally Posted by jimit View Post
    ...
    But how can i get those queries which uses my indexes.
    Setting index monitoring will NOT give you the information of which queries use those indexes (only if the index is being used), and you did read correctly, this information can be derived from v$sql_plan and v$sql without index monitoring.

    To find out this information, describe those "V$" views and write your query!


    Hint: Join V$SQL to V$SQL_PLAN on PLAN_HASH_VALUE
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2009
    Posts
    17
    You can also enable monitoring on specific index, but with this you can see only how many times your index was used.

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Does plan_hash_value column of v$sql refer to hash_value column of v$sql_plan?
    If it is so, then y do I observe this anomaly?

    SQL> SELECT * FROM V$SQL_PLAN A , V$SQL B WHERE A.HASH_VALUE=B.PLAN_HASH_VALUE;

    no rows selected



    futhermore, I investigated it as follows:

    SQL> ED
    Wrote file afiedt.buf

    1* SELECT * FROM V$SQLTEXT WHERE UPPER(SQL_TEXT) LIKE '%SELECT * FROM DEPARTMENTS%'
    SQL> /

    ADDRESS HASH_VALUE COMMAND_TYPE PIECE SQL_TEXT
    -------- ---------- ------------ ---------- --------------------------------------------------
    66E91224 3654847228 3 0 SELECT * FROM DEPARTMENTS

    SQL> SELECT * FROM V$SQL WHERE ADDRESS='66E91224' ;

    SQL_TEXT
    ----------------------------------------------------------------------------------------------
    SELECT * FROM DEPARTMENTS

    SQL> SELECT PLAN_HASH_VALUE,HASH_VALUE FROM V$SQL WHERE ADDRESS='66E91224' ;

    PLAN_HASH_VALUE HASH_VALUE
    --------------- ----------
    4167016233 3654847228

    SQL> SELECT * FROM V$SQL_PLAN WHERE HASH_VALUE='4167016233';

    no rows selected


    Did I go wrong anywhere???
    lucky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width