Oracle Advisor 관련 오라클 메모리 조회 SQL

Oracle Advisor 관련 오라클 메모리 조회 SQL

—  PGA target Advsior 보기

— estd_overalloc_count 가 0인 가장 작은 target_MB 가 적절한 PGA_target 이 된다.

select ROUND(pga_target_for_estimate/1024/1024) TARGET_MB, estd_pga_cache_hit_percentage CACHE_HIT_PERC,estd_overalloc_count
from V$PGA_TARGET_ADVICE

— Tablespace별 사용량 보기

select * from dba_tablespace_usage_metrics ;

— Advisor 수행 task보기

select * from DBA_ADVISOR_TASKS order by execution_end desc ;

— DB cache advisor 보기

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads

FROM V$DB_CACHE_ADVICE

WHERE name = ‘DEFAULT’

AND block_size = (SELECT value FROM V$PARAMETER WHERE name = ‘db_block_size’)

AND advice_status = ‘ON’ ;

— Redo advisor 보기

— Redo log advisor

— init.ora에 Fast_start_mttr_target이 지정되어 있어야 한다.

select target_mttr, estimated_mttr, writes_mttr, optimal_logfile_size from v$instance_recovery;

— Segment advisor 수행 흔적 보기

— Segment advisor 수행 권고 보기

— reclaimable_space (tablespace level로 확인 하기)

— Segment advisor 수행권고 보기 ( Table 별 )

select trunc(reclaimable_space/allocated_space,2) reclaimable_pct,

trunc(reclaimable_space/allocated_space,2) reclaim_raito,

Segment_owner,segment_name,Segment_type,Partition_name, Allocated_space,

Used_space,Reclaimable_space,Chain_rowexcess chian_ratio,substr(Recommendations,1,40) recommendations

from table (dbms_space.asa_recommendations()) a

order by trunc(reclaimable_space/allocated_space,2) desc,to_number(reclaimable_space) desc ;

— Auto sga일경우, memory size 변동 결과 보기.

select * from V$SGA_RESIZE_OPS;