본문 바로가기

Oracle/admin

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

'Oracle > admin' 카테고리의 다른 글

ADMIN 점검 사항  (0) 2016.06.13
성능 분석 View  (0) 2016.06.13
11gR2 RAC 권장 파라미터 정리  (0) 2016.06.03
Redo log file의 장애 처리 유형  (0) 2016.06.03
Oracle User Password Policy Configuration  (0) 2016.05.26