When to Rebuild Indexes?

When is the time to rebuild the index?

Oracle now provides the capability to rebuild indexes while the system remains online. In the past, creating or rebuilding an index necessitated a complete table lock, rendering applications unusable for extended periods, especially on large tables. Today, Oracle allows you to create or rebuild indexes while users continue to perform various data operations. This is achieved by first creating the index structure and then populating it. While populating, all changes to the table are tracked in a journal table. As the index finalizes, the changes recorded in the journal table are incorporated.

To assess the status of an index and determine whether it needs reconstruction, you can utilize the "ANALYZE INDEX VALIDATE STRUCTURE" command. Normally, the "ANALYZE INDEX" command generates computed or estimated statistics for the index, which can be examined in the DBA_INDEXES view. However, this action may have unintended side effects, particularly if the index has not been analyzed before.

The "VALIDATE STRUCTURE" command can be executed safely without impacting the query optimizer. It populates only the SYS.INDEX_STATS table, which can be accessed via the public synonym INDEX_STATS. This table holds validation information for one index at a time, so you must query it before validating the structure of the next index.

If the analysis suggests that the index should be rebuilt, you can easily achieve this with the "ALTER INDEX REBUILD" command. Although it's not necessarily recommended, this command can be executed during regular operational hours. Index rebuilding uses the existing index as a basis, as opposed to dropping and recreating it. Creating an index from scratch requires locking the base table, rendering the index unavailable during its creation.

Consider the following example of using "ANALYZE INDEX VALIDATE STRUCTURE" and examining output from INDEX_STATS:

**Note:** When using "validate structure," it may attempt to lock the table and fail with Oracle error 54 ("resource busy and acquire with NOWAIT specified") if someone is currently updating the table. If the validation process takes a significant amount of time, anyone attempting to update the table during that period will be locked out.

```sql

ANALYZE INDEX PK_shopping_basket VALIDATE STRUCTURE;

SELECT name, height, lf_rows, lf_blks, del_lf_rows, distinct_keys, used_space,

(del_lf_rows/lf_rows)*100 as ratio

FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW DISTINCT_K USED_SPACE RATIO

------------------------- --------- ---------- ---------- ---------- ---------- ---------- ------

PK_SHOPPING_BASKET 2 1 3 1 1 65 10

```

The following INDEX_STATS columns are particularly informative:

- **height:** Indicates the maximum number of levels within the index. While most indexes have a low height, if the index has a height greater than three, it might need rebuilding due to a skewed tree structure.

- **lf_rows:** Represents the total number of leaf nodes in the index.

- **del_lf_rows:** Indicates the number of deleted leaf rows resulting from table deletions.

An index should be considered for rebuilding under the following conditions:

1. When deleted leaf rows make up more than 25% of the total leaf rows, suggesting a need for rebalancing.

2. If the 'HEIGHT' is greater than 3, which may indicate a skewed tree structure.

3. When the number of rows in the index ('LF_ROWS') is significantly smaller than 'LF_BLKS,' signaling a large number of deletes.

4. If 'BLOCK_GETS' exceeds 5.

For example:

```sql

ANALYZE INDEX PK_item_basket VALIDATE STRUCTURE;

SELECT name, height, lf_rows, del_lf_rows,

(del_lf_rows/lf_rows)*100 as ratio

FROM INDEX_STATS;

NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO

------------------------------ ---------- ---------- ---------- ----------

PK_ITEM_BASKET 1 235 74 31.49

```

In this example, the ratio of deleted leaf rows to total leaf rows exceeds 25%, making it a suitable candidate for rebuilding. You can rebuild the index using the "ALTER INDEX" command with the "REBUILD" option:

```sql

ALTER INDEX pk_item_basket REBUILD online;

```

The "online" option allows the new index to coexist with the old one during the rebuild, ensuring that enough space is available in the database. Once rebuilt, the new index replaces the old one, and its space is reclaimed by the database.

You can customize the rebuild operation with options like "PARALLEL" for parallel processing, "NOLOGGING" for faster operation, "COMPUTE STATISTICS" for collecting statistics, and "TABLESPACE" to specify where the new index will be stored.

How Indexes Become Fragmented

The advantages of indexing do not come without a cost. As database objects, indexes are created for tables only and they must be in sync with them: indexes must be updated by the database with every data manipulation language (DML) operation—INSERT, DELETE, or UPDATE. When a row is deleted from a table, the Oracle database will not reuse the corresponding index space until you rebuild the index.

Therefore, indexes are always growing and can become very fragmented over time, especially in tables with dynamic data. As indexes grow, two things happen: splitting and spawning.

