Section 10.2. Maintaining Indexes

   

10.2 Maintaining Indexes

Indexes on tables are required in any database to help enforce integrity constraints and, more importantly, to increase database performance. If you don't maintain your indexes, there will be a measurable and noticeable effect on performance. In this section we'll provide some index maintenance scripts aimed at helping Oracle DBAs keep their databases running efficiently .

10.2.1 Looking at Oracle Space Problems

When table space is freed due to DML deletes or updates within previously full index blocks, Oracle ordinarily fails to reuse this space except under special circumstances. Oracle reuses such an index block only when it becomes completely empty, and this situation naturally leads to b*-tree index fragmentation. If unattended, indexes eventually become like Tom and Jerry's favorite snack ” except Swiss cheese is supposed to be full of holes. An exaggerated example using index_frag_test.sql illustrates the point.

On Unix, you'll find this script in the PDBA installation directory:

 $ ls /u01/build/PDBA-1.00/routine_tasks/index_frag_test.sql 

On Win32, type:

 DOS> type C:\Perl\site\lib\PDBA\sql\index_frag_test.sql 

This test script is shown in Example 10-8.

Example 10-8. index_frag_test.sql
 DROP TABLE IDX_FRAGMENT; PROMPT creating test table IDX_FRAGMENT CREATE TABLE IDX_FRAGMENT (PK NUMBER NOT NULL, TESTDATA VARCHAR2(2000));    PROMPT inserting test data into IDX_FRAGMENT DECLARE    Maxcount CONSTANT INTEGER := 1000;    Insert_Str VARCHAR2(2000); BEGIN    Insert_Str := RPAD('X',1000,'X');    FOR N IN 1 .. maxcount    LOOP       INSERT INTO IDX_FRAGMENT(PK,TESTDATA )       VALUES(N, Insert_Str);    END LOOP;    COMMIT; END; / PROMPT creating primary key IDX_FRAGMENT_PK ALTER TABLE IDX_FRAGMENT ADD CONSTRAINT IDX_FRAGMENT_PK PRIMARY KEY(PK);    PROMPT creating index IDX_FRAGMENT_IDX CREATE INDEX IDX_FRAGMENT_IDX ON IDX_FRAGMENT(TESTDATA, PK) PCTFREE 0;    COL SEGMENT_NAME FORMAT A30 HEAD 'SEGMENT NAME' COL EXTENT_ID FORMAT A10 HEAD 'EXTENT ID' COL BYTES FORMAT 999,999,999 HEAD 'BYTES' COMPUTE SUM OF BYTES ON REPORT BREAK ON REPORT    -- show number of extents, and then number of rows in table SELECT SEGMENT_NAME, DECODE(EXTENT_ID,0,'0',TO_CHAR(EXTENT_ID)) EXTENT_ID,        BYTES   FROM DBA_EXTENTS  WHERE OWNER = USER    AND SEGMENT_NAME = 'IDX_FRAGMENT_IDX'  ORDER BY TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME;     SELECT COUNT(*) IDX_FRAGMENT_ROW_COUNT FROM IDX_FRAGMENT;    PROMPT delete every 5th row from the table and reinsert it DECLARE    Maxcount CONSTANT INTEGER := 1000;    insert_str VARCHAR2(2000); BEGIN    insert_str := RPAD('X',1000,'X');    FOR N IN 1 .. Maxcount    LOOP       -- DELETE EVERY 5TH ROW       IF MOD(N,5) = 0 THEN          -- DELETE THE ROW          DELETE FROM IDX_FRAGMENT WHERE PK = N;          -- PUT IT BACK          INSERT INTO IDX_FRAGMENT(PK,TESTDATA )          VALUES(N, Insert_Str);       END IF;    END LOOP;    COMMIT; END; / SELECT SEGMENT_NAME, DECODE(EXTENT_ID,0,'0',TO_CHAR(EXTENT_ID)) EXTENT_ID,        BYTES   FROM DBA_EXTENTS  WHERE OWNER = USER    AND SEGMENT_NAME = 'IDX_FRAGMENT_IDX'  ORDER BY TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME;    SELECT COUNT(*) IDX_FRAGMENT_ROW_COUNT FROM IDX_FRAGMENT; 

In a nutshell , index_frag_test.sql creates a two-column table with 1000 rows, each row averaging 1002 bytes. We're going to fragment this index to make our point.

