Segments by Logical Reads
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.logical_reads,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.logical_reads_total - nvl(b.logical_reads_total, 0) logical_reads,
                       ratio_to_report(e.logical_reads_total -
                                       nvl(b.logical_reads_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.logical_reads_total - nvl(b.logical_reads_total, 0) > 0
                 ORDER BY logical_reads DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY logical_reads DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments by Physical Reads
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.physical_reads,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.physical_reads_total -
                       nvl(b.physical_reads_total, 0) physical_reads,
                       ratio_to_report(e.physical_reads_total -
                                       nvl(b.physical_reads_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.physical_reads_total -
                       nvl(b.physical_reads_total, 0) > 0
                 ORDER BY physical_reads DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY physical_reads DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments by Row Lock Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.row_lock_waits,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.row_lock_waits_total -
                       nvl(b.row_lock_waits_total, 0) row_lock_waits,
                       ratio_to_report(e.row_lock_waits_total -
                                       nvl(b.row_lock_waits_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.row_lock_waits_total -
                       nvl(b.row_lock_waits_total, 0) > 0
                 ORDER BY row_lock_waits DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY row_lock_waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments by ITL Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       n.subobject_name,
       n.object_type,
       r.itl_waits,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.itl_waits_total - nvl(b.itl_waits_total, 0) itl_waits,
                       ratio_to_report(e.itl_waits_total -
                                       nvl(b.itl_waits_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.itl_waits_total - nvl(b.itl_waits_total, 0) > 0
                 ORDER BY itl_waits DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY itl_waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments by Buffer Busy Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.buffer_busy_waits,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.buffer_busy_waits_total -
                       nvl(b.buffer_busy_waits_total, 0) buffer_busy_waits,
                       ratio_to_report(e.buffer_busy_waits_total -
                                       nvl(b.buffer_busy_waits_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.buffer_busy_waits_total -
                       nvl(b.buffer_busy_waits_total, 0) > 0
                 ORDER BY buffer_busy_waits DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY buffer_busy_waits DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments by Global Cache Buffer Busy Waits
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.gc_buffer_busy,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.gc_buffer_busy_total -
                       nvl(b.gc_buffer_busy_total, 0) gc_buffer_busy,
                       ratio_to_report(e.gc_buffer_busy_total -
                                       nvl(b.gc_buffer_busy_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.gc_buffer_busy_total -
                       nvl(b.gc_buffer_busy_total, 0) > 0
                 ORDER BY gc_buffer_busy DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY gc_buffer_busy DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments by CR Blocks Received(was Served in versions prior to 10g)
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.cr_blocks_received,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.gc_cr_blocks_received_total -
                       nvl(b.gc_cr_blocks_received_total, 0) cr_blocks_received,
                       ratio_to_report(e.gc_cr_blocks_received_total -
                                       nvl(b.gc_cr_blocks_received_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.gc_cr_blocks_received_total -
                       nvl(b.gc_cr_blocks_received_total, 0) > 0
                 ORDER BY cr_blocks_received DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY cr_blocks_received DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Segments By Current Blocks Received(was Served in versions prior to 10g)
++++-分析-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT n.owner,
       n.tablespace_name,
       n.object_name,
       CASE
         WHEN length(n.subobject_name) < 11 THEN
          n.subobject_name
         ELSE
          substr(n.subobject_name, length(n.subobject_name) - 9)
       END subobject_name,
       n.object_type,
       r.cu_blocks_received,
       substr(to_char(r.ratio * 100, '999.9MI'), 1, 5) ratio
  FROM dba_hist_seg_stat_obj n,
       (SELECT *
          FROM (SELECT e.dataobj#,
                       e.obj#,
                       e.ts#,
                       e.dbid,
                       e.gc_cu_blocks_received_total -
                       nvl(b.gc_cu_blocks_received_total, 0) cu_blocks_received,
                       ratio_to_report(e.gc_cu_blocks_received_total -
                                       nvl(b.gc_cu_blocks_received_total, 0)) over() ratio
                  FROM dba_hist_seg_stat e, dba_hist_seg_stat b
                 WHERE b.snap_id(+) = &bid
                   AND e.snap_id = &eid
                   AND b.dbid(+) = &dbid
                   AND e.dbid = &dbid
                   AND b.instance_number(+) = &inst_num
                   AND e.instance_number = &inst_num
                   AND b.ts#(+) = e.ts#
                   AND b.obj#(+) = e.obj#
                   AND b.dataobj#(+) = e.dataobj#
                   AND e.gc_cu_blocks_received_total -
                       nvl(b.gc_cu_blocks_received_total, 0) > 0
                 ORDER BY cu_blocks_received DESC) d
         WHERE rownum <= 10) r
 WHERE n.dataobj# = r.dataobj#
   AND n.obj# = r.obj#
   AND n.ts# = r.ts#
   AND n.dbid = r.dbid
 ORDER BY cu_blocks_received DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++