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]

Re: index statistics

Hy!

Try:
select * from all_indexes

Adios


"Heiko" <heiko@technolo gie-management.nets chrieb im Newsbeitrag
news:7bc3b1a6.0 404270125.5ac10 9b3@posting.goo gle.com...

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]

Re: index statistics

Hi,

You have to monitor the index using
"alter index <index_namemoni toring 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.


SQLconn scott/tiger;
Connected.
SQLselect * from tab;

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

SQL>
SQL>
SQL>
SQL>
SQLselect table_name,inde x_name from user_indexes;

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

SQLselect * from v$object_usage;

no rows selected

SQLalter index PK_EMP monitoring usage;

Index altered.

SQLselect * 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_MONITORIN G END_MONITORING
------------------------------ ------------------------------ --- ---
------------------- ----------
PK_EMP EMP YES NO
04/28/2004 19:43:42

SQLselect * from emp where empno = 7788;

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

SQLselect * from v$object_usage;

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



Rgds
Dhana
"Putz Ronald" <rputz@etm-ag.comwrote in message news:<108306512 5.475962@newsma ster-03.atnet.at>...

Hy!
>
Try:
select * from all_indexes
>
Adios
>
>
"Heiko" <heiko@technolo gie-management.nets chrieb im Newsbeitrag
news:7bc3b1a6.0 404270125.5ac10 9b3@posting.goo gle.com...

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]

Re: index statistics

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

Daniel

[Dhana]

Re: index statistics

Hi,

Yap, this will work only in 9i database.

Rgds,
Dhana

danielroy10junk @hotmail.com (Daniel Roy) wrote in message news:<3722db.04 04281519.7d1395 6@posting.googl e.com>...

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

[Heiko]

Re: index statistics

danielroy10junk @hotmail.com (Daniel Roy) wrote in message news:<3722db.04 04281519.7d1395 6@posting.googl e.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]

Re: index statistics

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