查找SCAN大量块的一个sql

1.sql如下

select *
  from ( select a.parsing_schema_name,
               sum(a.executions_delta) executions,
               sum(a.DISK_READS_delta) disk_reads,
               sum(a.DIRECT_WRITES_delta) direct_writes,
               sum(a.CPU_TIME_delta) / 1000000 / 60 cpu_time_min,
               sum(a.ELAPSED_TIME_delta) / 1000000 / 60 elapsed_time_min,
               sum(a.PHYSICAL_READ_BYTES_delta) / 1024 / 1024 / 1024 physical_read_gb,
               sum(a.physical_write_bytes_delta) / 1024 / 1024 / 1024 physical_write_gb,
               ( select sql_text
                  from dba_hist_sqltext c
                 where c.sql_id = a.sql_id
                   and rownum = 1)
          from DBA_HIST_SQLSTAT a, DBA_HIST_SNAPSHOT b
         where a.SNAP_ID = b.SNAP_ID
           and b.BEGIN_INTERVAL_TIME >=
               to_date( '2012-11-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) ---开始时间   
           and END_INTERVAL_TIME <=
               to_date( '2012-11-28 23:00:00', 'YYYY-MM-DD HH24:MI:SS' ) ---结束时间 
         group by parsing_schema_name, a.sql_id
         order by 3 desc)
 where rownum <= 50 ;

2.这个sql我们需要注意到的地方是这一句:

select sql_text from dba_hist_sqltext c where c.sql_id = a.sql_id and rownum = 1

3.什么是标量子查询?

标量子查询的引入是 Oracle9i中最显著的一个变化。以前 Oracle 允许在 SQL 语句的 FROM 子句中使用 SQL 子查询，Oracle9i扩展了这一功能，允许在SELECT 子句中使用 SQL 子查询。