Re: Tunning Indexes
From: Tanel Poder <tanel_at__at_peldik.com>
Date: Sun, 20 Jul 2003 20:02:54 +0300
Message-ID: <3f1acb3f_1_at_news.estpak.ee>
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
> 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.
Date: Sun, 20 Jul 2003 20:02:54 +0300
Message-ID: <3f1acb3f_1_at_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.
> 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.
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
------------------------------ ----------- ---------- -----------------
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
------------------------------ ----------- ---------- -----------------
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.
> 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.
Cheers,
Tanel.
>
> Hope this helps.
> Thiru
>
>
> "Javier Villegas" <mask_at_impsat1.com.ar> wrote in message
> news:YT1Sa.5756$Uc5.4404_at_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
> >
> >
> >
>
>
Received on Sun Jul 20 2003 - 19:02:54 CEST
> Hope this helps.
> Thiru
>
>
> "Javier Villegas" <mask_at_impsat1.com.ar> wrote in message
> news:YT1Sa.5756$Uc5.4404_at_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
> >
> >
> >
>
>