Re: indexing
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 19 Feb 2013 11:39:49 -0700
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@xxxxxxxxx
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:
> Why not monitor indexes for usage? :)
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Taral Desai
> Sent: Tuesday, February 19, 2013 9:20 AM
> To: Brian Zelli
> Cc: oracle-l (oracle-l@xxxxxxxxxxxxx)
> 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@xxxxxxxxxxxxxxx>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.
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
