DBA's XE Index Concerns

epost

Active Member
DBA\'s XE Index Concerns

Getting my first chance to dig around on an XE install and have a few
questions/concerns. I was a bit surprised to find so many indexes at first
on a "tables" tablespace. I soon found out that these are all primary keys.
A count of indexes shows that in addition to the 2000 or so PK indexes there
are another 2000+ indexes on the data tables in the indexes tablespace.
Well that happens to be about the same amount of indexes in B733.1. JDE was
already plagued by a number of completely redundant/useless indexes and now
it appears that they have added on to the problem. I've only been digging
around for a few minutes so maybe I have missed something (maybe a lot more
tables...?). For anyone who doesn't know, more indexes means slower inserts
and updates. My question is if anyone has moved these PK's to the index
tablespace? Next question is if anyone has ever done any tuning by removing
indexes that are redundant in that they offer almost no increase in
selectivity over existing indexes?

Thanks,
Ethan Post

------------------------------------------------------------------------------
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.

==============================================================================
 
Re: DBA\'s XE Index Concerns

Ethan,

I have in fact moved the indexes associated with the primary key constraints to a separate tablespace (on a separate disk). I have gone as far as utilizing some of Oracle's slicker index structures such as bitmap indexes. In some cases these measures have doubled or tripled performance.

I agree that there are an excessive number of indexes on many of the primary OneWorld transaction tables. JDE has delivered indexes to support the usage patterns of all their customers. Many of the indexes support specific UBE's. If I recall correctly, the F0911 table has something like 19 indexes (or is it 22). Most of these indexes may never be used in your environment. In other installations these same indexes will reduce sorting by covering all the fields in the Order By clause that the middleware generates for a particular UBE. With one client I was able to trim the F0911 down to 9 indexes. This improved insert performance without affecting UBE or grid query performance.

My favorite tool for trimming useless fat in OneWorld is the Oracle Expert. With a good sampling of workload over an average week, it can give you quite an accurate opinion of which indexes are useless. It also does a good job of spotting where indexes could be added for better performance.

Beware that some of these apparently useless indexes may play a part in the performance of UBE's that are only run during month-end processing. In some cases I have written scripts that built indexes to support month end reporting and then dropped them for the rest of the month.

Regards,

Justin Miller
[email protected]

working with B7332 and XE on AS/400, NT, Solaris and AIX
 
Re: DBA\'s XE Index Concerns

FYI Ethan is referring to an Oracle install.

Ethan,

this problem first appeared somewhere after SP7.1 under B733.1.
It definitely was there under SP11.3.
It becomes very apparent when you generate tables and/or indices (OL/OMW), or copy tables (R98403) - until you do something like that everything looks OK.
I had researched this problem 7 months ago and saw that it was supposed to be fixed under XE - according to the SARs on the business function at fault. The problem was even called in but attempting to get the support line person to understand it was like pushing water upstream.

I wrote a script to move (rebuild) indices onto the appropriate index tablespace - but it does not deal with identifying or eliminating redundant indices. If you're interested in the script I can post it.

Regards,

Larry Jones
[email protected]
OneWorld B733.1, SP 11.3
HPUX 11, Oracle SE 8.1.6
SandBox: OneWorld XE SP15.1
 
RE: DBA\'s XE Index Concerns

I assume you are an Oracle site! Remember OneWorld supports SQLServer and
AS400 databases as well. What may be redundant in Oracle terms is essential
for other database systems.

Oracle has the advantage of a whole lot of tools to analyse databases and
usage. If you have performance problems get you DBA involved and sort out
the indexes - OneWorld doesn't mind. Note you may find there are new indexes
which you need to add - our users seem to delight in running QBE's which
require non indexed fields, and some of these are essential.



OW733.3 Xe SP 14.2
Enterprise Server - Intel NT + Oracle 8.0.6
Client - Citrix TSE + Some 95 and NT PC's
 
RE: DBA\'s XE Index Concerns

Thanks for the great advice Justin. Steve Adams' has a script that will
help you locate redundant indexes also on his site at www.ixora.com.au. If
the first three leading columns of an index are the same on two indexes then
it is in all likelihood completely redundant to the cost based optimizer of
Oracle because the selectivity of columns A, B and C will almost always be
as adequate as A, B, C and D. So in these cases I would say you just need
to be sure that the additional columns don't really add a lot to selectivity
and your OK. If they do I would say it is safe to keep the index on more
columns and drop the one with less. The point is Oracle will do just fine
with one of these in 90% of the cases.

Thanks,
Ethan Post
 
RE: DBA\'s XE Index Concerns

i believe those mysterious _PK indexes are an added feature of 8.1.7. as
opposed to a JDE creation (though i imagine the PK's for each table are
defined by JDE).
regards
kieran fitzgerald
 
RE: DBA\'s XE Index Concerns

No, Oracle would name the index something like SYS#@($(@#&$ if a primary key
was defined but not named. In B733.1 JDE simply defined unique indexes to
enforce the primary key on each table. So the change is that they have made
these unique indexes primary keys in the DDL that creates the tables. The
trouble is that you need some additional SQL to define the storage settings
to put what is essentially a unique index on an indexes tablespace.

Thanks,
Ethan Post
 
Back
Top