Keep Buffer 대상 선정 SQL

select owner,   --결과에서 프로그램 중요도 선정하여 선택
        table_name,
        index_name,
        partition_name,
        sum(blocks) as t_blocks
 from (select sg.owner,
              decode(substr(s.ob_type,1,5),'TABLE',s.ob_name,'INDEX',
             ( select table_name
                from DBA_INDEXES
               where index_name = s.ob_name)) as table_name,
        decode(substr(s.ob_type,1,5),'INDEX',s.ob_name) as index_name,
        sg.partition_name,
        sg.blocks
        from (
            select distinct object_name as ob_name,
                            object_type as ob_type
             from v$sql_plan
             where (operation ='TABLE ACCESS'
                    and options ='FULL')
             or (operation ='INDEX'
                   and options ='FULL SCAN')
             or (operation ='INDEX'
                   and options ='FAST FULL SCAN') --full(table,index,index fast) usage
             ) s ,
             dba_segments sg
    where s.ob_name = sg.SEGMENT_NAME
    )
 group by owner,
          table_name,
          index_name,
          partition_name
having sum(blocks) > 100000;  --segment size