@@header

PROMPT *********************
PROMPT SCRIPT INCOMPLETE
PROMPT *********************

set term off
/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display index usage from AWR data.
*  Parameters : NONE
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  26-May-12  Vishal Gupta  Created
*
*
*/
set term on

COLUMN table_owner          HEADING "Table Owner"            FORMAT a20
COLUMN table_name           HEADING "Table Name"             FORMAT a20
COLUMN index_name           HEADING "Index Name"             FORMAT a20
COLUMN Monitoring           HEADING "Monitoring"             FORMAT a10
COLUMN Used                 HEADING "Used"                   FORMAT a4


SELECT object_name
     , sql_id
     , ROUND(avg(daily_exections)) avg_daily_exections
FROM     
(
      SELECT p.object_name
           , p.sql_id
         --, p.plan_hash_value
           , to_char(end_interval_time,'YYYY-MM-DD') Day
           , SUM(ss.executions_delta) daily_exections
      FROM dba_hist_sql_plan p
         , dba_hist_sqlstat ss
         , dba_hist_snapshot s
      WHERE s.dbid              = ss.dbid
        AND s.instance_number  = ss.instance_number
        AND s.snap_id          = ss.snap_id
        AND ss.plan_hash_value = p.plan_hash_value
        AND ss.dbid            = p.dbid
        AND ss.sql_id          = p.sql_id
        AND (p.object_name LIKE 'TRN_IX%'  OR p.object_name = 'TRN_UK1')
        AND p.object_name =  'TRN_IX13'
      --AND s.end_interval_time BETWEEN TO_DATE('01-Jun-12','DD-Mon-YY') and TO_DATE('30-Jun-12','DD-Mon-YY')
      GROUP BY p.object_name
             , p.sql_id
           --, p.plan_hash_value
             , to_char(end_interval_time,'YYYY-MM-DD')
--      having  sum(ss.executions_delta) > 1      
      ORDER BY p.object_name
              , p.sql_id
            --, p.plan_hash_value
              , sum(ss.executions_delta) desc
)
GROUP BY object_name
        , sql_id
--HAVING  ROUND(avg(daily_exections)) > 10        
ORDER BY object_name
       , sql_id
       , avg_daily_exections desc
   ;

   
@@footer