/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display table statistics history
*  Parameters : 1 - OWNER               (% - wildchar, \ - escape char)
*               2 - Object Name         (% - wildchar, \ - escape char) 
*               3 - Partition Name      (% - wildchar, \ - escape char)
*               5 - Object Type         (% - wildchar, \ - escape char)
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  08-Jul-15  Vishal Gupta  Removed savetime column from output
*  19-JUN-14  Vishal Gupta  Updated AvgRowLength heading
*  28-FEB-14  Vishal Gupta  Changed the ordering
*  02-Jul-12  Vishal Gupta  Added partition, sub-partition and objecttype as input
*  30-Apr-12  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE owner
UNDEFINE object_name
UNDEFINE subobject_name
UNDEFINE object_type

DEFINE owner="&&1"
DEFINE object_name="&&2"
DEFINE subobject_name="&&3"
DEFINE object_type="&&4"



COLUMN  _owner             NEW_VALUE owner             NOPRINT
COLUMN  _object_name       NEW_VALUE object_name       NOPRINT
COLUMN  _subobject_name    NEW_VALUE subobject_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('&&object_name'),'','%',UPPER('&&object_name')) 
       END                                                          "_object_name"
     , DECODE('&&subobject_name','','%','&&subobject_name')         "_subobject_name"
     , DECODE('&&object_type','','%','&&object_type')               "_object_type"
FROM DUAL
;
set term on


PROMPT
PROMPT ***********************************************************************
PROMPT *  S T A T I S T I C S    H I S T O R Y
PROMPT *
PROMPT *  Input Parameters 
PROMPT *  - Object Owner       = '&&owner'
PROMPT *  - Object Name        = '&&object_name'
PROMPT *  - SubObject Name     = '&&subobject_name'
PROMPT *  - Object Type        = '&&object_type'
PROMPT ***********************************************************************

COLUMN object_name             HEADING "ObjectName"               FORMAT a60
COLUMN subobject_name          HEADING "SubObjectName"            FORMAT a30
COLUMN object_type             HEADING "Object|Type"              FORMAT a18
COLUMN object_type_sort_order                                                    NOPRINT
COLUMN savtime                 HEADING "SaveTime"                 FORMAT a18
COLUMN ANALYZETIME             HEADING "AnalyzeTime"              FORMAT a18
COLUMN samplesize              HEADING "SampleSize"               FORMAT 999,999,999,999
COLUMN rowcnt                  HEADING "RowCount"                 FORMAT 999,999,999,999
COLUMN blkcnt                  HEADING "BlockCount"               FORMAT 999,999,999
COLUMN avgrln                  HEADING "Avg|Row|Length"           FORMAT 999,999
COLUMN SizeMB                  HEADING "Size (MB)"                FORMAT 9,999,999

select object_name
     , object_type
     , object_type_sort_order
     --, to_char(savtime,'DD-MON-YY HH24:MI:SS') savtime
     , to_char(ANALYZETIME,'DD-MON-YY HH24:MI:SS') ANALYZETIME
     , SizeMB
     , rowcnt
     , avgrln
     --, blkcnt
     , samplesize