Splitting happens when an index node becomes full with keys and a new index node is created at the same level as a full node. This widens the B*-tree horizontally and may degrade performance.

Spawning is the process of adding a new level to an index. As a new index is populated, it begins life as a single-level index. As keys are added, a spawning takes place and the first-level node reconfigures itself to have pointers to lower-level nodes. Spawning takes place at specific points within the index, not for the entire index. For example, a three-level index might have a node that experiences heavy INSERT activity. This node could spawn a fourth level without the other level-three nodes spawning new levels. That makes the index unbalanced.

A PL/SQL Procedure to Detect and Rebuild Out-of-Balance Indexes

This procedure (called RebuildUnbalancedIndexes) takes care of both global and local indexes. Global index information is extracted into the csrGlobalIndexes cursor from the USER_INDEXES view WHERE value in Partitioned column is 'NO':

cursor csrGlobalIndexes is

select index_name, tablespace_name

from user_indexes

where partitioned = 'NO';

Local indexes are created for partitioned tables. They are equi_partitioned with the table they are based on. That means that for each index entry residing in partition CUSTOMER_K, there is a row of the table CUSTOMER that also resides in that same partition CUSTOMER_K. Local index information is extracted into the csrLocalIndexes cursor from the USER_IND_PARTITIONS view WHERE value in Status column is 'USABLE':

cursor csrLocalIndexes is

select index_name, partition_name, tablespace_name

from user_ind_partitions

where status = 'USABLE';

If local indexes are UNUSABLE as a result of TRUNCATE or MOVE partition operations, they need to be rebuilt. Even though it's not a topic of this 10-Minute Solution, and it's not part of our procedure, here is the syntax for that case, for your reference:

alter table Customer

modify partition Customer_K

rebuild unusable local indexes;

For each index (global or local), the procedure generates statistics using the ANALYZE INDEX command with the VALIDATE STRUCTURE clause; extracts it from the INDEX_STATS view; checks for the index height and number of deleted leaf rows; and decides whether to rebuild the index or not.

Here is the COMPLETE code:

Create sequence S_AA_DB_MAINTENANCE

Start with 1

Increment by 1

create table AA_DB_MAINTENANCE

(

Actionid number,

StartTime date,

EndTime date,

Action varchar2(1000)

)

CREATE OR REPLACE

Procedure print_date_diff(p_dte1 IN DATE, p_dte2 IN DATE, v_result OUT varchar2)

v_diff NUMBER := 0;

v_hrs NUMBER := 0;

v_min NUMBER := 0;

v_sec NUMBER := 0;

BEGIN

v_diff := ABS(p_dte2 - p_dte1);

v_hrs := TRUNC(v_diff, 0)*24; -- start with days portion if any

v_diff := (v_diff - TRUNC(v_diff, 0))*24; -- lop off whole days, convert to hrs

v_hrs := v_hrs + TRUNC(v_diff, 0); -- add in leftover hrs if any

v_diff := (v_diff - TRUNC(v_diff, 0))*60; -- lop off hrs, convert to mins

v_min := TRUNC(v_diff, 0); -- whole mins

v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0); -- lop off mins, convert to secs

v_result := TO_CHAR(v_hrs) || ' HRS ' ||

TO_CHAR(v_min) || ' MIN ' ||

TO_CHAR(v_sec) || ' SEC';

DBMS_OUTPUT.put_line(v_result);

END print_date_diff;

CREATE OR REPLACE

Procedure DB_REBUILD_INDEXES

-- *****************************************

-- PURPOSE: Rebuild Indexes that are Fragmented

-- This Procedure is called by DB_MAINTENANCE_WEEKLY

-- This script will analyze all the indexes under the current Schema

-- After this the dynamic view INDEX_STATS is consulted to see if an index

-- is a good candidate for a rebuild.

-- Rebuild the index when :

-- - Deleted entries represent 25% or more of the current entries

-- - The index depth is more then 3 levels (defined in variable v_MaxHeight)

-- AUTHOR: Diego Pafumi

-- ******************************************

v_MaxHeight integer := 3;

v_MaxLeafsDeleted integer := 25;

v_Count integer := 0;

v_actionid integer;

v_date date;

v_error_message varchar2(255);

v_error_code number(8);

v_error_message2 varchar2(255);

v_finaldiff varchar2(40);

--Cursor to Manage NON-Partitioned Indexes

--Select the Indexes, excluding the ones in the Recycle Bin

cursor cur_Global_Indexes is

select index_name, tablespace_name

from user_indexes

where table_name not like 'BIN$%'

and partitioned = 'NO';

--Cursor to Manage Current Index

cursor cur_IndexStats is

