Home » RDBMS Server » Server Administration » The index rebuild "myth"
The index rebuild "myth" [message #160041] Wed, 22 February 2006 22:54 Go to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've heard lots of rumors about indexes requiring a rebuild for performance, and seen equally as many arguments that the rumors are rubbish.

I have a fast refreshable Materialized View with about 80,000 rows, 44 columns, which is the inner join using FK->PK of 22 tables.

Fast refresh works fine most of the time, but several of the 22 tables are very small, and the join key in the larger tables has low cardinality. When I update a single row in one of these tables, it can cause the refresh of 10,000+ rows in the MV. Fast Refresh resolves these changes not with UPDATE, but with DELETE/INSERT.

Just today, I found one such refresh going on, but it had almost stopped. Tracing it, I found it in the DELETE phase, with one row deleting every 10-30 seconds. The Fast Refresh was driving off a MV Log on one of the base tables, and accessing the MV using an indexed column containing the rowid of the base table.

Concerned that index may be the problem, I rebuilt it. Lo-and-behold, the Fast Refresh is Fast again. Since this table cops a lot of DELETEs and INSERTs, it is possible (nay, likely) that branched index blocks are now quite sparsely populated; the rebuild could have compacted it a bit (a lot?).

Questions:
1) Was it likely that the index was the problem, and that it was fixed with a rebuild?
2) If so, was it likely that the problem was caused by lots of previous deletes/inserts? How can an index get that screwed up? It was pretty drastically bad performance.
3) How can you tell IN ADVANCE that an index is screwed up so that you can rebuild it before it causes trouble?

_____________
Ross Leishman
Re: The index rebuild "myth" [message #160128 is a reply to message #160041] Thu, 23 February 2006 07:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If i understand correctly, the myth is about Rebuilding indexes as a scheduled Job regularly to magically fix all the issues.
Did you look into the clustering factor?
If the index clustering factor closer to the total number of blocks in table , it may be good. If it is closer to total number of rows, it may be bad.

>>3) How can you tell IN ADVANCE that an index is screwed up so that you can rebuild it before it causes trouble?
It is very hard to say ( atleast for me. I have no sure proof method for this. Please note that, the recomendation of Rebuilding an index with a high Clustering factor is again a myth.)
All the time before, I was told that number of rows scanned is a key factor for index/table. Thanks to Cary/Jonathan, it is actually the blocks that is important. The value of 'data blocks per key' is very important thingy, in determining the effectiveness of index.
It gives the average number of blocks visited.

