• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

How to automate index rebuilds online in Oracle

Is there a way to set up monitoring of index pct used and kick off an online rebuild of the index when the 20% threshold is met.

what happens is a session spawns multiple inserts, deletes and updates on tables to set up a "data scenario" that the application uses.  When the application has completed the java framework on the app side spawns an additional sqlid that deletes all the data for the scenario and replaces it with the original data before the session.  Each time (hundreds of times a day) after the session closes the indexes on these tables (some are 3mm + rows) are not usable due to the massive dml changes.  The next user runs another session and everything is going full table scan and the performance is terrible.

Any ideas how to deal with this?

1 Solution
Yes it is possible. You have to define where it is the value you use to trigger.

PROCEDURE rebuild_ind IS
   v_sql VARCHAR2(890);
FOR i IN (SELECT * FROM dba_indexes) LOOP

    IF  pct_free < 20 THEN
     v_sql :=  'ALTER INDEX ' || i.owner ||'.'||i.index_name || ' rebuild';
     execute immediate v_sql;

    END IF;




Some clarification should be done.
This procedure cold be run every 20 minutes (DBMS_JOB, DBMS_SCHEDULE packages) or
when application finishes.
johnsoneSenior Oracle DBACommented:
After loading large amounts of data are the tables being analyzed?  That could account for your performance issues.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now