|
|
|
10.2 Maintaining IndexesIndexes 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 ProblemsWhen 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.sqlDROP 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:
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.plTo 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:
The idxr.conf configurationfile contains only a few parameters, as shown in Example 10-9. Example 10-9. idxr.confpackage 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:
10.2.2.1 FragmentationThe 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.
Example 10-10. Determining optimal b*-tree heightsub 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:
10.2.2.2 Testing idxr.plWe'll demonstrate the use of idxr.pl with the following test:
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 TrackingAn 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 }
|
|
|
|