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;

 


Comments

comments

haisins

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

Oracle Advisor 관련 오라클 메모리 조회 SQL”의 6개의 댓글

  • 2019-03-26 6:52 오후
    Permalink

    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.

    댓글달기
  • 2019-03-28 7:15 오후
    Permalink

    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.

    댓글달기
  • 2019-04-25 8:07 오후
    Permalink

    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!

    댓글달기
  • 2019-04-28 6:59 오후
    Permalink

    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.

    댓글달기

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다