– 10g의 statistics_level=typical이상이면 gathering됨.(but this view does not include undo block or undo header transfers)

SELECT object_name, 
       statistic_name, 
       SUM(value) waits 
FROM   v$segment_statistics 
WHERE  statistic_name = 'buffer busy waits' 
GROUP  BY object_name, 
          statistic_name 
ORDER  BY waits DESC; 

– 10g, transaction에 대한 rollback하고 있는 정보 보기

SELECT * FROM   v$fast_start_servers; 

SELECT message FROM   v$session_longops; 

SELECT state, 
       undoblocksdone, 
       undoblockstotal, 
       cputime 
FROM   v$fast_start_transactions; 

– 10g, global cache activity를 class별로 볼수 있음.

SELECT * 
FROM   v$instance_cache_transfer 
WHERE  cr_busy > 0 
ORDER  BY INSTANCE, 
          cr_busy DESC; 

– 10g, cluster wait 보기(현재 수행중인 long running sql에대한 정보는 들어가지 않는다. 끝난 sql의 정보만 들어간다.)

SELECT Substr(sql_text, 1, 100), 
       cluster_wait_time wait_time_microsecond 
FROM   v$sql 
WHERE  cluster_wait_time > 0 
ORDER  BY 2 DESC; 

– 10g, blocked session 과 blocking session 찾기

SELECT * FROM   v$session; 

SELECT sid, 
       blocking_session, 
       event 
FROM   v$session 
WHERE  blocking_session_status = 'VALID'; 

– 9i, 10g, 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; 

— 10g, tablespace별 사용량 보기

select * from dba_tablespace_usage_metrics;

— 10g, 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'; 

— 10g, Redo advisor 보기(init.ora에 Fast_start_mttr_target이 지정되어 있어야 한다.)

SELECT target_mttr, 
       estimated_mttr, 
       writes_mttr, 
       optimal_logfile_size 
FROM   v$instance_recovery; 

– segment advisor 수행 흔적 보기

select * from DBA_AUTO_SEGADV_SUMMARY order by start_time desc;
AUTO_TASKID SNAPID      SEGMENTS_SE SEGMENTS_PR TABLESPACE_ TABLESPACE_ RECOMMENDAT STAR  END_
------------- ----------- ----------- ----------- ----------- ----------- ----------- ---- ----
          1        2231           0           0           0           0           0 2006/03/01 15:02:25.773803 

– reclaimable_space (tablespace level로 확인 하기)

SELECT tablespace_name, 
       allocated_space, 
       reclaimable_space, 
       Trunc(reclaimable_space / allocated_space, 2) * 100 reclaimable_pct 
FROM   TABLE(dbms_space.Asa_recommendations('TRUE', 'TRUE', 'ALL')) 
ORDER  BY reclaimable_pct DESC; 

– 10g, 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; 

– 10g, auto sga일경우, memory size 변동 결과 보기.

select * from V$SGA_RESIZE_OPS;

참고 : http://www.dbguide.net