@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Statistics of a table (including part, sub-par, ind, ind-part, ind-subpart)
*  Parameters : 1 - OWNER
*               2 - Table Name
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  16-Mar-12  Vishal Gupta  Intial version
*
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE owner
UNDEFINE table_name
UNDEFINE partition_name
UNDEFINE subpartition_name
UNDEFINE object_type

DEFINE owner="&&1"
DEFINE table_name="&&2"
DEFINE partition_name="&&3"
DEFINE subpartition_name="&&4"
DEFINE object_type="&&5"


COLUMN  _owner             NEW_VALUE owner             NOPRINT
COLUMN  _table_name        NEW_VALUE table_name        NOPRINT
COLUMN  _partition_name    NEW_VALUE partition_name    NOPRINT
COLUMN  _subpartition_name NEW_VALUE subpartition_name NOPRINT
COLUMN  _object_type       NEW_VALUE object_type       NOPRINT

set term off
SELECT CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1) 
           ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner')) 
       END                                                          "_owner"
     , CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) 
           ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name')) 
       END                                                          "_table_name"
     , DECODE('&&partition_name','','%','&&partition_name')         "_partition_name"
     , DECODE('&&subpartition_name','','%','&&subpartition_name')   "_subpartition_name"
     , DECODE('&&object_type','','%','&&object_type')               "_object_type"
FROM DUAL;
set term on




PROMPT
PROMPT ***********************************************************************
PROMPT *  Z E R O    S T A L E   O B J E C T    S T A T I S T I C S
PROMPT *
PROMPT *  Input Parameters 
PROMPT *  - Table Owner       = '&&owner'
PROMPT *  - Table Name        = '&&table_name'
PROMPT *  - Partition Name    = '&&partition_name'
PROMPT *  - SubPartition Name = '&&subpartition_name'
PROMPT *  - Object Type       = '&&object_type'
PROMPT ***********************************************************************


PROMPT
PROMPT  ***********************************
PROMPT  *  Zero and Stale Table Statistics 
PROMPT  ***********************************
PROMPT

COLUMN owner                   HEADING "Owner"                    FORMAT a20
COLUMN table_name              HEADING "TableName"                FORMAT a30
COLUMN index_name              HEADING "IndexName"                FORMAT a30
COLUMN partition_name          HEADING "Partition"                FORMAT a25
COLUMN partition_position      HEADING "Part|Pos"                 FORMAT 999
COLUMN subpartition_name       HEADING "Sub-Parition"             FORMAT a25
COLUMN subpartition_position   HEADING "Sub|Part|Pos"             FORMAT 999
COLUMN last_analyzed           HEADING "LastAnalyzed"             FORMAT a18
COLUMN last_analyzed           HEADING "LastAnalyzed"             FORMAT a18
COLUMN stattype_locked         HEADING "Lock"                     FORMAT a5
COLUMN global_stats            HEADING "Global|Stat"              FORMAT a5
COLUMN user_stats              HEADING "User|Stat"                FORMAT a4
COLUMN stale_stats             HEADING "Stale|Stat"               FORMAT a5


select /*+ RULE */ 
       s.owner
     , s.table_name
--   , s.object_type
     , s.partition_name
--   , s.partition_position
     , s.subpartition_name
--   , s.subpartition_position
     , s.stattype_locked
     , s.global_stats
     , s.user_stats
     , s.stale_stats
     , to_char(s.last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
     , s.sample_size
     , s.num_rows
     , s.blocks
     , s.empty_blocks
     , s.avg_space
     , s.chain_cnt
     , s.avg_row_len
 FROM dba_tab_statistics s
      JOIN dba_tables t ON  t.owner        = s.owner 
                        AND t.table_name   = s.table_name 
                        AND t.temporary    = 'N'
WHERE s.owner LIKE upper('&&owner')
  AND s.table_name LIKE upper('&&table_name')
  AND NVL(s.partition_name,'%')    LIKE upper('&&partition_name') ESCAPE '\'
  AND NVL(s.subpartition_name,'%') LIKE upper('&&subpartition_name') ESCAPE '\'
  AND NVL(DECODE(s.object_type
               ,'PARTITION','TABLE PARTITION'
               ,'SUBPARTITION','TABLE SUBPARTITION'
               ,s.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'  
  AND s.table_name not like 'BIN$%'
  AND s.num_rows = 0 
  AND s.stale_stats = 'YES'
  AND s.owner NOT IN ('SYS','XDB')     
ORDER BY s.owner
       , s.table_name
       , s.partition_position
       , s.subpartition_position
/


PROMPT
PROMPT  ***********************************
PROMPT  *  Zero and Stale Index Statistics 
PROMPT  ***********************************


COLUMN table_name               HEADING "TableName"                 FORMAT a30
COLUMN index_name               HEADING "IndexName"                 FORMAT a65
COLUMN distinct_keys            HEADING "DistinctKeys"              FORMAT 99,999,999,999
COLUMN blevel                   HEADING "BLevel"                    FORMAT 99999
COLUMN leaf_blocks              HEADING "Leaf|Blocks"               FORMAT 99,999,999
COLUMN clustering_factor        HEADING "Clustering|Factor"         FORMAT 99,999,999,999
COLUMN avg_leaf_blocks_per_key  HEADING "Avg|LeafBlocks|PerKey"     FORMAT 999,999,999
COLUMN avg_data_blocks_per_key  HEADING "Avg|DataBlocks|PerKey"     FORMAT 999,999,999


select /*+ RULE */ 
       s.owner
     , s.table_name
--   , s.object_type
     , s.index_name
     , s.partition_name
--   , s.partition_position
     , s.subpartition_name
--   , s.subpartition_position
     , s.stattype_locked
     , s.global_stats
     , s.user_stats
     , s.stale_stats
     , to_char(s.last_analyzed,'DD-MON-YY HH24:MI:SS') last_analyzed
     , s.sample_size
     , s.blevel
     , s.num_rows
     , s.leaf_blocks
     , s.clustering_factor
 FROM dba_ind_statistics s
      JOIN dba_indexes i ON i.owner        = s.owner 
                        AND i.index_name   = s.index_name 
                        AND i.temporary    = 'N'
WHERE s.owner                      LIKE upper('&&owner') ESCAPE '\'
  AND s.table_name                 LIKE upper('&&table_name') ESCAPE '\'
  AND NVL(s.partition_name,'%')     LIKE upper('&&partition_name') ESCAPE '\'
  AND NVL(s.subpartition_name,'%')  LIKE upper('&&subpartition_name') ESCAPE '\'
  AND NVL(DECODE(s.object_type
               ,'PARTITION','INDEX PARTITION'
               ,'SUBPARTITION','INDEX SUBPARTITION'
               ,s.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'
  AND s.table_name not like 'BIN$%'
  AND s.num_rows = 0 
  and s.stale_stats = 'YES'
  AND s.owner NOT IN ('SYS','XDB')     
ORDER BY s.owner
       , s.table_name
     , s.index_name
       , s.partition_position
       , s.subpartition_position
/


UNDEFINE owner
UNDEFINE table_name
UNDEFINE partition_name
UNDEFINE subpartition_name
UNDEFINE object_type

@@footer