By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

index statistics

P: n/a
Hello,

is there any way (v$-view) to get informaion about how often an index hast been
used since of starting the Database?

Thanks for help
Heiko
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hy!

Try:
select * from all_indexes

Adios
"Heiko" <he***@technologie-management.net> schrieb im Newsbeitrag
news:7b**************************@posting.google.c om...
Hello,

is there any way (v$-view) to get informaion about how often an index hast been used since of starting the Database?

Thanks for help
Heiko

Jul 19 '05 #2

P: n/a
Hi,

You have to monitor the index using
"alter index <index_name> monitoring usage;" and see the
V$object_usage view.

Read the example below and look for "used" column. If the index was
used
the column contains "YES".

In my first query, I used ename column in my where clause which is not
part of index but in second one, I used empno column which is part of
index.
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL>
SQL>
SQL>
SQL>
SQL> select table_name,index_name from user_indexes;

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
DEPT PK_DEPT
EMP PK_EMP

SQL> select * from v$object_usage;

no rows selected

SQL> alter index PK_EMP monitoring usage;

Index altered.

SQL> select * from emp where ename='SCOTT';

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- ---
------------------- ----------
PK_EMP EMP YES NO
04/28/2004 19:43:42

SQL> select * from emp where empno = 7788;

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- ---
------------------- ----------
PK_EMP EMP YES YES
04/28/2004 19:43:42

Rgds
Dhana
"Putz Ronald" <rp***@etm-ag.com> wrote in message news:<10***************@newsmaster-03.atnet.at>...
Hy!

Try:
select * from all_indexes

Adios
"Heiko" <he***@technologie-management.net> schrieb im Newsbeitrag
news:7b**************************@posting.google.c om...
Hello,

is there any way (v$-view) to get informaion about how often an index hast

been
used since of starting the Database?

Thanks for help
Heiko

Jul 19 '05 #3

P: n/a
Index monitoring is only going to work if your version is at least 9i,
if I remember well.

Daniel
Jul 19 '05 #4

P: n/a
Hi,

Yap, this will work only in 9i database.

Rgds,
Dhana

da*************@hotmail.com (Daniel Roy) wrote in message news:<37***********************@posting.google.com >...
Index monitoring is only going to work if your version is at least 9i,
if I remember well.

Daniel

Jul 19 '05 #5

P: n/a
da*************@hotmail.com (Daniel Roy) wrote in message news:<37***********************@posting.google.com >...
Index monitoring is only going to work if your version is at least 9i,
if I remember well.

Daniel


Hello,
thanks for help!
Yes we use Oracle 9i.
Now we can look which index has already been used, but not, how often.
Is ther any way to get this information too.

thanks Heiko
Jul 19 '05 #6

P: n/a
> > Index monitoring is only going to work if your version is at least 9i,
if I remember well.

Daniel


Hello,
thanks for help!
Yes we use Oracle 9i.
Now we can look which index has already been used, but not, how often.
Is ther any way to get this information too.

thanks Heiko


To get a "ballpark" idea of how often an index is used, I'd
periodically take a look at the contents of the shared pool
during/after periods of high activity (hint for you: column
OBJECT_NAME of dynamic view V$SQL_PLAN).

HTH

Daniel
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.