 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
|
|
 |
Donald K. Burleson Oracle Tips |
Predict indexes for rebuilding
By John Palinski
One of the DBA's chores is to monitor tablespace sizes to ensure that adequate space is available for the objects that reside within the tablespace. A second task is to monitor index sizes and rebuild the indexes when they get too large or fragmented. See the latest consensus on index rebuilding.
Indexes have a tendency to grow as they are being updated in a different proportion to the referenced data. This is especially true of bitmap indexes that can grow very rapidly when updated. Besides the space savings, smaller sized indexes increase performance by reducing the disk area needed to identify rows. For these reasons it is a good habit to monitor the size of tablespaces and the indexes rebuilding them when necessary.
Indexes are also often kept in their own tablespaces segregated from the tables they reference. This allows the DBA to monitor growth separately from the data. If you have tables that grow consistently, but at a slow steady space, you may want to keep a smaller amount of free space than you would with a tablespace populated by indexes. Placing indexes into their own tablespace also allows you to target them based upon the object need.
Normally, when I rebuild a tablespaces index I generate a DDL script using SQL*Plus and the following script:
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where tablespace_name = 'INDEX_TABLESPACE';
This statement produces a list of DDL commands that rebuild the indexes in place. Normally, the generated script works exceedingly well. However, in some occasions the target index is larger that the tablespace free space. When this occurs, you will get the following error:
ORA-1652: unable to extend temp segment by 3168 in tablespace
INDEX_TABLESPACE
This error occurs because Oracle must build the new index before the old one is removed and the tablespace does not have the free space for a new version and an old version. The DBA then has two choices:
1. Drop the existing index and recreate it.
2. Rebuild the index in a different tablespace with adequate free space and rebuilding it again in the original tablespace after the original index version is dropped.
The former option of dropping the index may cause applications and users response problems. It also requires the DBA to create a new DDL index statement. Not a difficult task, but not one a busy DBA relishes. For these two reasons I generally choose option 2.
The only drawback to this option is that the index is created twice requiring extra server work. Having the computer perform a little more work is far superior then me performing some work. However, I don't like having to re-execute commands because they failed. For this reason I developed a simple script for rebuilding all tablespaces indexes that eliminates the Ora-1652 error.
It performs the following:
1. Prompts the user for the target tablespace
2. Determines the tablespace's free space available for the rebuilt index
3. Determines the tablespace's indexes
4. Determines the size of each index
5. Compares the index size to the available free space. If the index size is less than the tablespace size, the index is rebuilt in the same tablespace. If the index is larger than the available free space the index is first rebuilt in a development tablespace called Reorg_index_area. The index is then rebuilt in the original tablespace. The Reorg_index_area tablespace is an empty tablespace created by the DBA to temporarily hold rebuilt indexes.
6. Messages displaying the original tablespace free space, the indexes
rebuilt, and the final tablespace free space.
The script that rebuilds the indexes is displayed below. It is an anonymous block procedure that will prompt you for the target tablespace name.
set serveroutput on
declare
cursor freespace_cursor is select sum(bytes)/1024/1024 freespace
from dba_free_space
where tablespace_name =
upper('&&target_tablespace')
group by tablespace_name;
freespace_value number;
q varchar2(60);
begin
open freespace_cursor;
fetch freespace_cursor into freespace_value;
close freespace_cursor;
dbms_output.put_line ('Tablespace '||'&&target_tablespace'||'
freespace is now '||freespace_value); for z in (select a.owner,
index_name, segment_name, 'Index', index_type, uniqueness, compression,
a.tablespace_name, mbytes_used
from dba_indexes a
join (select owner, segment_name, sum(bytes)/1024/1024
mbytes_used
from dba_extents
where tablespace_name = upper('&target_tablespace')
group by owner, segment_name) b on (a.owner = b.owner
and a.index_name = b.segment_name)
order by mbytes_used)
loop
if z.mbytes_used < freespace_value then
execute immediate 'alter index '||z.owner||'.'||z.segment_name||'
rebuild '; else
execute immediate 'alter index '||z.owner||'.'||z.segment_name||'
rebuild tablespace reorg_index_area';
dbms_output.put_line ('Rebuild of index in reorg_index_area
'||z.owner||'.'||z.segment_name||' is complete');
execute immediate 'alter index '||z.owner||'.'||z.segment_name||'
rebuild tablespace '||z.tablespace_name;
dbms_output.put_line ('Rebuild of index in original tablespace is
'||z.owner||'.'||z.segment_name||' is complete');
end if;
dbms_output.put_line ('Rebuild of index
'||z.owner||'.'||z.segment_name||' is complete');
open freespace_cursor;
fetch freespace_cursor into freespace_value;
close freespace_cursor;
dbms_output.put_line ('Tablespace '||'&&target_tablespace'||'
freespace is now '||freespace_value); end loop; end; / undefine
target_tablespace
The code block prompts the user for the name of the target tablespace, Index_tablespace. Also notice that the script built several indexes directly into the Index_tablespace tablespace. Two of the indexes were too large for the free space and were rebuilt into the Reorg_index_area tablespace and again into the Index_tablespace tablespace.
The net effect is rebuilt indexes with no DBA intervention. The only thing the DBA needs to do is launch the script and identify the tablespace. Everything else is automatic.
See these related notes on Oracle index rebuilding:
Oracle Index rebuilding - Why when how
Important Oracle 10g bug in index rebuilding
Oracle myth busters index rebuild disk io wait event analysis
Identifying which Oracle Indexes to Rebuild
Predict Oracle table index benefit from rebuild reorganization
Oracle tuning: Blocksize and index tree structures
Oracle indexes rebuild distinct keys
|
|
Copyright © 1996 - 2011 by
Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.