查询数据库热点对象

--description:start with Oracle9i,can use this script to direct determine the hot object.
--syntax:@segment_io(object_name or all)(schema)

SELECT OBJECT_NAME SEGMENT_NAME,
       max(DECODE(STATISTIC_NAME, 'segment scans', value, null)) full_scan,
       max(DECODE(STATISTIC_NAME, 'logical reads', value, null)) logical_reads,
       max(DECODE(STATISTIC_NAME, 'physical reads', value, null)) physical_reads,
       max(DECODE(STATISTIC_NAME, 'physical writes', value, null)) physical_writes,
       max(DECODE(STATISTIC_NAME, 'physical reads direct', value, null)) physical_reads_direct,
       max(DECODE(STATISTIC_NAME, 'physical writes direct', value, null)) physical_writes_direct
  FROM V$SEGMENT_STATISTICS
 WHERE OBJECT_NAME = decode(upper('&object_name'),
                            'ALL',
                            OBJECT_NAME,
                            upper('&object_name'))
   AND OWNER = UPPER('&OWNER')
   AND STATISTIC_NAME IN
       ('logical reads', 'physical reads', 'physical writes',
        'physical reads direct', 'physical writes direct', 'segment scans')
 group by OBJECT_NAME;