RE: Reduce latch row cache objects with event 10089

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <mark.brinsmead@xxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Wed, 18 Mar 2015 11:54:50 -0400

+1 to the idea of this bit of the thread to logically eliminate not only 
completely unused indexes, but also manipulating for net reduction the set of 
indexes to take advantage of combinations that reduce the total number of 
indexes without damaging overall access “much” (which includes possibly 
improving access).

 

When I’ve seen 20+ indexes on a table it usually involves many single column 
indexes. Sometimes these are at least somewhat needed for constraint support. 
Sometimes these are an artifact of old designs from when Oracle had a combine 
indexes access plan (which in my experience never was more efficient than a 
multicolumn index with b*tree indexes, though it could be at least in theory).

Sometimes it is just a plain misunderstanding of how multi-column indexes work.

 

If many single column indexes are in play quite often “a” will win over “b” 
sometimes and “b” will win over “a” sometimes, but “ab” or “ba” might win or 
tie all the time. Even if there is minor degradation with, say, “ab”,  for some 
queries the net system might function better with “ab” instead of “a” and “b”.

 

Beyond that, since the introduction of skip scan, and index does not 
necessarily become much more inefficient simply because its leading column to 
not a predicate.

 

Unless there is a huge difference in index size for a query access path that is 
used sufficiently to care about (and using the notation each letter is a 
column), you can probably immediately eliminate multicolumn indexes like a, ab, 
abc, abc…z-1, where an index abc…z exists.

If indexes abcd and abdc exist, likely you can live with one or the other. 
There is even a chance you can live with one of even so short as the pair ab 
and ba.

If multiple indexes exist mostly to serve the purpose of skipping table access 
for a few different queries, sometimes this is an artifact of a good idea 
replicated without looking for a close match. For example, if the predicate 
needs are satisfied by ab (for multiple queries), you might have abcdef, 
abcdefg, abghi, and abj used by four different queries (avoiding table access, 
each picking the smallest index that served its needs. Very likely all four 
would still be very good with just abcdefhij instead of the other four.

 

Good luck. All the practical examples would fill a tome. Maybe you really do 
need all your indexes. But I hope I’ve unbundled the bullseye shorthand of JL 
and Mark Brinsmead below.

 

mwf

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of MARK BRINSMEAD
Sent: Wednesday, March 18, 2015 10:26 AM
To: jonathan@xxxxxxxxxxxxxxxxxx
Cc: Petr.Novak@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Reduce latch row cache objects with event 10089

 

Or an index may be used where there is another very-nearly-as-good option.

The fact that all the indexes are being used does not necessarily indicate that 
all indexes are actually needed.  :-)

 

On Wed, Mar 18, 2015 at 7:32 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 
wrote:


And some indexes may have been used when there was a better option.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle

________________________________________
From: Timur Akhmadeev [timur.akhmadeev@xxxxxxxxx]
Sent: 18 March 2015 11:16
To: Petr.Novak@xxxxxxxxxxxx
Cc: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: Reduce latch row cache objects with event 10089


On Wed, Mar 18, 2015 at 9:08 AM, Petr Novak 
<Petr.Novak@xxxxxxxxxxxx<mailto:Petr.Novak@xxxxxxxxxxxx>> wrote:
I check continually index usage in shared pool and AWR, there are only  1-2 
index candidates for elimination.

Do not need to look into AWR for that. Start with listing indexes & indexed 
columns instead, just looking at what is indexed + at avg_data_blocks_per_key. 
Very often people introduce redundant & unnecessary/inefficient indexes which 
could be logically eliminated.

--
Regards
Timur Akhmadeev

--
//www.freelists.org/webpage/oracle-l



 

Other related posts: