Tunning Indexes

[Jitendra Patel]

"Javier Villegas" <mask@impsat1.com.ar> wrote in message news:<YT1Sa.5756$Uc5.4404@fe06.atl2.webusenet.com> ...

> HI.
>
> I have tables that have a couple of indexes
>
> The table are growing
>
> I don´t know how can I improve the performance on the indexes (Defrag ,
> rebuild, etc)
>
> Could you tell me how can I do that ?
>
> thanks in advance

In most cases an index rebuild may clean-up space, but usually does
not have a big impact on performance.

You can use the "alter index xxx rebuild;" command to rebuild them.

[Thiru]

Before rebuilding the indexes, you may need to analyze the indexes and look
at the dictionary (ie dba_indexes,index_stats ) for clustering
factor,blevel(ie binary height) ,leaf nodes,deleted keys etc.
Clustering factor indicates how well the data is physically organised within
the index. IF the clustering actor approaches the number of leaf_blocks in
the index, then you are OK, but if it approaches the number of rows in the
table, then it means that more I/O needs to be performed to fetch the index
blocks ,unnecessarily and the index becomes a good choice for rebuild.
Blevel for most of the indexes,including large ones lies in the range 2-3
which indicates the number of reads to be performed to get to the key value.
Also look for the ratio of number of deleted rows to the total number of
rows. If you find that this is a considerable percentage and does not find
that this is not getting reused by additional inserts,you may need to
rebuild . In most cases,Oracle will reuse the space freed up the deletes.

If you do decide to rebuild the index,then you have the option of rebuilding
it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the
index creation. Also you may want to increase session SORT_AREA_SIZE to
speed up the sorts during the rebuild.

Hope this helps.
Thiru


"Javier Villegas" <mask@impsat1.com.ar> wrote in message
news:YT1Sa.5756$Uc5.4404@fe06.atl2.webusenet.com.. .

> HI.
>
> I have tables that have a couple of indexes
>
> The table are growing
>
> I don´t know how can I improve the performance on the indexes (Defrag ,
> rebuild, etc)
>
> Could you tell me how can I do that ?
>
> thanks in advance
>
>
>

[Thiru]

"Tanel Poder" <tanel@@peldik.com> wrote in message news:3f1acb3f_1@news.estpak.ee...
Hi!

> Clustering factor indicates how well the data is physically organised within
> the index. IF the clustering actor approaches the number of leaf_blocks in
> the index, then you are OK, but if it approaches the number of rows in the
> table, then it means that more I/O needs to be performed to fetch the index
> blocks ,unnecessarily and the index becomes a good choice for rebuild.

No, clustering factor shows how much are matching rows for index key values spread out in data blocks. No index rebuild will reduce clustering factor. You could of course rebuild the table and put rows with same key values to adjacent blocks, but normally tables have more than one index anyway, so you'll always have indexes with bad clustering factor. For monotonically increasing columns such often are primary keys or dates the clustering factor is usually better, but that also depends on whether rows are inserted to the end of the table or are spread in (somewhat) empty blocks inside the table.

"Oops. I meant Clustering factor indicates how well the data(ie matching rows) is physically organized within TABLE,relative to the index . And again, table rebuilds could improve the clustering factor of indexes,if they have single indexes ...not index rebuilds as I mentioned earlier,my bad "

A little (and very easy to do) demonstration:

C:\>sqlplus admin/admin

SQL*Plus: Release 9.2.0.1.0 - Production on P Jul 20 17:34:28 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create table t as select * from sys.obj$;

Table created.

SQL> create index i on t(obj#);

Index created.

SQL> create index j on t(name);

Index created.

SQL> analyze index i compute statistics;

Index analyzed.

SQL> analyze index j compute statistics;

Index analyzed.

SQL> select index_name, leaf_blocks, num_rows, clustering_factor from user_indexes;

INDEX_NAME LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------------------ ----------- ---------- -----------------
I 65 29764 398
J 145 29764 13749

2 rows selected.

We see that clustering factor is quite low for index on obj#. The obj# column value comes from sequence and in this fresh test database not much objects are deleted & created, that's why most inserts to this table go to the end of segment and are in same "order" than key inserts to index. But the CF for index on name column is quite high, since object names are quite random from string comparision point of view.

Now, let's see another example, where name column would be kind of monotonically increasing column.

SQL> create table t2 as select * from t order by name;

Table created.

SQL> create index i2 on t2(obj#);

Index created.

SQL> create index j2 on t2(name);

Index created.

SQL> analyze index i2 compute statistics;

Index analyzed.

SQL> analyze index j2 compute statistics;

Index analyzed.

SQL> select index_name, leaf_blocks, num_rows, clustering_factor from user_indexes;

INDEX_NAME LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------------------ ----------- ---------- -----------------
I 65 29764 398
I2 65 29764 14810
J 145 29764 13749
J2 145 29764 710

4 rows selected.

SQL>

As you see, now the index on obj# has quite bad clustering factor. Since this index fas just built, it is in perfect shape and no rebuild can make it better.

Thus, clustering factor should not affect decision for rebuilding indexes.

In theory, it might be useful to order data in table according to index which is most queried with *range scans*. Unique scans don't matter, because for accessing table data only one block is read anyway (if not migrated or chained row). But for that, it might be reasonable to use IOT's instead and forget about clustering factors..

> Blevel for most of the indexes,including large ones lies in the range 2-3
> which indicates the number of reads to be performed to get to the key value.
> Also look for the ratio of number of deleted rows to the total number of
> rows. If you find that this is a considerable percentage and does not find
> that this is not getting reused by additional inserts,you may need to
> rebuild . In most cases,Oracle will reuse the space freed up the deletes.

This is usually a rare case where index level is reduced by rebuild. The most common reason would be a table with monotonically increasing column where lots of data is inserted and 99% of data is deleted, leaving few entries in every leaf block. That way Oracle can't use leaf blocks for other values. But in this case you should use alter index coalesce, which can be done completely online (online rebuild actually still has to acquire exclusive lock on index AFAIK, but for short time, when doing final sync of index with the temporarily created materialized view log). Index coalesce does just take two blocks with adjacent key values and merges them, leaving one free for other key values (happens only if merged block will contain less data than PCTFREE allows).
Coalescing is much faster, since it only traverses index and merges blocks, rebuild requires an expensive sort operation.

>
> If you do decide to rebuild the index,then you have the option of rebuilding
> it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the
> index creation. Also you may want to increase session SORT_AREA_SIZE to
> speed up the sorts during the rebuild.

Yep, plus using PARALLEL if you got fast IO (and multiple CPUs).

Also, don't crosspost, c.d.o.server is right place for this kind of questions.

" Who was doing crosspost ? "

Cheers,
Tanel.

>
> Hope this helps.
> Thiru
>
>
> "Javier Villegas" <mask@impsat1.com.ar> wrote in message
> news:YT1Sa.5756$Uc5.4404@fe06.atl2.webusenet.com.. .

> > HI.
> >
> > I have tables that have a couple of indexes
> >
> > The table are growing
> >
> > I don´t know how can I improve the performance on the indexes (Defrag ,
> > rebuild, etc)
> >
> > Could you tell me how can I do that ?
> >
> > thanks in advance
> >
> >
> >

>
>

[Thiru]

see my previous reply ...I meant physical ordering within Table and not
Index !!..


"Quarkman" <quarkman@myrealbox.com> wrote in message
news:oprsmj5zb8r9lm4d@haydn...

> On Sun, 20 Jul 2003 11:31:11 -0400, Thiru <t@t.com> wrote:
>

> > Before rebuilding the indexes, you may need to analyze the indexes and
> > look
> > at the dictionary (ie dba_indexes,index_stats )
> > for clustering
> > factor,blevel(ie binary height) ,leaf nodes,deleted keys etc.
> > Clustering factor indicates how well the data is physically organised
> > within
> > the index.

>
> No it doesn't. It tells you whether the way things are ordered in the

index

> is very similar or totally dissimilar to the way the rows are ordered in
> the table. That is, if visit one index leaf block, how many table blocks
> will I then have to visit? If table data is scattered all over the place

in

> no apparent order, but the index is in (say) ascending numeric order, then
> one leaf node will likely point to dozens of table blocks.
>

> > IF the clustering actor approaches the number of leaf_blocks in
> > the index, then you are OK, but if it approaches the number of rows in
> > the
> > table, then it means that more I/O needs to be performed to fetch the
> > index
> > blocks ,unnecessarily

>
> Wrong. If it approaches the number of rows in the table, it means that
> there is no correspondence whatsoever between the way stuff is stored in
> the table and the way it is ordered in the index. That's a good indication
> that the *table* should be re-organised, not the index (as in 'create

table

> empcopy as select * from emp', 'truncate table emp', 'insert into emp
> select * from empcopy ordered by ename').
>

> > and the index becomes a good choice for rebuild.

>
> See above. Bad clustering means your table is disorganised, not your

index.

> And if you've got multiple indexes on the one table, then no amount of
> table re-organisation is going to help you. (If you've an index on empno
> AND a separate one on ename, then you're unlikely to find an 'order by'
> clause when re-organising the table which gets both indexes' clustering
> factors down to 'good' levels).
>

> > Blevel for most of the indexes,including large ones lies in the range

2-3

> > which indicates the number of reads to be performed to get to the key
> > value.

>
> You'd have to have a completely deranged index before the height of the
> index becomes something that can be 'fixed' by a rebuild. It's unusual to
> find height becoming progressively bigger and bigger anyway.
>

> > Also look for the ratio of number of deleted rows to the total number of
> > rows. If you find that this is a considerable percentage and does not
> > find
> > that this is not getting reused by additional inserts,you may need to
> > rebuild . In most cases,Oracle will reuse the space freed up the

deletes.

>
> Absolutely true. Fundamentally, it's rather unusual *ever* to really need
> to rebuild an index. There are always exceptions, of course, but generally
> it's a lot of old hard work for no discernible benefit, particularly since
> Oracle is generally pretty good at re-using index space as you describe.
>

> >
> > If you do decide to rebuild the index,then you have the option of
> > rebuilding
> > it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the
> > index creation. Also you may want to increase session SORT_AREA_SIZE to
> > speed up the sorts during the rebuild.

>
> Also mostly good advice here. Except that an online rebuild of an index
> isn't terribly "online". It requires an exlcusive lock on the table to
> start, after which it relinquishes it. And then it takes another exclusive
> lock to finish. If it can't take those exclusive locks, then the session
> where you're trying to rebuild the index will 'hang', and others will

start

> queueing up behind it. And it won't be able to take the lock without this
> hanging behaviour if the table is subject to almost constant DML.
>
> So you end up with the odd paradox: when would you like to be able to do

an

> online rebuild? When the table is subject to lots of DML. When won't an
> online rebuild be a particularly "online" operation? Er, when the table is
> subject to lots of DML.
>
> If "online-ness" is really a requirement, have you considered 'alter index
> xxxx coalesce'? Coalescing is inherently online, and there are no

exclusive

> locks taken on the table at all.
>
> ~QM

[Thiru]

I meant Clustering factor indicates how well the data(ie matching rows) is
physically ordered within TABLE,relative to the index
...One slip ..and everybody starts pounding. :-) I like this.,.

"Quarkman" <quarkman@myrealbox.com> wrote in message
news:oprsmj5zb8r9lm4d@haydn...

> On Sun, 20 Jul 2003 11:31:11 -0400, Thiru <t@t.com> wrote:
>

> > Before rebuilding the indexes, you may need to analyze the indexes and
> > look
> > at the dictionary (ie dba_indexes,index_stats )
> > for clustering
> > factor,blevel(ie binary height) ,leaf nodes,deleted keys etc.
> > Clustering factor indicates how well the data is physically organised
> > within
> > the index.

>
> No it doesn't. It tells you whether the way things are ordered in the

index

> is very similar or totally dissimilar to the way the rows are ordered in
> the table. That is, if visit one index leaf block, how many table blocks
> will I then have to visit? If table data is scattered all over the place

in

> no apparent order, but the index is in (say) ascending numeric order, then
> one leaf node will likely point to dozens of table blocks.
>

> > IF the clustering actor approaches the number of leaf_blocks in
> > the index, then you are OK, but if it approaches the number of rows in
> > the
> > table, then it means that more I/O needs to be performed to fetch the
> > index
> > blocks ,unnecessarily

>
> Wrong. If it approaches the number of rows in the table, it means that
> there is no correspondence whatsoever between the way stuff is stored in
> the table and the way it is ordered in the index. That's a good indication
> that the *table* should be re-organised, not the index (as in 'create

table

> empcopy as select * from emp', 'truncate table emp', 'insert into emp
> select * from empcopy ordered by ename').
>

> > and the index becomes a good choice for rebuild.

>
> See above. Bad clustering means your table is disorganised, not your

index.

> And if you've got multiple indexes on the one table, then no amount of
> table re-organisation is going to help you. (If you've an index on empno
> AND a separate one on ename, then you're unlikely to find an 'order by'
> clause when re-organising the table which gets both indexes' clustering
> factors down to 'good' levels).
>

> > Blevel for most of the indexes,including large ones lies in the range

2-3

> > which indicates the number of reads to be performed to get to the key
> > value.

>
> You'd have to have a completely deranged index before the height of the
> index becomes something that can be 'fixed' by a rebuild. It's unusual to
> find height becoming progressively bigger and bigger anyway.
>

> > Also look for the ratio of number of deleted rows to the total number of
> > rows. If you find that this is a considerable percentage and does not
> > find
> > that this is not getting reused by additional inserts,you may need to
> > rebuild . In most cases,Oracle will reuse the space freed up the

deletes.

>
> Absolutely true. Fundamentally, it's rather unusual *ever* to really need
> to rebuild an index. There are always exceptions, of course, but generally
> it's a lot of old hard work for no discernible benefit, particularly since
> Oracle is generally pretty good at re-using index space as you describe.
>

> >
> > If you do decide to rebuild the index,then you have the option of
> > rebuilding
> > it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the
> > index creation. Also you may want to increase session SORT_AREA_SIZE to
> > speed up the sorts during the rebuild.

>
> Also mostly good advice here. Except that an online rebuild of an index
> isn't terribly "online". It requires an exlcusive lock on the table to
> start, after which it relinquishes it. And then it takes another exclusive
> lock to finish. If it can't take those exclusive locks, then the session
> where you're trying to rebuild the index will 'hang', and others will

start

> queueing up behind it. And it won't be able to take the lock without this
> hanging behaviour if the table is subject to almost constant DML.
>
> So you end up with the odd paradox: when would you like to be able to do

an

> online rebuild? When the table is subject to lots of DML. When won't an
> online rebuild be a particularly "online" operation? Er, when the table is
> subject to lots of DML.
>
> If "online-ness" is really a requirement, have you considered 'alter index
> xxxx coalesce'? Coalescing is inherently online, and there are no

exclusive

> locks taken on the table at all.
>
> ~QM