Both columns help create an IDX_FRAGMENT_IDX index, creating 12.6 rows per index block on our 8K block database. Let's take a look at the output. We'll pick up the important lines afterwards:

 01:  creating test table IDX_FRAGMENT 02:  inserting test data into IDX_FRAGMENT 03:  creating primary key IDX_FRAGMENT_PK 04:  creating index IDX_FRAGMENT_IDX 05:   06:  Index created. 07:   08:  SEGMENT NAME                   EXTENT ID         BYTES 09:  ------------------------------ ---------- ------------ 10:  IDX_FRAGMENT_IDX               0               131,072 11:  IDX_FRAGMENT_IDX               1               131,072 12:  IDX_FRAGMENT_IDX               2               131,072 13:  IDX_FRAGMENT_IDX               3               131,072 14:  IDX_FRAGMENT_IDX               4               131,072 15:  IDX_FRAGMENT_IDX               5               131,072 16:  IDX_FRAGMENT_IDX               6               131,072 17:  IDX_FRAGMENT_IDX               7               131,072 18:  IDX_FRAGMENT_IDX               8               131,072 19:  IDX_FRAGMENT_IDX               9               131,072 20:  IDX_FRAGMENT_IDX               10              131,072 21:                                            ------------ 22:  sum                                          1,441,792 23:  24:  11 rows selected  . 25:   26:  IDX_FRAGMENT_ROW_COUNT 27:  ---------------------- 28:                    1000 29:  1 row selected. 30:  31:  delete every 5th row from the table and reinsert it  32:   33:  PL/SQL procedure successfully completed. 34:   35:  SEGMENT NAME                   EXTENT ID         BYTES 36:  ------------------------------ ---------- ------------ 37:  IDX_FRAGMENT_IDX               0               131,072 38:  IDX_FRAGMENT_IDX               1               131,072 39:  IDX_FRAGMENT_IDX               2               131,072 40:  IDX_FRAGMENT_IDX               3               131,072 41:  IDX_FRAGMENT_IDX               4               131,072 42:  IDX_FRAGMENT_IDX               5               131,072 43:  IDX_FRAGMENT_IDX               6               131,072 44:  IDX_FRAGMENT_IDX               7               131,072 45:  IDX_FRAGMENT_IDX               8               131,072 46:  IDX_FRAGMENT_IDX               9               131,072 47:  IDX_FRAGMENT_IDX               10              131,072 48:  IDX_FRAGMENT_IDX               11              131,072 49:  IDX_FRAGMENT_IDX               12              131,072 50:  IDX_FRAGMENT_IDX               13              131,072 51:  IDX_FRAGMENT_IDX               14              131,072 52:  IDX_FRAGMENT_IDX               15              131,072 53:  IDX_FRAGMENT_IDX               16              131,072 54:  IDX_FRAGMENT_IDX               17              131,072 55:  IDX_FRAGMENT_IDX               18              131,072 56:  IDX_FRAGMENT_IDX               19              131,072 57:  IDX_FRAGMENT_IDX               20              131,072 58:  IDX_FRAGMENT_IDX               21              131,072 59:  IDX_FRAGMENT_IDX               22              131,072 60:                                            ------------ 61:  sum                                          3,014,656 62:  63:  23 rows selected  . 

Viewing the code output shows that:

  • At line 24, IDX_FRAGMENT_IDX gets created with a total of eleven 128K extents.

  • At line 31, a procedure deletes every fifth table row before immediately reinserting it. The index impact can be seen at line 63. Even though the index is still pointing to the same 1000 rows, it now requires more than twice as much space to do so; 3,014,656 bytes.

  • If this were a million-row index, the additional space required would cause many more index buffer gets and disk reads. The holes in the index would have a noticeable impact on performance.

The idxr.pl script described in the next section will help you maintain your indexes for peak performance.

10.2.2 Rebuilding Indexes with idxr.pl

To assist you in rebuilding an index and improving the efficiency of index operations, we've included the script idxr.pl in the toolkit. This script uses Oracle's ALTER INDEX REBUILD statement. Some of its features include:

Compute index statistics

You can generate statistics for the index at the time of the rebuild.

Control over length of runtime

You can specify a limited runtime. The script runs within a maintenance window, rebuilding as many indexes as possible in that time frame.

Incremental index rebuilds

Based on LAST_ANALYZED dates, and runtime windows , you control how many idxr.pl executions are necessary to completely rebuild indexes.

Index optimal height calculation

The optimal index height is calculated from index statistics. If the actual height is greater than the calculated value, the index will be rebuilt.

Percent of deleted rows threshold

A threshold based on the percentage of deleted rows in the index can be used to force the index to be rebuilt.

