Subject:            CAPTURING INDEX_STAT INFORMATION
Author:             RADRANLY
Last Revision Date: 04 June      1996



_

                       Capturing Index_Stat Information

This bulletin gives a package that can be created which will automatically
analyze indexes with the 'validate structure' option.  It will also insert
the data inserted into the index_stats table into a temporary table for
future analysis.

This temporary table must be created manually, and within the same schema that
will own the package.  To create the table simply execute the following:

   Create table <temp_table_name> as select * from index_stats where 1=2;

This create statement will copy the structure of the index_stats table but
not any of it's data.

In addition, a script is provided which will generate the execute of this
package for all indexes in the database.  This script can easily be modified
to capture only indexes for certain users.

This package and the capturing script have been written and provided to Oracle
support by customer, Michael Mack.  Be sure to test them in your environment
before relying on their output.

==============================================================================

CREATE PACKAGE DBMS_LOCAL IS
   PROCEDURE validate_index (schema IN VARCHAR2, index_name IN VARCHAR2,
   table_name IN VARCHAR2);
 --
 -- Analyzes the give schema.index_name and takes the row from index_stats
 -- and inserts into to a table.
 --
 -- Input parameters:
  --   schema = Owner of the index.
  --   index_name = The name of the index you want to analyze
  --   table_name = The name of the table you want to insert the row into
  --   from index_stats
  --

END DBMS_LOCAL;
/

CREATE PACKAGE BODY DBMS_LOCAL IS
   PROCEDURE validate_index (schema IN VARCHAR2, index_name IN VARCHAR2,
   table_name IN VARCHAR2) IS
     cid INTEGER;
     rows_processed INTEGER;
   BEGIN
 -- Open new cursor and return cursor ID.
 --
    cid := dbms_sql.open_cursor;
 --
 -- Parse and immediately execute dynamic SQL statement built by concatenating
 -- schema and index_name to analyze index command.
 --
    dbms_sql.parse(cid, 'ANALYZE INDEX ' || schema || '.' || index_name ||
       ' validate structure',dbms_sql.v7);
 --
 -- Parse and immediately execute dynamic SQL statement built by concatenating
 -- table_name to the insert statement.
 --
    dbms_sql.parse(cid, 'INSERT INTO '|| table_name || ' SELECT *
       FROM index_stats',dbms_sql.v7);
    rows_processed := dbms_sql.execute(cid);
 --
COMMIT;
 --
    dbms_sql.close_cursor(cid);
        EXCEPTION
          WHEN OTHERS THEN
            dbms_sql.close_cursor(cid);
            dbms_output.put_line('Went to exception ORA'||TO_CHAR(SQLCODE));
        END validate_index;
END DBMS_LOCAL;
/

==============================================================================

Below is a sample script that can be created to spool an execute of the above
package for all indexes in the database, except those owned by SYSTEM or SYS.
This script assumes the table created to preserve the index_stat information
is named 'TEMP_TABLE_NAME'.  The where clause of the query below can easily
be modified to achieve desired results.

set heading off
set pagesize 0
spool run_in2.sql
select 'spool run_in2.log' from dual;
select 'set serveroutput on' from dual;
select 'execute dbms_local.validate_index('
        ||chr(39)||owner||chr(39)||
        ','
        ||chr(39)||index_name||chr(39)||
        ','
        ||chr(39)||
        'TEMP_TABLE_NAME'
        ||chr(39)||');'
from   DBA_INDEXES, DUAL
where  OWNER not in ('SYS','SYSTEM')
/
select 'spool off' from dual;
spool off
exit