How to automate index rebuilds online in Oracle 10.2.0.2

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?

thanks
mjimison1956Asked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
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;

END LOOP;

EXCEPTION
WHEN OTHERS
    .....

END;

Some clarification should be done.
This procedure cold be run every 20 minutes (DBMS_JOB, DBMS_SCHEDULE packages) or
when application finishes.
0
 
johnsoneSenior Oracle DBACommented:
After loading large amounts of data are the tables being analyzed?  That could account for your performance issues.
0
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.

All Courses

From novice to tech pro — start learning today.