select name, height, lf_rows as leafRows, del_lf_rows as leafRowsDeleted

from index_stats;

v_IndexStats cur_IndexStats%rowtype;

--Cursor to Manage Partitioned Indexes

-- cursor cur_Local_Indexes is

-- select index_name, partition_name, tablespace_name

-- from user_ind_partitions

-- where status = 'USABLE';

begin

select S_AA_DB_MAINTENANCE.nextval into v_actionid from dual;

select sysdate into v_date from dual;

delete from AA_DB_MAINTENANCE

where StartTime < sysdate - 20;

insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)

values (v_actionid, v_date, 'DB_REBUILD_INDEXES Procedure Started');

commit;

DBMS_OUTPUT.ENABLE(1000000);

/* Global or Standard Indexes Section */

for v_IndexRec in cur_Global_Indexes

loop

begin

dbms_output.put_line('before analyze ' || v_IndexRec.index_name);

execute immediate 'analyze index ' || v_IndexRec.index_name || ' validate structure';

dbms_output.put_line('After analyze ');

open cur_IndexStats;

fetch cur_IndexStats into v_IndexStats;

if cur_IndexStats%found then

if (v_IndexStats.height > v_MaxHeight) OR

(v_IndexStats.leafRows > 0 AND v_IndexStats.leafRowsDeleted > 0 AND

(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

begin

dbms_output.put_line('*****************************************************************');

dbms_output.put_line('Rebuilding index ' || v_IndexRec.index_name || ' with '

|| to_char(v_IndexStats.height) || ' height and '

|| to_char(trunc(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows)) || ' % LeafRows');

--- Line for Oracle 9i

-- execute immediate 'alter index ' || v_IndexRec.index_name ||

-- ' rebuild parallel nologging online tablespace ' || v_IndexRec.tablespace_name;

--- Line for Oracle 10g

-- On 10g Oracle now automatically collects statistics during index creation and rebuild

execute immediate 'alter index ' || v_IndexRec.index_name ||

' rebuild' ||

' parallel nologging compute statistics' ||

' tablespace ' || v_IndexRec.tablespace_name;

insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)

values (v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || ' has been rebuilt');

dbms_output.put_line('*****************************************************************');

v_Count := v_Count + 1;

exception

when OTHERS then

dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');

insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)

values (v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');

commit;

end;

end if;

close cur_IndexStats;

exception

when OTHERS then

dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');

insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)

values (v_actionid, sysdate, 'The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');

commit;

end;

end loop;

print_date_diff(v_date, sysdate, v_finaldiff);

insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)

values (v_actionid, sysdate, 'DB_REBUILD_INDEXES Finished in ' || v_finaldiff);

commit;

dbms_output.put_line('Global or Standard Indexes Rebuilt: ' || to_char(v_Count));

v_Count := 0;

--Local indexes Section

for v_IndexRec in cur_Local_Indexes

loop

execute immediate 'analyze index ' || v_IndexRec.index_name ||

' partition (' || v_IndexRec.partition_name ||

') validate structure';

open cur_IndexStats;

fetch cur_IndexStats into v_IndexStats;

if cur_IndexStats%found then

if (v_IndexStats.height > v_MaxHeight) OR

