Indexing Question 1

[AJCG]

I have a few tables, and a few indexes.... I remember an Oracle tool for checking out indexes and giving you a report on whether they are useful, and giving you suggestions on additional ones.... Anyone think of another program for this?

 

[nbateman]

As a rule of thumb, it is usually best to add indexes only to solve a specific performance issue. If they solve the issue, they are useful.

I know this isn't a tool, but it is an approach to your problem that I would expect to work better than a tool (assuming someone is able to suggest one - which they haven't yet).

Indexes are also not all positive. Adding one MAY speed something up, but it will also DEFINITELY slow down inserts, administration, and utilise additional disk space.

My suggestion then would be to take off any that you aren't sure are serving a purpose, and experiment with any that you think might.

 

[Scunningham99]

Also.

To check if your index is being used use:

Alter index my_index monitoring usage;

then when the index has been used the date will be inserted in to view v$object_usage.

If at any time you wish to stop monitoring the index use command

alter index my_index nomonitoring usage;


an example of output from : select * from v$object_usage
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
INDEX1 TEST YES NO 06/04/2003 13:39:44

which shows 'YES' in monitoring column and 'NO' in use column stating that it has not being used.

Sy UK

 

[BJCooperIT]

Thanks for the tip Sy!

^{Everywhere is within walking distance if you have the time. ~Steven Wright}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}

 

[ddrillich]

Hi Scunningham99,

I’m using Oracle 8i and the query -
select * from v$object_usage
results in ORA-00942: table or view does not exist

Any idea?
Dan

 

[sybaseguru]

I believe the dynamic view v$object_usage was part of 9i release. So unfortunately you will not be able to use it in 8i. Have a look at the site

http://tahiti.oracle.com/pls/db901/db901.show_mindex?letter=V

 

[Scunningham99]

sorry... assumed 9i because we were in an 9i forumn

Sy UK

 

[AJCG]

Hi again, just me... OK

Too the first poster here (nbateman) Yep I know been doing that one for a few years, just getting lazy I suppose and trying to find a way round it....

v$object_usage yep been using that too to keep an eye on a few indexes.. Removed six useless ones so far....

Thanks for everyones reply

Adam

 

[joe33067]

We have an issue using Oracle 9a Client connecting to a database on a SUN box. We have noticed, that clients installed on W2K Adv Serv (SP3 or SP4) exhibit latency when issuing a TNSPING, versus when the client is installed an a Windows XP machine. We have verified the results, and they are consistent on different machines. Is this latency problem on Windows 2K a known issue? If so, is there a work around?

 

[joe33067]

For above - sorry - should have been a new thread...