Regards
Re: The index rebuild "myth" [message #160195 is a reply to message #160128] Thu, 23 February 2006 16:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Great information, thanks. It got me looking into the stats recorded in USER_INDEXES. I checked the manual to see what CLUSTERING_FACTOR meant. I agree that rebuilding the index would be a great way to get exactly the same value Smile

Based on my assumptions described above (which may be a stretch) and your advice, I'm looking for sparse indexes. A sparse index would result in more blocks read (for a range scan of a low cardinality key), because keys could be unnecessarily split across leaf blocks. Is there logic in that?

To this end, I came up with the following query to estimate the number of rows per leaf block:
select index_name, num_rows / leaf_blocks as lpb
from user_indexes
where leaf_blocks > 0
and num_rows > 5000
and INDEX_TYPE = 'NORMAL'
and table_name = 'PD_CST_CNTR'
and index_name like '%RID%'
order by 2 desc
/

My thinking is that small values could indicate sparse indexes, and high values indecate dense indexes. I also need to take into account the average key width plus the width of a rowid, otherwise I could just end up with wide concatenated indexes at the bottom of the list.

I did a test on some non-unique indexes that contain a single ROWID column.
INDEX_NAME                            LPB
------------------------------ ----------
PD_CCNTR_RID6_IX                  650.125
PD_CCNTR_RID7_IX               266.692308
PD_CCNTR_RID1_IX                   260.05
PD_CCNTR_RID2_IX               253.682927
PD_CCNTR_RID3_IX               241.883721
The first index has been rebuilt and re-analyzed, and the third one just re-analyzed.

Now, after rebuilding and re-analyzing the third one...
INDEX_NAME                            LPB
------------------------------ ----------
PD_CCNTR_RID1_IX                  650.125
PD_CCNTR_RID6_IX                  650.125
PD_CCNTR_RID7_IX               266.692308
PD_CCNTR_RID2_IX               253.682927
PD_CCNTR_RID3_IX               241.883721

Hmmmm. I think I might be onto something here.

Am I on drugs? Or is this going somewhere useful?
_____________
Ross Leishman
Re: The index rebuild "myth" [message #160204 is a reply to message #160195] Thu, 23 February 2006 21:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Actually by, 'data blocks PER key' i actually meant AVG_DATA_BLOCKS_PER_KEY
[quote]
AVG_DATA_BLOCKS_PER_KEY* NUMBER Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.

CLUSTERING_FACTOR* NUMBER Indicates the amount of order of the rows in the table based on the values of the index.
If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
For bitmap indexes, this column is not applicable and is not used.
[/quoute]
Jonathan talks a little about it here, in this article, in the section Why is the Tablescan Cheaper?
http://www.dbazine.com/oracle/or-articles/jlewis12

[Updated on: Mon, 17 September 2007 23:44] by Moderator

Report message to a moderator

Re: The index rebuild "myth" [message #160228 is a reply to message #160204] Fri, 24 February 2006 00:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I looked at that, but AVG_DATA_BLOCKS_PER_KEY is not much help as an indicator of indexes requiring rebuild. I try to avoid b-tree indexing very low cardinality columns, and with 16K blocks it is most unusual to have a AVG_DATA_BLOCKS_PER_KEY other than 1.

It is true that a value > 1 would signal trouble, but since I can get 400+ leaf entries per block, I could be in trouble long before it got above 1.

_____________
Ross Leishman
Re: The index rebuild "myth" [message #160321 is a reply to message #160228] Fri, 24 February 2006 07:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Right now,I really have no idea.
I am trying to reproduce a certain cases. Will keep posted.
Thanks
Re: The index rebuild "myth" [message #160475 is a reply to message #160321] Sun, 26 February 2006 19:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If anyone is interested, I have developed a procedure that rebuilds indexes where there are more leaf blocks than strictly necessary.
- It works for 10g, and probably for 9i as well.
- It handles partitioned indexes, but no sub-partitioned.
- It should handle function-based indexes
- It does not rebuild BITMAP indexes, BITMAP JOIN indexes, or IOTs

It's very much beta-test at the moment, but it seems to work OK for my purposes. I'd give it a thorough review and test before implementing it if I were you.

PLEASE NOTE: This is one possible (unproven) measure of index-sparseness. It is not guaranteed to solve all instances of performance degradation in indexes that may be corrected with a rebuild.

PROCEDURE rebuild_fragmented_indexes (
        pTab            IN USER_TABLES.TABLE_NAME%TYPE
,       pPctUsed        IN NUMBER := 50
) AS
        lSqlStmt        VARCHAR2(2000);
        lRowSize        NUMBER;

        lClientInfo     VARCHAR2(2000) := pTab || ' ' || to_char(pPctUsed);
        lDbBlockSize    NUMBER;
BEGIN
        dbms_application_info.set_module('rebuild_fagmented_indexes', NULL);
        dbms_application_info.set_client_info(lClientInfo);

        SELECT  to_number(value)
        INTO    lDbBlockSize
        FROM    v$parameter
        WHERE   name = 'db_block_size';

        --
        -- Fetch all indexes for tables matching pTab (with >5000 rows)
        -- For partitioned indexes, select the partitions
        --
        FOR ind IN (
                SELECT  i.table_name
                ,       i.index_name
                ,       NULL as partition_name
                ,       i.num_rows / i.leaf_blocks AS rows_per_block
                FROM    user_indexes i
                LEFT OUTER JOIN user_part_indexes p
                ON      p.index_name = i.index_name
                WHERE   p.index_name IS NULL
                AND     i.num_rows >= 5000
                AND     i.leaf_blocks > 0
                AND     i.table_name LIKE pTab
                UNION ALL
                SELECT  i.table_name
                ,       p.index_name
                ,       p.partition_name
                ,       p.num_rows / p.leaf_blocks AS rows_per_block
                FROM    user_ind_partitions p
                JOIN    user_indexes i
                ON      i.index_name = p.index_name
                WHERE   p.num_rows >= 5000
                AND     p.leaf_blocks > 0
                AND     i.table_name LIKE pTab
        ) LOOP
                --
                -- Build a SQL statement that selects the average size of an index
                -- entry. Since every index entry contains a ROWID, add 10 to the
                -- total. SQL is of the form
                -- SELECT AVG(10 + NVL(VSIZE(col1),0) + NVL(VSIZE(col2),0)) + ...)
                -- FROM pTab
                --
                lSqlStmt := 'SELECT avg(10';
                FOR col IN (
                        SELECT  column_name
                        FROM    user_ind_columns
                        WHERE   index_name = ind.index_name
                ) LOOP
                        lSqlStmt := lSqlStmt || '+nvl(vsize(' || col.column_name || '),0)';
                END LOOP;

                lSqlStmt := lSqlStmt || ')FROM ' || ind.table_name;
                EXECUTE IMMEDIATE lSqlStmt INTO lRowSize;

                --
                -- The current number of rows per leaf block times the logical average
                -- row size (in bytes) gives a minimum space used in each leaf block
                -- by data (there are other overheads not counted here). Take this space
                -- as a percentage of the block size. If it falls below pPctUsed then
                -- rebuild and analyze the index
                --
                IF (100 * ind.rows_per_block * lRowSize / lDbBlockSize < pPctUsed) THEN
                        lSqlStmt := 'ALTER INDEX ' || ind.index_name || ' REBUILD ';

                        IF ind.partition_name IS NOT NULL THEN
                                lSqlStmt := lSqlStmt || 'PARTITION ' || ind.partition_name;
                        END IF;

                        dbms_application_info.set_module(
                                'rebuild_fagmented_indexes'
                        ,       'Rebuild ' || ind.index_name || ' ' || ind.partition_name);
                        dbms_application_info.set_client_info(lClientInfo);

                        EXECUTE IMMEDIATE lSqlStmt;

                        dbms_application_info.set_module(
                                'rebuild_fagmented_indexes'
                        ,       'Gather stats ' || ind.index_name || ' ' || ind.partition_name);
                        dbms_application_info.set_client_info(lClientInfo);

                        dbms_stats.gather_index_stats(
                                user
                        ,       ind.index_name
                        ,       ind.partition_name
                        );
                END IF;
        END LOOP;
        dbms_application_info.set_client_info(NULL);
        dbms_application_info.set_module(NULL, NULL);
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                dbms_application_info.set_client_info(NULL);
                dbms_application_info.set_module(NULL, NULL);
                raise_application_error(-20000, sqlerrm);
END rebuild_fragmented_indexes;


_____________
Ross Leishman
Re: The index rebuild "myth" [message #268159 is a reply to message #160475] Mon, 17 September 2007 17:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just discovered this relevant FAQ. Looks like a superior method, but I haven't tried it myself yet.

Ross Leishman
Re: The index rebuild "myth" [message #268162 is a reply to message #160041] Mon, 17 September 2007 18:20 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>If it is more than 20% of rows are deleted then the index should be rebuilt.
Ross,

I understand that you are just the messenger but I wonder how/where the "20%" was derived.

EXACTLY what quantifiable benefits are obtained by the rebuild;
other than the 20% now becomes 0%.
Re: The index rebuild "myth" [message #269610 is a reply to message #268162] Sun, 23 September 2007 20:34 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello,

Is this article matters?

http://www.jlcomp.demon.co.uk/indexes_i.html

Regards,


mson77
Previous Topic: Database Failure(oracle not available)
Next Topic: deconfig dbcontrol when oracle has benn patched
Goto Forum:
  


Current Time: Tue Dec 06 16:13:33 CST 2016

Total time taken to generate the page: 0.11535 seconds