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

— 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



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;

By haisins

오라클 DBA 박용석 입니다. 으로 문의 주세요.

7 thoughts on “Oracle Advisor 관련 오라클 메모리 조회 SQL”
  1. Your style is so unique compared to other folks I’ve read stuff from.
    Many thanks for posting when you’ve got the opportunity, Guess
    I will just bookmark this site.

  2. Hello every one, here every one is sharing these experience,
    so it’s fastidious to read this website, and I used to go to see this blog everyday.

  3. Heya! I’m at work surfing around your blog from my new iphone 3gs!
    Just wanted to say I love reading your blog and look forward to all your posts!
    Keep up the superb work!

  4. I all the time used to study article in news papers but now as I am a user of internet so from now I am using net
    for articles or reviews, thanks to web.

  5. I simply want to say I am just newbie to blogging and absolutely savored you’re page. Probably I’m likely to bookmark your site . You surely have wonderful posts. Kudos for sharing with us your blog site.

a knockout post에 답글 남기기 답글 취소하기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다