RE: Reduce latch row cache objects with event 10089
Date: Wed, 18 Mar 2015 11:54:50 -0400
Message-ID: <045d01d06193$e0c1abe0$a24503a0$_at_rsiz.com>
+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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MARK BRINSMEAD
Sent: Wednesday, March 18, 2015 10:26 AM
To: jonathan_at_jlcomp.demon.co.uk
Cc: Petr.Novak_at_trivadis.com; oracle-l_at_freelists.org
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_at_jlcomp.demon.co.uk> wrote:
And some indexes may have been used when there was a better option.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Timur Akhmadeev [timur.akhmadeev_at_gmail.com] Sent: 18 March 2015 11:16
To: Petr.Novak_at_trivadis.com
Cc: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: Reduce latch row cache objects with event 10089
On Wed, Mar 18, 2015 at 9:08 AM, Petr Novak <Petr.Novak_at_trivadis.com<mailto:Petr.Novak_at_trivadis.com>> 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 18 2015 - 16:54:50 CET