index statistics

[Heiko]

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

[Putz Ronald]

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

[Dhana]

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

[Daniel Roy]

Index monitoring is only going to work if your version is at least 9i,
if I remember well.

Daniel

[Dhana]

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

[Heiko]

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

[Daniel Roy]

> > 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