Rem Filename : indexqual.sql Rem Rem Parameters : &1 = owner[.table(pattern)] Rem The default value for table(pattern) is %. Rem Rem Author : Rem Rem Modification history Rem -------------------- Rem 17-Sep-2005 V1.0 Initial creation. Rem 25-Sep-2006 V1.1 Added column "# gets Full Index Scan", result is not correct, probably need to use the index size Rem Rem ============================================================================================================================================== Rem Rem Description : Lists an overview of the index quality properties. Rem Rem Table name : The name of the table Rem Index name : The name of the index Rem Distinct Key Values : The number of distinct key values in the index. Rem Index Selectivity : 100 * ( Number of distinct key values / number of rows in the table ) Rem Clustering Factor : A indicator how badly rows with the same index key value are spread over multiple data blocks. Rem The larger the number, the worse the situation. Rem # gets Full Table Scan : The number of block gets needed to perform a full table scan, corresponds with the number of blocks in Rem the table minus the empty blocks (=HWM). Rem # gets Full index Scan : The number of block gets needed to perform a full table scan, corresponds with the number of blocks in Rem the table minus the empty blocks (=HWM). Rem Avg # gets Single key Index Access : The average number of gets performed to perform a full index scan. Rem Avg # gets Single key Index Onlys : The average number of gets performed to read all index leafs for a single key value. Oracle uses this Rem method if all queried information can be retrieved from the index itself without further accessing Rem the table data segment. Rem # gets WHEN EXISTS : The number of gets needed to resolve a "WHEN EXISTS" where condition for a single key value. Rem Rem Rem Sample output Rem ------------- Rem @indexqual est_core Rem Rem ---------------------------------------------------------------------------------------------------------------------------------------------- Rem Index quality report for all indexes on tables matching pattern Rem Rem of database PDB1 on server deeisep1 Rem ---------------------------------------------------------------------------------------------------------------------------------------------- Rem Current date and time : Wednesday 07-JUN-2006 16:40:31 Rem ---------------------------------------------------------------------------------------------------------------------------------------------- Rem Rem # gets Avg # gets Avg # gets Rem Distinct Index Clustering Full Single key Single key # gets Rem Table name Index name Key values Selectivity Factor Table Scan Index Access Index Only WHEN EXISTS Rem ------------------------------ ------------------------------ ---------- ----------- ---------- ---------- ------------ ---------- ----------- Rem .. Rem CO_PO_OPTIONS XIF1CO_PO_OPTIONS 13666 2 6952 2659 4 3 3 Rem XIF2CO_PO_OPTIONS 418 0 264570 2659 645 13 3 Rem XIF3CO_PO_OPTIONS 6 0 12473 2659 2143 65 2 Rem XPKCO_PO_OPTIONS 910303 100 21591 2659 4 3 3 Rem CO_PO_PART_INFOS XAK1CO_PO_PART_INFOS 3393561 100 37137 34676 4 3 3 Rem XIF1CO_PO_PART_INFOS 13666 0 41417 34676 6 3 3 Rem XIF2CO_PO_PART_INFOS 2086 0 1489735 34676 725 11 3 Rem XIF3CO_PO_PART_INFOS 523 0 3389059 34676 6514 34 3 Rem XPKCO_PO_PART_INFOS 3393561 100 37137 34676 4 3 3 Rem .. Rem ============================================================================================================================================== SET PAGESIZE 0 SET TRIMSPOOL ON SET VERIFY off SET TERMOUT OFF SET FEEDBACK OFF ALTER SESSION SET nls_numeric_characters = ",."; -- Disect the input argument, and get the owner name and table pattern into two seperate -- substitution variables. The table pattern defaults to %. DEFINE InputOwner = ' ' DEFINE InputTableFilter = ' ' COLUMN table_owner NOPRINT NEW_VALUE InputOwner COLUMN table_name NOPRINT NEW_VALUE InputTableFilter SELECT decode(instr('&&1','.'), 0, upper('&&1'), /* Only the table owner was passed in. */ upper(substr('&&1',1,instr('&&1','.')-1))) table_owner, decode(instr('&&1','.'), 0, '%', /* Only the table owner was passed in. */ upper(substr('&&1',instr('&&1','.')+1))) table_name FROM dual; CLEAR COLUMNS SET PAGESIZE 9999 SET TERMOUT ON @@header_title 153 DC 'Index quality report for all indexes on tables matching pattern <&InputOwner..&InputTableFilter>' SET FEEDBACK ON COLUMN "TableName" FORMAT A20 HEADING "Table name" COLUMN "IndexName" FORMAT A25 HEADING "Index name" COLUMN "DistinctKeys" FORMAT B999999999 HEADING "Distinct|Key values" COLUMN "ClustFact" FORMAT B999999999 HEADING "Clustering|Factor" COLUMN "Selectivity" FORMAT A11 HEADING " Index|Selectivity" COLUMN "HWM" FORMAT 9999999 HEADING "# gets|Full|Table Scan" COLUMN "LeafBlocks" FORMAT 9999999 HEADING "# gets|Full|Index Scan" COLUMN "AvgCostInd" FORMAT B9999999 HEADING "Avg # gets|Single key|Index Access" COLUMN "AvgCostIndOnly" FORMAT B9999999 HEADING "Avg # gets|Single key|Index Only" COLUMN "CostIfExists" FORMAT 9999999 HEADING "# gets|WHEN EXISTS" BREAK ON "TableName" NODUP set lines 300 SELECT i.table_name TableName, i.index_name IndexName, i.distinct_keys DistinctKeys, CASE t.num_rows WHEN 0 THEN '' ELSE lpad(to_char((100*i.distinct_keys)/t.num_rows, '990'),11) END Selectivity, i.clustering_factor ClustFact, t.blocks-t.empty_blocks HWM, i.leaf_blocks LeafBlocks, i.blevel+i.avg_leaf_blocks_per_key+avg_data_blocks_per_key AvgCostInd, i.blevel+i.avg_leaf_blocks_per_key AvgCostIndOnly, decode(i.avg_leaf_blocks_per_key,0,null,i.blevel)+1 CostIfExists, to_char(i.last_analyzed,'ddmmyyyy hh24:mi:ss') last_analyzed FROM dba_indexes i, dba_tables t WHERE i.table_owner = '&InputOwner' AND i.table_name like '&InputTableFilter' AND t.owner = i.table_owner AND t.table_name = i.table_name ORDER BY i.table_name, i.index_name; SELECT i.table_name TableName, i.index_name IndexName, i.distinct_keys DistinctKeys, CASE t.num_rows WHEN 0 THEN '' ELSE lpad(to_char((100*i.distinct_keys)/t.num_rows, '990'),11) END Selectivity, i.clustering_factor ClustFact, i.blevel blevel, s.bytes/1024/1024 MB, to_char(o.last_ddl_time,'ddmmyyyy hh24:mi:ss') last_ddl_time, to_char(i.last_analyzed,'ddmmyyyy hh24:mi:ss') last_analyzed FROM dba_indexes i, dba_tables t, dba_segments s, dba_objects o WHERE i.table_owner = '&InputOwner' AND i.table_name like '&InputTableFilter' AND t.owner = i.table_owner AND t.table_name = i.table_name AND s.owner = t.owner AND s.segment_name = i.index_name AND o.owner = t.owner AND o.object_name = i.index_name ORDER BY i.table_name, i.index_name; SET FEEDBACK ON CLEAR BREAKS CLEAR COLUMNS UNDEFINE InputOwner UNDEFINE InputTableFilter