text: -- +----------------------------------------------------------------------------+
  -- |                          Jeffrey M. Hunter                                 |
  -- |                      jhunter@idevelopment.info                             |
  -- |                         www.idevelopment.info                              |
  -- |----------------------------------------------------------------------------|
  -- |      Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved.       |
  -- |----------------------------------------------------------------------------|
  -- | DATABASE : Oracle                                                          |
  -- | FILE     : perf_top_10_tables.sql                                          |
  -- | CLASS    : Tuning                                                          |
  -- | PURPOSE  : Report on top 10 tables with respect to usage and command type. |
  -- | NOTE     : As with any code, ensure to test this script in a development   |
  -- |            environment before attempting to run it in production.          |
  -- +----------------------------------------------------------------------------+
  
  SET LINESIZE 145
  SET PAGESIZE 9999
  SET VERIFY   off
  
  COLUMN ctyp      FORMAT a13                  HEADING 'Command Type'
  COLUMN obj       FORMAT a30                  HEADING 'Object Name'
  COLUMN noe       FORMAT 999,999,999,999,999  HEADING 'Number of Executions'
  COLUMN gets      FORMAT 999,999,999,999,999  HEADING 'Buffer Gets'
  COLUMN rowp      FORMAT 999,999,999,999,999  HEADING 'Rows Processed'
  
  BREAK ON report
  COMPUTE sum OF noe   ON report
  COMPUTE sum OF gets  ON report
  COMPUTE sum OF rowp  ON report
  
  SELECT
      ctyp
    , obj
    , 0 - exem noe
    , gets
    , rowp
  FROM (
      select distinct exem, ctyp, obj, gets, rowp 
      from (select
                DECODE(   s.command_type
                        , 2,  'Insert into '
                        , 3,  'Select from '
                        , 6,  'Update  of  '
                        , 7,  'Delete from '
                        , 26, 'Lock    of  ')   ctyp
              , o.owner || '.' || o.name        obj
              , SUM(0 - s.executions)           exem
              , SUM(s.buffer_gets)              gets
              , SUM(s.rows_processed)           rowp
            from
                v$sql                s
              , v$object_dependency  d
              , v$db_object_cache    o 
            where
                  s.command_type  IN (2,3,6,7,26) 
              and d.from_address  = s.address 
              and d.to_owner      = o.owner 
              and d.to_name       = o.name   
              and o.type          = 'TABLE' 
            group by
                s.command_type
              , o.owner
              , o.name
      )
  )
  WHERE rownum <= 10;
  