The idxr.conf configurationfile contains only a few parameters, as shown in Example 10-9.

Example 10-9. idxr.conf
 package idxr;    use PDBA; use vars qw{ %config };  %config = (  # don't check indexes that have been analyzed more recently    # than a specified number of days. The reason for this is    # that large systems may have many thousands of indexes, more    # than can be done in a single pass.  It may take several passes    # if you have an hour each night to run this, and it takes 20     # hours to validate structure, rebuild and analyze your indexes,    # you would set mostRecentlyAnalyzed to 20 and maxRunTime to 60       # specifify maxRunTime in minutes  maxRunTime => 60  ,    # don't check indexes that have    # been analyzed more recently than    # mostRecentlyAnalyzed, expressed in days  mostRecentlyAnalyzed => 0  ,    # rebuild the index if percent of deleted    # rows is greater than this  pctDeletedThreshold => 10  ,  logFile => PDBA->pdbaHome . q{/logs/idxr.log}  ,  );  1; 

Three parameters determine the runtime characteristics of the script, and one locates the log file:

maxRunTime

Time in minutes that idxr.pl is allowed to run. This time won't be exact, because it is rechecked after each index rebuild. If 60 minutes are set, and a rebuild requiring 10 minutes starts at 58 minutes, the script exits at 68 minutes.

mostRecentlyAnalyzed

This parameter determines how old an index must be before it will be considered for rebuilding. If this parameter is set to 3, and the script is set to run on a Sunday, indexes analyzed more recently than the previous Thursday will be ignored. Suppose that:

  • You have 500 indexes, and it takes 20 hours to rebuild them all.

  • You have a one-hour maintenance window each evening.

With these constraints in mind, you set mostRecentlyAnalyzed to 20 and maxRunTime to 60. All of your indexes will be gradually rebuilt over a 20-day period.

logFile

Sets the location of the output log file.

pctDeletedThreshold

If the deleted row percentage in the index exceeds the value of this parameter, the index is rebuilt.

10.2.2.1 Fragmentation

The idxr.pl script also determines whether the height of the b*-tree index has exceeded its optimal value. We've ignored the standard formulas for this value and adapted our SQL from a popular paper on Oracle fragmentation. [3] The relevant portion of idxr.pl is reproduced in Example 10-10. The script's command-line options are summarized in Table 10-5.

[3] See " How To Stop Defragmenting and Start Living: The Definitive Word On Fragmentation" by Bhaskar Himatsingka and Juan Loaiza at http://www.oreilly.com/catalog/oressentials/chapter/defrag.pdf

Example 10-10. Determining optimal b*-tree height
 sub  getStat  {    my ($self, $dbh) = @_;       my $statSql = q{       SELECT           NAME INDEX_NAME          , DECODE (             SIGN(                CEIL(                   LOG(                      BR_BLK_LEN/(BR_ROWS_LEN/BR_ROWS),                      LF_BLK_LEN/((LF_ROWS_LEN - DEL_LF_ROWS_LEN)                      /(LF_ROWS - DEL_LF_ROWS))                   )                ) + 1 - HEIGHT             )             , -1, 'YES'             , 'NO'          )  CAN_REDUCE_LEVEL  ,DEL_LF_ROWS*100/DECODE(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED       FROM  INDEX_STATS  WHERE LF_ROWS <> 0       AND DEL_LF_ROWS <> 0       AND DEL_LF_ROWS_LEN <> 0       AND LF_ROWS_LEN <> 0       AND BR_ROWS <> 0       AND BR_ROWS_LEN <> 0    };    my $statSth = $dbh->prepare($statSql);    $statSth->execute;    my $row = $statSth->fetchrow_hashref;       return $row ? $row : undef; } 

Here are the steps that determine whether an index should be rebuilt:

  • Run ANALYZE INDEX VALIDATE STRUCTURE for each index. (If you have index partitions and subpartitions, these will be analyzed too.)

  • Retrieve ANALYZE figures from the INDEX_STATS system view.

  • If the CAN_REDUCE_LEVEL row from the getStat method is YES, or the deleted rows percentage exceeds pctDeleteThreshold , then rebuild.

  • If -compute_statistics was specified, then rebuild and compute statistics.

Table 10-5. Command-line options ” idxr.pl

Option

Description

-machine

Server where the target database resides.

-database

Target database.

-username

DBA account.

-password

DBA password (optional if Password server in use).

-conf

Configuration file. This defaults to idxr.conf .

-target_schema

Target schema on which to rebuild indexes.

-compute_statistics

Compute statistics when rebuilding index. (Adds very little overhead.)

10.2.2.2 Testing idxr.pl

We'll demonstrate the use of idxr.pl with the following test:

  1. A single-column test table HASH_TEST is created with a HASH PARTITIONED index of HASH_TEST_PK.

  2. We insert 100,000 table rows and then immediately delete 20,000 of them.

  3. For our test we set the mostRecentlyAnalyzed parameter in idxr.conf to 0. This will cause idxr.pl to consider all indexes as candidates for rebuilding regardless of age. We also set the pctDeletedThreshold parameter in idxr.conf to 10 so that candidate indexes with more than 10% deleted rows will be rebuilt.

  4. Because this deletion exceeds the deleted rows percentage of 10% in the index hash partition, the index partitions should all be rebuilt:

     $ idxr.pl -machine sherlock -database ts01 \      -username system -target_schema jkstill 

All the output from idxr.pl is directed to a log file, so nothing should appear on the screen while it's running. The results of the test are seen here:

 20020217105027:starting 20020217105027:maxRunSeconds:3600 20020217105027:sysDate:2002/02/17 10:50 20020217105027:globalName:TS01.JKS.COM 20020217105027:schema:JKSTILL 20020217105027:checking indexes analyzed more than 0 days ago 20020217105028:checking INDEX CHILD_PK_IDX 20020217105028:checking INDEX DM_UNQ 20020217105028:checking INDEX IDX_FRAGMENT_IDX ... 20020217105030:checking INDEX PARTITION HASH_TEST_P1 20020217105032:Rebuilding INDEX PARTITION HASH_TEST_P1 20020217105032:Attempting to Rebuild Index online 20020217105032:Rebuilt INDEX PARTITION HASH_TEST_P1 online ... 20020217105035:checking INDEX PARTITION HASH_TEST_P8 20020217105035:Rebuilding INDEX PARTITION HASH_TEST_P8 20020217105035:Attempting to Rebuild Index online 20020217105036:Rebuilt INDEX PARTITION HASH_TEST_P8 online 20020217105036:exiting 

Because we specified that all indexes of any age having more than 10% deleted rows should be rebuilt, all of the indexes were rebuilt in this test.

10.2.2.3 Tracking

An internal idxr.pl feature tracks how long the script has been running. We've used a form of closure to determine when the maximum runtime is breached. In standard Perl terms, a closure is simply a subroutine reference that preserves the value of a lexically scoped variable between calls. In this case, it's simply an anonymous code block that accomplishes the same thing.

The closure is formed by curly braces {} on lines 10 and 29 of Example 10-11. The lexically scoped or my variables of $maxRunSeconds and $startTimeSeconds are enclosed within this block. When the startTimer method is called at line 1, it sets the value of $maxRunSeconds . Even when the startTimer method returns, the value of $maxRunSeconds is maintained because the code block containing it is never actually exited.

After each index is rebuilt, the checkTimer method at line 21 is used to determine if the maximum allowable runtime has been reached. If so, the number of actual seconds elapsed is returned; otherwise , zero is returned. If a nonzero value is returned by checkTimer at line 3, messages are logged indicating the actual runtime, and the index rebuild loop is exited via the last statement. The script then exits.

Example 10-11. Closure in idxr.pl
 1   my $maxRunSeconds = idxrp->startTimer($idxr::config{maxRunTime});   2     3  if ( my $runSeconds = idxrp->checkTimer )  {   4      $logFh->printflush("Max seconds $maxRunSeconds reached\n");   5      $logFh->printflush("Actual runtime was $runSeconds seconds\n");   6  last;  7   }   8     9  {  10      my $maxRunSeconds = undef;  11      my $startTimeSeconds = time;  12     13      sub startTimer {  14         my ($self, $maxMinutes) = @_;  15         $maxRunSeconds = $idxr::config{maxRunTime} * 60;  16         $startTimeSeconds = time;  17         return $maxRunSeconds;  18      }  19    20  sub checkTimer  {  21         my $self = shift;  22         my $currTimeSeconds = time;  23         my $runSeconds = $currTimeSeconds - $startTimeSeconds;  24         if ( $runSeconds >= $maxRunSeconds ) {  25            return $runSeconds;  26         } else { return 0 }  27      }  28  }  
   


Perl for Oracle DBAs
Perl for Oracle Dbas
ISBN: 0596002106
EAN: 2147483647
Year: 2002
Pages: 137

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net