【TUNE_ORACLE】列出走了TABLE ACCESS FULL的SQL參考

實驗環境

搭建平臺：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL參考

--可按表大小、表的總欄位數、表的欄位實際訪問數量來定製化該指令碼

select a.sql_id,

       a.sql_text,

       d.table_name,

       REGEXP_COUNT(b.projection, ']') || '/' || d.column_cnt column_cnt,

       c.size_mb,

       b.FILTER_PREDICATES filter

  from v$sql a,

       v$sql_plan b,

       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb

          from dba_segments

         group by owner, segment_name) c,

       (select owner, table_name, count(*) column_cnt

          from dba_tab_cols

         group by owner, table_name) d

 where a.sql_id = b.sql_id

   and a.child_number = b.child_number

   and b.object_owner = c.owner

   and b.object_name = c.segment_name

   and b.object_owner = d.owner

   and b.object_name = d.table_name

   and c.owner = 'TEST'

   and b.operation = 'TABLE ACCESS'

   and b.options = 'FULL'

 order by 5 desc;