-
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
-
Describe v$sql_plan and v$sql
Originally Posted by jimit
...
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
-
You can also enable monitoring on specific index, but with this you can see only how many times your index was used.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|