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 |