(v_IndexStats.leafRows > 0 and v_IndexStats.leafRowsDeleted > 0 AND

(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

v_Count := v_Count + 1;

dbms_output.put_line('Rebuilding Index ' || v_IndexRec.index_name || '...');

-- execute immediate 'alter index ' || v_IndexRec.index_name ||

-- ' rebuild' ||

-- ' partition ' || v_IndexRec.partition_name ||

-- ' parallel nologging compute statistics' ||

-- ' tablespace ' || v_IndexRec.tablespace_name;

end if;

close cur_IndexStats;

end loop;

dbms_output.put_line('Local Indexes Rebuilt: ' || to_char(v_Count));

EXCEPTION

WHEN OTHERS THEN

--Show Error on specific line

--10g Only

DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

v_error_code := SQLCODE;

v_error_message := SQLERRM;

--10g Only

v_error_message2 := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;

--10g Only

DBMS_OUTPUT.PUT_LINE('Error: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

dbms_output.put_line('Error: '||TO_CHAR(v_error_code)||' - '||v_error_message);

insert into AA_DB_MAINTENANCE (Actionid,StartTime, Action)

values (v_actionid, sysdate, '*** ERRORS on DB_REBUILD_INDEXES:' || TO_CHAR(v_error_code)||' - '||v_error_message);

commit;

end;

-- Run it Weekly on Sundays at 5AM

VARIABLE v_jobnum NUMBER;

BEGIN

DBMS_JOB.SUBMIT(:v_jobnum,'DB_MAINTENANCE_WEEKLY;', TO_DATE('2309200705','DDMMYYYYHH24'), 'trunc(sysdate,''hh'') + 7');

EN;

This is Just the PL/SQL Version of it

spool Rebuild_Indexes.txt

-- *****************************************

-- PURPOSE: Rebuild Indexes that are Fragmented

-- This is the PL/SQL Code version

-- This script will analyze all the indexes under the current Schema

-- After this the dynamic view INDEX_STATS is consulted to see if an index

-- is a good candidate for a rebuild.

-- Rebuild the index when :

-- - Deleted entries represent 25% or more of the current entries

-- - The index depth is more then 3 levels (defined in variable v_MaxHeight)

-- AUTHOR: Diego Pafumi

-- ******************************************

set serveroutput on

declare

v_MaxHeight integer := 3;

v_MaxLeafsDeleted integer := 25;

v_Count integer := 0;

v_date date;

v_error_message varchar2(255);

v_error_code number(8);

v_error_message2 varchar2(255);

v_finaldiff varchar2(40);

--Cursor to Manage NON-Partitioned Indexes

--Select the Indexes, excluding the ones in the Recycle Bin

cursor cur_Global_Indexes is

select index_name, tablespace_name

from user_indexes

where table_name not like 'BIN$%'

and partitioned = 'NO';

--Cursor to Manage Current Index

cursor cur_IndexStats is

select name, height, lf_rows as leafRows, del_lf_rows as leafRowsDeleted

from index_stats;

v_IndexStats cur_IndexStats%rowtype;

--Cursor to Manage Partitioned Indexes

-- cursor cur_Local_Indexes is

-- select index_name, partition_name, tablespace_name

-- from user_ind_partitions

-- where status = 'USABLE';

begin

select sysdate into v_date from dual;

DBMS_OUTPUT.ENABLE(1000000);

/* Global or Standard Indexes Section */

for v_IndexRec in cur_Global_Indexes

loop

begin

dbms_output.put_line('before analyze ' || v_IndexRec.index_name);

execute immediate 'analyze index ' || v_IndexRec.index_name || ' validate structure';

dbms_output.put_line('After analyze ');

open cur_IndexStats;

fetch cur_IndexStats into v_IndexStats;

if cur_IndexStats%found then

if (v_IndexStats.height > v_MaxHeight) OR

(v_IndexStats.leafRows > 0 AND v_IndexStats.leafRowsDeleted > 0 AND

(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

begin

dbms_output.put_line('*****************************************************************');

dbms_output.put_line('Rebuilding index ' || v_IndexRec.index_name || ' with '

|| to_char(v_IndexStats.height) || ' height and '

|| to_char(trunc(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows)) || ' % LeafRows');

--- Line for Oracle 9i

-- execute immediate 'alter index ' || v_IndexRec.index_name ||

-- ' rebuild parallel nologging online tablespace ' || v_IndexRec.tablespace_name;

--- Line for Oracle 10g

-- On 10g Oracle now automatically collects statistics during index creation and rebuild

execute immediate 'alter index ' || v_IndexRec.index_name ||

' rebuild' ||

' parallel nologging compute statistics' ||

' tablespace ' || v_IndexRec.tablespace_name;

dbms_output.put_line('The index ' || v_IndexRec.index_name || ' has been rebuilt');

dbms_output.put_line('*****************************************************************');

v_Count := v_Count + 1;

exception

when OTHERS then

dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT rebuilt');

commit;

end;

end if;

close cur_IndexStats;

exception

when OTHERS then

dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');

commit;

end;

end loop;

print_date_diff(v_date, sysdate, v_finaldiff);

dbms_output.put_line ('Rebuild INDEXES Finished in ' || v_finaldiff);

dbms_output.put_line('# of Indexes Rebuilt: ' || to_char(v_Count));

v_Count := 0;

--Local indexes Section

for v_IndexRec in cur_Local_Indexes

loop

execute immediate 'analyze index ' || v_IndexRec.index_name ||

' partition (' || v_IndexRec.partition_name ||

') validate structure';

open cur_IndexStats;

fetch cur_IndexStats into v_IndexStats;

if cur_IndexStats%found then

if (v_IndexStats.height > v_MaxHeight) OR

(v_IndexStats.leafRows > 0 and v_IndexStats.leafRowsDeleted > 0 AND

(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then

v_Count := v_Count + 1;

dbms_output.put_line('Rebuilding Index ' || v_IndexRec.index_name || '...');

-- execute immediate 'alter index ' || v_IndexRec.index_name ||

-- ' rebuild' ||

-- ' partition ' || v_IndexRec.partition_name ||

-- ' parallel nologging compute statistics' ||

-- ' tablespace ' || v_IndexRec.tablespace_name;

end if;

close cur_IndexStats;

end loop;

dbms_output.put_line('Local Indexes Rebuilt: ' || to_char(v_Count));

EXCEPTION

WHEN OTHERS THEN

--Show Error on specific line

--10g Only

DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

v_error_code := SQLCODE;

v_error_message := SQLERRM;

--10g Only

v_error_message2 := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ;

--10g Only

DBMS_OUTPUT.PUT_LINE('Error: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

dbms_output.put_line('Error: '||TO_CHAR(v_error_code)||' - '||v_error_message);

end;

spool off

And Another Method of Index Rebuilder with Job Scheduler:

An index should be considered for rebuilding under any of the following conditions:

- The percentage of deleted rows exceeds 30% of the total, i.e. if DEL_LF_ROWS / LF_ROWS > 0.3.

- If the 'HEIGHT' is greater than 3.

- If the number of rows in the index ('LF_ROWS') is significantly smaller than 'LF_BLKS' this can indicate a large number of deletes,

indicating that the index should be rebuilt.

- BLOCK_GETS greater than 5

We considered job scheduler for index rebuilding.

Steps:

1. Create a table TEMP_INDEX_STAT, which is similar to INDEX_STATS

2. Create a procedure GEN_INDEX_STAT. It will analyze an index and store that data in TEMP_INDEX_STAT

3. Create a job class LOW_PRIORITY_CLASS with LOW_GROUP resource consumer group, so that the job might not hamper production transaction.

4. Create a job GET_INDEX_STAT_JOB

5. Create a table RUNNING_CMDS

6. Create a procedure REBUILD_INDEX, which will REBUILD the indexes

7. Create another job REBUILD_INDEX_JOB. This job will call the above procedure to rebuild the indexes.

Please be sure that the load in the database is not at the peak. If yes, you may get resource timeout error during the job execution period and the job may be failed.

Step 1. Create table TEMP_INDEX_STAT

CREATE TABLE TEMP_INDEX_STAT AS SELECT * FROM INDEX_STATS WHERE 1=2;

Step 2. Create the Procedure GEN_INDEX_STAT

CREATE OR REPLACE PROCEDURE GEN_INDEX_STAT (PRM_SCHEMA VARCHAR2)

CURSOR INDEX_CUR IS SELECT INDEX_NAME

FROM DBA_INDEXES d

WHERE OWNER = prm_schema

AND NOT EXISTS (SELECT 1

FROM temp_index_stat b

WHERE d.index_Name = b.NAME

)

AND INDEX_TYPE = 'NORMAL';

v_str VARCHAR2(500);

BEGIN

FOR INDEX_REC IN INDEX_CUR LOOP

v_str := 'ANALYZE INDEX '||PRM_SCHEMA||'.'||INDEX_REC.INDEX_NAME||' VALIDATE STRUCTURE ';

EXECUTE IMMEDIATE v_str;

v_str := 'insert into TEMP_INDEX_STAT select * from index_stats';

EXECUTE IMMEDIATE v_str;

COMMIT;

END LOOP; --

END GEN_INDEX_STAT;

Step 3. Create a Job Class with low Pirority

begin

dbms_scheduler.create_job_class(

job_class_name => 'LOW_PRIORITY_CLASS',

resource_consumer_group => 'LOW_GROUP',

logging_level => DBMS_SCHEDULER.LOGGING_FULL,

log_history => 60,

comments => 'LOW PRIORITY JOB CLASS');

end;

Step 4. Create a Job to Call Pricedure

You may set START_DATE if you want to schedule the following job.

BEGIN

dbms_scheduler.create_job

(

job_name=> 'GEN_INDEX_STAT_JOB',

job_type=> 'PLSQL_BLOCK',

job_action=>'begin GEN_INDEX_STAT(''SCHEMA_NAME''); end;',

START_DATE=>NULL,

REPEAT_INTERVAL=>NULL,

ENABLED=>TRUE,

AUTO_DROP=>FALSE,

COMMENTS=>'Generate Index Stat',

job_class=>'LOW_PRIORITY_CLASS'

);

END;

Step 5. Create table RUNNING_CMDS

Upto this point, we have collected statistics for all indexes in our schema.

Now we need to REBUILD the indexes according to the conditions described above.

This table will store the commands so that we could easily identify which index is in rebuild process during the running of the following job.

CREATE TABLE RUNNING_CMDS CMD VARCHAR2(200);

Step 6. Create the Procedure to Rebuild Reported Indexes

CREATE OR REPLACE PROCEDURE REBUILD_INDEX(PRM_SCHEMA VARCHAR2)

CURSOR CUR IS

SELECT NAME

FROM TEMP_INDEX_STAT a

WHERE (HEIGHT >= 4

OR (del_lf_rows/lf_rows > 0.3 AND lf_rows > 0)

OR (blks_gets_per_access > 4)

)

AND EXISTS (SELECT 1

FROM dba_indexes d

WHERE a.NAME = D.index_name

AND d.index_type <> 'LOB'

);

BEGIN

execute immediate 'truncate table RUNNING_CMDS';

FOR REC IN CUR LOOP

v_str := 'ALTER INDEX '||PRM_SCHEMA||'.'||REC.NAME||' REBUILD ONLINE';

INSERT INTO RUNNING_CMDS VALUES(V_STR);

COMMIT;

EXECUTE IMMEDIATE v_str;

END LOOP;

END REBUILD_INDEX;

Step 7. Create a Job to call the previous procedure

You may set START_TIME according to your requirement.

BEGIN

dbms_scheduler.create_job

(

job_name=> 'REBUILD_INDEX_JOB',

job_type=> 'PLSQL_BLOCK',

job_action=>'begin REBUILD_INDEX(''SCHEMA_NAME''); end;',

START_DATE=>NULL,

REPEAT_INTERVAL=>NULL,

ENABLED=>TRUE,

AUTO_DROP=>FALSE,

COMMENTS=>'Rebuild Index',

job_class=>'LOW_PRIORITY_CLASS'

);

END;

Rebuilding Indexes

Beginning in 9i, you can rebuild your indexes online and compute statistics at the same time. Online index rebuild is also available for reverse key indexes, function-based indexes and key compressed indexes on both regular tables and index organized tables (including secondary indexes). In previous releases, you could either issue one of the following:

alter index index_name rebuild online

alter index index_name rebuild compute statistics

However it was not possible to combine these statements into one operation. In 9i, you can issue:

alter index index_name rebuild compute statistics online;

This allows your current users to be able to access the original index while it is being rebuilt and having statistics get generated against it. When you rebuild your indexes online, the new index is built within your index tablespace while the old index is still being used. The online option specifies that DML operations on the table or partition are allowed during rebuilding of the index. After the new index is completed the old index is dropped. When you rebuild your indexes and do not specify the online option, the database locks the entire table from being used while the index is being rebuilt. This option can also be combined with the nologging attribute to avoid generating redo when rebuilding your indexes (ex: alter index index_name rebuild compute statistics online nologging;)

Monitoring Index Usage

By using the 'alter index … monitoring usage' statement you can see if a particular index is being used.

Query v$object_usage as the owner of those indexes to see if the index has been used and during what time period it was monitored, it will show you just whether the index has been used or not; it will not show you how many times the index was used or when it was last used.

To stop monitoring an index, type:

ALTER INDEX index_name NOMONITORING USAGE;

This script activates index monitoring on an entire schema (must be run as System)

set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool monitor_idx.log
@monitor_idx.tmp
spool off

A sample query on V$OBJECT_USAGE confirms that monitoring has been activated (must be run as the OWNER of indexes)

set linesize 92
set pagesize 9999
select substr(index_name,1,25) index_name,
substr(table_name,1,15) table_name,
MONITORING, USED, START_MONITORING, END_MONITORING
from v$object_usage
order by used;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING------------- -------------------- --- ---- ------------------- --------------ITEM_ORDER_IX ORDER_ITEMS YES NO 08/15/2001 11:23:10INVENTORY_PK INVENTORIES YES YES 08/15/2001 16:51:32PROD_NAME_IX PRODUCT_DESCRIPTIONS YES NO 08/15/2001 16:50:32ORDER_P ORDERS YES YES 08/15/2001 17:10:32PRD_DESC_PK PRODUCT_DESCRIPTIONS YES YES 08/15/2001 17:13:32

Another useful index, is this one, to check ALL the indexes on the DB

select u.name "OWNER",

io.name "INDEX_NAME",

t.name "TABLE_NAME",

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",

decode(bitand(nvl(ou.flags,0), 1), 0, 'NO', 'YES') "USED",

ou.start_monitoring "START_MONITORING",

ou.end_monitoring "END_MONITORING"

from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u

where t.obj# =i.bo#

and io.owner# = u.user#

and io.obj# = i.obj#

and u.name not in ('SYS','SYSTEM','XDB','WMSYS','ORDSYS','OUTLN','MDSYS','CTXSYS')

and i.obj# =ou.obj#(+)

order by u.name, t.name, io.name;

This script stops index monitoring on an entire schema.(must be run as System)

set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool stop_monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool stop_monitor_idx.log
@stop_monitor_idx.tmp
spool off

Index Usage without "alter index.... monitoring usage"

To get a solution for this, there is one pre requirement-AWR. AWR (Automatic Workload Repository), which is part of "Tuning and Diagnostic Packs", must be installed, what require additional licensing. AWR is snapshot repository of important database activity, so AWR should able to keep data with retention of minimum of 30 days (46 days are perfect) and those snapshots must be taken in appropriate interval.

After that you are able to use this script that shows all indexes in schema (defined with first parameter-&&1) which are bigger then a value (defined with scripts's second parameter-&&2). Remember that this will work over your "AWR Monitor Period". Save the file to some location.

/* ---------------------------------------------------------------------------

Purpose : Shows index usage by execution (find problematic indexes)

Date : 22.01.2008.

Author : Damir Vadas, damir.vadas@gmail.com

Remarks : Run as privileged user

Must have AWR run because sql joins data from there works on 10g >

@index_usage SCHEMA MIN_INDEX_SIZE

Changes (DD.MM.YYYY, Name, CR/TR#):

25.11.2010, Damir Vadas added index size as parameter

30.11.2010, Damir Vadas fixed bug in query

--------------------------------------------------------------------------- */

ACCEPT OWNER PROMPT 'Schema_Name: '

ACCEPT SIZE_MB PROMPT 'SIZE in MB: '

set linesize 140

set pagesize 160

clear breaks

clear computes

break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB

compute sum of NR_EXEC on TABLE_NAME SKIP 2

compute sum of MB on TABLE_NAME SKIP 2

SET TIMI OFF

set linesize 140

set pagesize 10000

col OWNER noprint

col TABLE_NAME for a20 heading 'Table name'

col INDEX_NAME for a25 heading 'Index name'

col INDEX_TYPE for a10 heading 'Index|type'

col INDEX_OPERATION for a21 Heading 'Index operation'

col NR_EXEC for 9G999G990 heading 'Executions'

col MB for 999G990 Heading 'Index|Size MB' justify right

WITH Q AS (

SELECT

S.OWNER A_OWNER,

TABLE_NAME A_TABLE_NAME,

INDEX_NAME A_INDEX_NAME,

INDEX_TYPE A_INDEX_TYPE,

trunc(SUM(S.bytes) / 1048576) A_MB

FROM DBA_SEGMENTS S,

DBA_INDEXES I

WHERE S.OWNER = upper('&&OWNER')

AND I.OWNER = upper('&&OWNER')

AND INDEX_NAME = SEGMENT_NAME

GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE

HAVING SUM(S.BYTES) > 1048576 * &&SIZE_MB

)

SELECT /*+ NO_QUERY_TRANSFORMATION(S) */

A_OWNER OWNER,

A_TABLE_NAME TABLE_NAME,

A_INDEX_NAME INDEX_NAME,

A_INDEX_TYPE INDEX_TYPE,

A_MB MB,

DECODE (OPTIONS, null, ' -',OPTIONS) INDEX_OPERATION,

COUNT(OPERATION) NR_EXEC

FROM Q, DBA_HIST_SQL_PLAN d

WHERE D.OBJECT_OWNER(+)= q.A_OWNER

AND D.OBJECT_NAME(+) = q.A_INDEX_NAME

GROUP BY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB,

DECODE (OPTIONS, null, ' -',OPTIONS)

ORDER BY A_OWNER, A_TABLE_NAME, A_INDEX_NAME, A_INDEX_TYPE, A_MB DESC, NR_EXEC DESC;

PROMPT Showed only indexes in &OWNER schema whose size > &SIZE_MB MB in period:

SET HEAD OFF;

select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')

|| '-' ||

to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')

from dba_hist_snapshot;

SET HEAD ON

SET TIMI ON

This will show:

Index

Table name Index name type Size MB Index operation Executions

-------------------- ------------------------- ---------- -------- --------------------- ----------

EXCEPTIONITEM IN_EXCEPITEM_IMPDATE_INFS NORMAL 13 - 0

OURID

******************** ************************* ********** -------- ----------

sum 13 0

ISSUEDATA IN_ISSUEDATA_IMPORTEDDATE NORMAL 43 - 0

PK_ISSUEDATA NORMAL 22 - 0

UN_ISSUEDATA_BANKIDACCTSE NORMAL 265 - 0

RIAL

******************** ************************* ********** -------- ----------

sum 330 0

ISSUEDATA_ARCH IN_ISSUEDATAARCH_ISSUEID NORMAL 1,984 - 0

******************** ************************* ********** -------- ----------

sum 1,984 0

Showed only indexes in FG836_PNC_PROD schema whose size > 10 MB in period:

16.08.2011-24.08.2011

What is interesting here (keep in mind that monitoring period is 46 days!):

1- All indexes with "Executions" value=0 has never been used in monitored period. Reason may be poor quality (against underlying data) or change of CBO plans. These indexes are highly on the list for purge-especially if they are big or they are under table with big use (on other indexes i.e. NSO_PROD_I)!

2- Big indexes with small number of executions, especially those with just one execution, are really candidate to be purged and created/dropped for each execution

3- Indexes where FULL SCAN is using mode are candidates for rebuild or checking it's quality*

4- Tables which have many indexes with small index usage are candidates for relation model redesign

5- Indexes with huge number of executions should be observed a lot. Rebuild it when usage in next period decrease for more then 15%-25%.

Get Index Information

The idea of this script is to measure index quality.

If index has bad ratio of index_clustering_factor/table_rows it's usage may be worse then full table scan!

There are mainly two reasons why index has bad quality (without deeper coverage any of these terms):

Index is fragmented-solution is to rebuild index
Underlying table data are in that order that usage of index is really doubt-able. Solution is to drop it (make invisible in 11g!) and try to execute query to see performance. In a case of huge performance downgrade think about creation of index before query and drop after is the best solution for rarely run queries.

/**********************************************************************

* File: Index_Info.sql

* Type: SQL*Plus script

* Author: Dan Hotka

* Date: 04-16-2009

* Description:

* SQL*Plus script to display Index Statistics in relation to clustering factor

* Script originated from Jonathan Lewis

* Modifications:

*********************************************************************/

set linesize 300

set pagesize 200

spool Index_Info.txt

SELECT i.table_name, i.index_name, t.num_rows, t.blocks, i.clustering_factor,

case when nvl(i.clustering_factor,0) = 0 then 'No Stats'

when nvl(t.num_rows,0) = 0 then 'No Stats'

when (round(i.clustering_factor / t.num_rows * 100)) < 6 then 'Excellent '

when (round(i.clustering_factor / t.num_rows * 100)) between 7 and 11 then 'Good'

when (round(i.clustering_factor / t.num_rows * 100)) between 12 and 21 then 'Fair'

else 'Poor'

end Index_Quality,

i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key,

to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created

from user_indexes i, user_objects o, user_tables t

where i.index_name = o.object_name

and i.table_name = t.table_name

order by 1;

spool off

-- exit

And here some details on how to read and analyze each case:

Table Table Index Data Blks Leaf Blks Clust Index

Table Rows Blocks Index Size MB per Key per Key Factor Quality

--------------------- ----------- ---------- ------ --------- --------- --------- ------------ -----------

NC_SUBSCRIPTION_SALDO 22,461,175 415103 NSO_I 560.00 9 1 21,749,007 1-Poor

This is classical representative of index with enormous clustering factor who may be a candidate for rebuild (very doubtful on first look) and if that doesn't help then recreate/drop should eb an option if not and pure drop!

Table Table Index Data Blks Leaf Blks Clust Index

Table Rows Blocks Index Size MB per Key per Key Factor Quality

--------------------- ----------- ---------- ------ --------- --------- --------- ------------ -----------

NC_TRANSACTION_ARZ_T 5,815,708 137742 TRA_I 184.00 12 1 222,104 5-Excellent

This is representative of high quality index who is unused (used only twice). For such an index I'll recommend deeper analyze for involved SQLs. But this is not a candidate for purge in any mean.

Table Table Index Data Blks Leaf Blks Clust Index

Table Rows Blocks Index Size MB per Key per Key Factor Quality

--------------------- ---------- ---------- ------- --------- --------- --------- ------------ -----------

NC_TRANSACTION_OK_T 24,492,333 851796 TROK_I 536.00 1 1 21,977,784 1-Poor

This index has never been used-highly recommended for purge!

Table Table Index Data Blks Leaf Blks Clust Index

Table Rows Blocks Index Size MB per Key per Key Factor Quality

--------------------- ---------- ---------- ------- --------- --------- --------- ------------ -----------

NC_TRANSACTION_ULAZ_TT 840,179 27437 TRUT_I 774.00 1 1 731,609 1-Poor

This index is pretty big (774 MB), with low index quality, and use only twice. Recommendation rebuild and, if that doesn't help, purge.

When to Rebuild Indexes?

When is the time to rebuild the index?

Post a Comment

Contact Form