FROM
(
select o.owner || '.' || o.object_name 
       || NVL2(o.subobject_name,':' || o.subobject_name, '') 
       object_name 
     , o.object_type
     , DECODE (o.object_type
           , 'TABLE'              ,1
           , 'TABLE PARTITION'    ,2
           , 'TABLE SUBPARTITION' ,3
           , 'INDEX'              ,4
           , 'INDEX PARTITION'    ,5
           , 'INDEX SUBPARTITION' ,6
           ,9
           ) object_type_sort_order         
     , h.savtime
     , h.ANALYZETIME
     , h.samplesize
     , h.rowcnt
     , h.avgrln
     , (h.blkcnt * p.value) /1024/1024  SizeMB
     , h.blkcnt
 FROM sys.wri$_optstat_tab_history h
    , dba_objects o 
    , v$system_parameter p
WHERE h.obj# = o.object_id
  AND p.name = 'db_block_size'
  AND o.owner                      LIKE upper('&&owner')       ESCAPE '\'
  AND o.object_name                LIKE upper('&&object_name') ESCAPE '\'
  AND NVL(o.subobject_name,'%')    LIKE upper('&&subobject_name') ESCAPE '\'
  AND NVL(DECODE(o.object_type
               ,'PARTITION','TABLE PARTITION'
               ,'SUBPARTITION','TABLE SUBPARTITION'
               ,o.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'
UNION 
select s.owner || '.' || s.table_name 
       || NVL2(s.partition_name,':' || s.partition_name, '')
       object_name
 , DECODE(s.object_type
               ,'PARTITION','TABLE PARTITION'
               ,'SUBPARTITION','TABLE SUBPARTITION'
               ,s.object_type) object_type
 , DECODE (s.object_type
           , 'TABLE'              ,1
           , 'TABLE PARTITION'    ,2
           , 'TABLE SUBPARTITION' ,3
           , 'INDEX'              ,4
           , 'INDEX PARTITION'    ,5
           , 'INDEX SUBPARTITION' ,6
           ,9
           ) object_type_sort_order         
     , NULL savtime
  , last_analyzed ANALYZETIME
  , sample_size
  , num_rows
  , avg_row_len
  , (s.blocks * p.value) /1024/1024  SizeMB
  , s.blocks 
 FROM dba_tab_statistics s
    , v$system_parameter p
WHERE p.name = 'db_block_size'
  AND s.owner                     LIKE upper('&&owner')   ESCAPE '\'
  AND s.table_name                LIKE upper('&&object_name') ESCAPE '\'
  AND NVL(s.partition_name,'%')    LIKE upper('&&subobject_name') ESCAPE '\'
  AND NVL(DECODE(s.object_type
               ,'PARTITION','TABLE PARTITION'
               ,'SUBPARTITION','TABLE SUBPARTITION'
               ,s.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'
) a
ORDER BY a.object_name
       , a.object_type_sort_order
       , a.savtime NULLS LAST
       --, a.ANALYZETIME
/



COLUMN table_name               HEADING "IndexName"                  FORMAT a40
COLUMN object_name              HEADING "IndexName"                  FORMAT a50
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 object_name
     , object_type
     , object_type_sort_order
     --, to_char(savtime,'DD-MON-YY HH24:MI:SS') savtime
     , to_char(ANALYZETIME,'DD-MON-YY HH24:MI:SS') ANALYZETIME
     , SizeMB
     , rowcnt
     , samplesize
     , distinct_keys
     , blevel
     , clustering_factor
     , leaf_blocks
     , avg_leaf_blocks_per_key
     , avg_data_blocks_per_key
FROM     
(
select o.owner || '.' || o.object_name 
       || NVL2(o.subobject_name,':' || o.subobject_name, '')
       object_name
     , o.object_type
     , DECODE (o.object_type
           , 'TABLE'              ,1
           , 'TABLE PARTITION'    ,2
           , 'TABLE SUBPARTITION' ,3
           , 'INDEX'              ,4
           , 'INDEX PARTITION'    ,5
           , 'INDEX SUBPARTITION' ,6
           ,9
           ) object_type_sort_order         
     , h.savtime      
     , h.ANALYZETIME
     , (h.leafcnt * p.value) /1024/1024  SizeMB
     , h.rowcnt
     , h.samplesize
     , h.distkey distinct_keys
     , h.blevel
     , h.clufac   clustering_factor
     , h.leafcnt  leaf_blocks
     , h.lblkkey  avg_leaf_blocks_per_key
     , h.dblkkey  avg_data_blocks_per_key
     --, h.avgrln
 FROM sys.wri$_optstat_ind_history h
    , dba_objects o 
    , v$system_parameter p
WHERE h.obj# = o.object_id
  AND p.name = 'db_block_size'
  AND o.owner                      LIKE upper('&&owner')          ESCAPE '\'
  AND o.object_name                LIKE upper('&&object_name')    ESCAPE '\'
  AND NVL(o.subobject_name,'%')    LIKE upper('&&subobject_name') ESCAPE '\'
  AND NVL(o.object_type,'%')       LIKE upper('&&object_type')    ESCAPE '\'
UNION ALL
select s.owner || '.' || s.index_name 
       || NVL2(s.partition_name,':' || s.partition_name, '')
       object_name
   , DECODE(s.object_type
               ,'PARTITION','TABLE PARTITION'
               ,'SUBPARTITION','TABLE SUBPARTITION'
               ,s.object_type) object_type
   , DECODE (s.object_type
           , 'TABLE'              ,1
           , 'TABLE PARTITION'    ,2
           , 'TABLE SUBPARTITION' ,3
           , 'INDEX'              ,4
           , 'INDEX PARTITION'    ,5
           , 'INDEX SUBPARTITION' ,6
           ,9
           ) object_type_sort_order         
  , null savtime         
  , s.last_analyzed ANALYZETIME
  , ROUND((s.leaf_blocks * p.value) /1024/1024 ) SizeMB
  , s.num_rows
  , s.sample_size
  , s.distinct_keys
  , s.blevel
  , s.clustering_factor
  , s.leaf_blocks
  , s.avg_leaf_blocks_per_key
  , s.avg_data_blocks_per_key
 FROM dba_ind_statistics s
    , v$system_parameter p
WHERE p.name = 'db_block_size'
  AND s.owner                      LIKE upper('&&owner') ESCAPE '\'
  AND s.index_name                 LIKE upper('&&object_name') ESCAPE '\'
  AND NVL(s.partition_name,'%')     LIKE upper('&&subobject_name') ESCAPE '\'
  AND NVL(DECODE(s.object_type
               ,'PARTITION','INDEX PARTITION'
               ,'SUBPARTITION','INDEX SUBPARTITION'
               ,s.object_type),'%')       LIKE upper('&&object_type') ESCAPE '\'  
) a
ORDER BY a.object_name
       , a.object_type_sort_order
       , a.savtime NULLS LAST
     --, a.ANALYZETIME
/