Dima or SantaMufasa Please ... HELP ...

[sbix]

Hi folks,
A customer of mine has an Oracle DB on Sun Solaris coming out from a conversion from DB2 on OS/390.
The Sun people performed the conversion on a "one to one" base, without seeking for any optimizing goal.
So I have a very huge table, which could be also partitioned, with 5 big indexes on her.
This indexes are equipotential ... meaning each of them has the same number of rows of the table ... so they are pratically five PK indexes.
I was looking the I/O for each datafile and I saw writes are 5 times the reads for the datafile holding those indexes.
To me this sounds like:
- Nobody uses these indexes for search
- These indexes are only accessed to be kept updated from Oracle, because I believe Oracle needs to read a leaf of an index in order to change the value of this leaf
Do you think I am right?
Do you have any suggestions?

 

[sbix]

No suggestions? Not a clue? Not an idea?

 

[sem]

I think that index may be cached thus there's no need to read it from disk each time upon request. Then even indexes not used for search may serve UNIQUE constraints imposed by some business requirements. Do you plan to drop them?

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Again me. Found an old thread, may be helpful thread759-523185

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Thanks Dima,
Yes I thought something like ... but should be something like an equity rate between I/O for read on indexes and on data. Keep in mind that you need more access on an index to get one key ... Furthermore I gave a look to v$system_events and I noticed that rows returned by index scan are 28% of the amount returned by full-table scan.
This because on this system Oracle finds too much economic the full table scan usage. Probably I'am looking the story from the wrong view point .... Probably the queries aro so poorly selective that Oracle has no choice on executing them. ....
Don't know ....

 

[sem]

but should be something like an equity rate between I/O for read on indexes and on data

Please, elaborate on this statement as I can not undertand the idea.

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Well, I have a DB cache hit ratio about 91%.
Now, there's no evidence that cached info are mainly index leaves and not data-block or vice-versa, we have to suppose the percentage of them are almost equally distribuited in cache.
So theoretically the percentage of I/Os should be in the same scale ..... (perhaps) ...
By other hand I have a lot of full table scan, so PROBABLY only a few data block remain cached and this should drive to the conclusion you arrived.
Giving another look to the v$system_events I can see Oracle finds too much economic to perform a full table scan versus an index scan in terms of avg time waited for the two solutions.
By the moment only the 28% of data rows are returned by an index scan so, I have to stimate if is convenient to kill partially unused indexes, in order to save all the I/Os needed to maintain them and loose some of the advantages gained with those indexes, or to encourage the indexes usage by adjusting some optimizer tuning parameters

 

[sem]

I'm sorry but your idea reminds me a story about the probability to meet a dinosaur as 50%: either yes or not.
How can you compare IO produced by reading rows of arbitrary (in some cases quite large) size against fixed-length ROWID (or quite limited index) size?

BTW how did you use v$system_event view to estimate the difference?

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

You're absolutely right for what concerning the weigth of an average data row against an average key pointing it in the DB cache, but nobody has the formula to know the truth about the "DB cache values composition".
You can stimate how much costs a full table scan or an index scan in your system by doing:
select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read';

Scattered read are Full Table Scan
Sequential read are index scan

 

[sem]

Ok, so you need 1:1 distribution for the sake of harmony only? Did you take into account query complexity? I mean using full scans against index scans for the same queries? You may execute full scans on small tables because you really need all their data to display for user choice. From the other hand sometimes you need to select single row from a rerally huge table by index. So tell me please what's wrong with that system? I'm pretty sure that the answer would be "nothing".

In general I suppose that your investigations are just wasting your time, sorry.

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Dima,
pls ... I don't need a 1:1 distribution, I am not looking for a more equal rate of I/O distribution ... which is a topic who cannot interest nobody.
I am only guessing if indexes in this DB are conveniently used, or ... as I fear, they are only a big load for the system because Oracle PROBABLY is not using them for query but have to maintain them.
What's wrong in the system? Perhaps a really huge amount of read on the "DATA" datafiles, really huge amount of write in the INDEX datafiles .... an almost irrilevant amount of read in the INDEX datafile?
I would like to reduce the amount of readings in the DATA, using a little bit more the indexes ... if this is not feasible, I would to understand what will happen if I cut off the indexes....
There wasn't irony on my words ... nor sarchasm ... as I see in your answers.
Sorry for bother you

 

[sem]

Have you aleady tried to

alter index <index> monitoring usage;

from Thread759-523185 mentioned above? I've got no feedback. How about serving uniqueness? From your words I've concluded that you're trying to get rid of unique indexes, am I correct? Then, when the fast feedback is a must for a system, it's worth to spent some microseconds during insert or update and not to wait for query results for hours. Yes, probably an average speed of your system increases, but average numbers don't interest an end-user. That's why I don't encourage your investigations. Besides you didn't specified any performance issues and a rule of thumb is: don't ruin a wall if you don't know what was it built for.

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Yes, I see what you mean, indexes could be there also to guarantee constraints and not only for speed-up reasons.
Obviously those indexes should not be touched .. as like the really used ones.
Nobody is going to run a massive DROP INDEX, I am only interrogating myself about the I/O's behaviours.
Are there any information? I have only a few points which are saying "System is spending really a lot of resources in maintaining indexes, now ... are they really used?"
If I look to the READ side I should say NO .. .BUT ... as you wisely said, probably they are so efficiently cached to result in an "look like out balance".
The % of rows returned from index scan are the 28% of the one returned from full table scan, so this makes me say "Well SOME indexes are used and are very efficiently cached", NOW ... the real questions are "are those indexes the same which cost so much to the system in order to maintain them?", "are there any chance to improve system performance, by encourage Oracle to use a little more indexes?", "are there any chance to improve overall system performance by killing some unused indexes".
You gave me an "almost answer" suggesting to investigate the V$OBJECT_USAGE view.
The only problem with that view is it doesn't returns the number of usage in a lapse of time, but only a flag YES or NOT, which could be not very significant ....
So probably I have to run :
ALTER INDEX BLAH.BLAH MONITORING USAGE;
SELECT USAGE (or whatever) from V$OBJECT_USAGE WHERE INDEX_NAME = 'BLAH';
ALTER INDEX BLAH.BLAH NOMONITORING USAGE;

A lot of time during the day in order to collect a something meaningful statistics.

Thanks for you help