查询出当前热点Buffer相关对象的SQL语句

 

摘自【深入解析Oracle----作者：盖国强】

 

/* break on hash_value skip 1 :每个页面相同的hash_value只输出第一个，其它的跳过,在sqlplus有效，ps/sql developer 中貌似无效 */
break on hash_value skip 1

select /*+ rule */ hash_value, sql_text
  from v$sqltext
 where (hash_value, address) in (
             select a.hash_value, a.address
                from v$sqltext a,
                    (select distinct a.owner, a.segment_name, a.segment_type
                           from dba_extents a,
                               (select dbarfil, dbablk
                                  from (select dbarfil, dbablk
                                            from x$bh
                                        order by tch desc)
                                   where rownum < 11) b
                           where a.relative_fno = b.dbarfil
                             and a.block_id <= b.dbablk
                             and a.block_id + a.blocks > b.dbablk) b
         where upper(a.sql_text) like '%'||b.segment_name||'%'
           and b.segment_type = 'TABLE')
 order by hash_value, address, piece;