SQL ordered by Reads(源码)

 select *
     from (select sqt.dskr,
                  sqt.exec,
                  decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)),
                  (100 * sqt.dskr) /
                  (SELECT sum(e.VALUE) - sum(b.value)
                     FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                    WHERE B.SNAP_ID = &beg_snap
                      AND E.SNAP_ID = &end_snap
                      AND B.DBID = &DBID
                      AND E.DBID = &DBID
                      AND B.INSTANCE_NUMBER = &INST_NUM
                      AND E.INSTANCE_NUMBER = &INST_NUM
                      and e.STAT_NAME = 'physical reads'
                      and b.stat_name = 'physical reads') norm_val,
                  nvl((sqt.cput / 1000000), to_number(null)),
                  nvl((sqt.elap / 1000000), to_number(null)),
                  sqt.sql_id,
                  decode(sqt.module, null, null, 'Module: ' || sqt.module),
                  nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
             from (select sql_id,
                          max(module) module,
                          sum(disk_reads_delta) dskr,
                          sum(executions_delta) exec,
                          sum(cpu_time_delta) cput,
                          sum(elapsed_time_delta) elap
                     from dba_hist_sqlstat
                    where dbid = &dbid
                      and instance_number = &inst_num
                      and &beg_snap < snap_id
                      and snap_id <= &end_snap
                    group by sql_id) sqt,
                  dba_hist_sqltext st
            where st.sql_id(+) = sqt.sql_id
              and st.dbid(+) = &dbid
              and (SELECT sum(e.VALUE) - sum(b.value)
                     FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                    WHERE B.SNAP_ID = &beg_snap
                      AND E.SNAP_ID = &end_snap
                      AND B.DBID = &DBID
                      AND E.DBID = &DBID
                      AND B.INSTANCE_NUMBER = &INST_NUM
                      AND E.INSTANCE_NUMBER = &INST_NUM
                      and e.STAT_NAME = 'physical reads'
                      and b.stat_name = 'physical reads') > 0
            order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
    where rownum < 65and(rownum <= 10
                      or norm_val > 1)

---------------------------------------------------实际应用的SQL--------------------------------------------------------

select *
  from (select sqt.dskr "Physical Reads",
               sqt.exec,
               decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) "Reads per Exec",
               (100 * sqt.dskr) /
               (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID =
                       (select snap_id
                          from DBA_HIST_SNAPSHOT
                         where to_char(end_interval_time, 'yyyymmdd hh24') =
                               '20111121 08')
                   AND E.SNAP_ID =
                       (select snap_id
                          from DBA_HIST_SNAPSHOT
                         where to_char(end_interval_time, 'yyyymmdd hh24') =
                               '20111121 18')
                   AND B.DBID = 911569205
                   AND E.DBID = 911569205
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = 'physical reads'
                   and b.stat_name = 'physical reads') "%Total",
               nvl((sqt.cput / 1000000), to_number(null)) "CPU Time (s)",
               nvl((sqt.elap / 1000000), to_number(null)) "Elapsed Time (s)",
               sqt.sql_id,
               decode(sqt.module, null, null, 'Module: ' || sqt.module) "SQL Module",
               nvl(st.sql_text, to_clob('** SQL Text Not Available **')) "SQL Text"
          from (select sql_id,
                       max(module) module,
                       sum(disk_reads_delta) dskr,
                       sum(executions_delta) exec,
                       sum(cpu_time_delta) cput,
                       sum(elapsed_time_delta) elap
                  from dba_hist_sqlstat
                 where dbid = 911569205
                   and instance_number = 1
                   and snap_id >
                       (select snap_id
                          from DBA_HIST_SNAPSHOT
                         where to_char(end_interval_time, 'yyyymmdd hh24') =
                               '20111121 08')
                   and snap_id <=
                       (select snap_id
                          from DBA_HIST_SNAPSHOT
                         where to_char(end_interval_time, 'yyyymmdd hh24') =
                               '20111121 18')
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 911569205
           and (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID =
                       (select snap_id
                          from DBA_HIST_SNAPSHOT
                         where to_char(end_interval_time, 'yyyymmdd hh24') =
                               '20111121 08')
                   AND E.SNAP_ID =
                       (select snap_id
                          from DBA_HIST_SNAPSHOT
                         where to_char(end_interval_time, 'yyyymmdd hh24') =
                               '20111121 18')
                   AND B.DBID = 911569205
                   AND E.DBID = 911569205
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = 'physical reads'
                   and b.stat_name = 'physical reads') > 0
         order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
 where rownum < 65and(rownum <= 10
                   or "%Total" > 1)