励维营销:To Capture Index Stat Information

Product Name, Product Version

Execution Environment:     Access Privileges:     The script should be run as user SYS.Usage:     SQL> execute dbms_local.validate_index(schema, index_name, table_name);

     eg:     SQL> execute dbms_local.validate_index('OEMADMIN','EX_PK','TEMP_TABLE_NAME');Instructions:     Copy the script into a file named indexan.sql. Execute the script fro SQL*Plus     connected as SYS.PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable statewhen you first receive it. Check over the script to ensure that errors ofthis type are corrected.The script will produce an output file named [outputfile].This file can be viewed in a browser or uploaded for support analysis.

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

In addition, the following script could be used to generate the execute statements of this procedure for all indexes in the database.  The script can be easily modified to capture only indexes for certain schemas. SET ECHO offREM NAME:    TFSINST2.SQLREM USAGE:"@path/tfsinst2"REM ------------------------------------------------------------------------REM REQUIREMENTS:REM    EXECUTE on procedure (or ANY), ANALYZE ANYREM ------------------------------------------------------------------------REM AUTHOR: REM    Michael Mack   REM ------------------------------------------------------------------------REM PURPOSE:REM    Runs the dbms_local.validate_index for ever non SYS or SYSTEMREM    owned table in the database.REM ------------------------------------------------------------------------REM DISCLAIMER:REM    This script is provided for educational purposes only. It is NOT REM    supported by Oracle World Wide Technical Support.REM    The script has been tested and appears to work as intended.REM    You should always run new scripts on a test instance initially.REM ------------------------------------------------------------------------REM Main text of script follows:set heading offset pagesize 0set linesize 132spool run_in2.sqlselect '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, DUALwhere  OWNER not in ('SYS','SYSTEM')/select 'spool off' from dual;spool off=============Sample Output=============execute dbms_local.validate_index('IHT','PK_MED_LOV','TEMP_TABLE_NAME');execute dbms_local.validate_index('OWNER1','ELEM_11','TEMP_TABLE_NAME');execute dbms_local.validate_index('OWNER1','ELEM_12','TEMP_TABLE_NAME');execute dbms_local.validate_index('OWNER1','ELEM_13','TEMP_TABLE_NAME');execute dbms_local.validate_index('OWNER1','ELEM_14','TEMP_TABLE_NAME');execute dbms_local.validate_index('OWNER1','ELEM_15','TEMP_TABLE_NAME');

[Include references to  FAQ, Troubleshooting guide, and Current issuesArticles from Top Tech Docs or other relevant references.]

SET ECHO offREM NAME:INDEXAN.SQLREM USAGE:"@path/tfsinsta"REM ------------------------------------------------------------------------REM REQUIREMENTS:REM    Should be run as SYSREM ------------------------------------------------------------------------REM AUTHOR: REM    Michael Mack   REM ------------------------------------------------------------------------REM PURPOSE:REM    Creates a package that which will automatically analyze indexes REM    with the 'validate structure' option.  It will also copy the REM    data inserted into the index_stats table to a temporary table REM    for  future analysis.    REM ------------------------------------------------------------------------REM EXAMPLE:REM    N/AREM ------------------------------------------------------------------------REM DISCLAIMER:REM    This script is provided for educational purposes only. It is NOT REM    supported by Oracle World Wide Technical Support.REM    The script has been tested and appears to work as intended.REM    You should always run new scripts on a test instance initially.REM ------------------------------------------------------------------------REM Main text of script follows:CREATE OR REPLACE 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 OR REPLACE 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; / CREATE OR REPLACE 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 OR REPLACE 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; / DROP TABLE TEMP_TABLE_NAME;CREATE TABLE TEMP_TABLE_NAME AS SELECT * FROM INDEX_STATS WHERE 1=2;

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMSALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOTLIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULARPURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTSTHAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE ORRELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUREXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OROTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVENO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THATRESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANYTIME WITHOUT NOTICE.

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION INCONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.