Re: indexing
Date: Tue, 19 Feb 2013 11:39:49 -0700
Message-ID: <5123C6F5.2030803_at_evdbt.com>
Brian,
I agree with Jackie. Technical reasoning is useful, but often becomes just one position in a heated technical argument moderated by a disinterested non-technical person, but hard facts win the day and pave the way for one's own position.
Monitor the indexes either by using ALTER INDEX ... MONITORING USAGE command and then querying V$OBJECT_USAGE afterwards, or (if you are licensed for AWR) query the DBA_HIST_SQL_PLAN for OBJECT_NAME IN (/index-name-list/) and view the SQL text captured. The former mechanism (i.e. index monitoring) is designed for identifying unused indexes and is guaranteed accurate (see Tim Hall's excellent post at "http://www.oracle-base.com/articles/10g/index-monitoring.php"), and the latter suggestion (i.e. mining AWR data) is just a quick 'n' dirty way to detect index usage, but is based on sampled data and is not guaranteed accurate.
Once unused indexes have been empirically proven, then you can document the cost in terms of space (by querying DBA_SEGMENTS) and estimate the relative cost to INSERT, UPDATE, and DELETE performance by constructing a copy of the table and it's indexes and using bogus workload generated within a PL/SQL procedure performing a fixed number of INSERT, UPDATE, and DELETE operations against the copied table. By running the PL/SQL procedure as a baseline, then removing the unused indexes and re-running the PL/SQL procedure, you should be able to provide a good estimate of the impact of the useless and unused indexes on transactions.
It would be ideal if the impact in terms of space and transactional processing was impressive, but if it is a small and static table, be prepared for relatively unimpressive justification. In this situation, be prepared for a negative decision to your proposal, but at least you'll have the techiques in place for the same situation involving a large and volatile table.
Hope this helps...
-- Tim Gorman consultant -> Evergreen Database Technologies, Inc. postal => PO Box 352151, Westminster CO 80035-2151 USA web/blog => http://www.EvDBT.com/ email => Tim_at_EvDBT.com mobile => +1 (303) 885-4526 twitter => timothyjgorman president -> Rocky Mtn Oracle Users Group (www.RMOUG.org) board -> Oracle Developers Tools Users Group (www.ODTUG.com) advisor -> Northern California Oracle Users Group (www.NoCOUG.org) secretary -> Project SafeGuard (www.PSGHelps.org) member -> OakTable Network (www.OakTable.net) Oracle ACE Director (www.oracle.com/technetwork/community/oracle-ace) Lost Data? => www.ora600.be/ for info about DUDE... On 2/19/2013 9:34 AM, Jackie Brock wrote:Received on Tue Feb 19 2013 - 19:39:49 CET
> Why not monitor indexes for usage? :)
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Taral Desai
> Sent: Tuesday, February 19, 2013 9:20 AM
> To: Brian Zelli
> Cc: oracle-l (oracle-l_at_freelists.org)
> Subject: Re: indexing
>
> Well. this might be too much but still depends. You need to provide some more information Like 1. How many approximately rows this table has 2. No. of columns 3. How frequently int's updated/deleted or data is inserted.
> 4. What kind of indexes it has and what are the layout
>
> And most importantly is you need to ask Is this index is going to help some query if so then can it be combined with other index.
>
> So, it's not easy to answer this question without any information.
>
>
> On Tue, Feb 19, 2013 at 10:08 AM, Zelli, Brian
> <Brian.Zelli_at_roswellpark.org>wrote:
>
>> I have developers who are able to create indexes thru their
>> application builder. On one table they have 30 indexes. I've often
>> said to them that they are creating too many but I get overruled by their manager. Are they
>> creating too many? Can oracle handle many, many indexes? Is there a way
>> I can at least minimize any depreciation of performance if they are
>> going to be allowed to do this?
>> ciao,
>> Brian
>>
>>
>>
>>
>> This email message may contain legally privileged and/or confidential
>> information. If you are not the intended recipient(s), or the
>> employee or agent responsible for the delivery of this message to the
>> intended recipient(s), you are hereby notified that any disclosure,
>> copying, distribution, or use of this email message is prohibited. If
>> you have received this message in error, please notify the sender
>> immediately by e-mail and delete this email message from your computer. Thank you.
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-l