– 10g의 statistics_level=typical이상이면 gathering됨.(but this view does not include undo block or undo header transfers)
|
1 2 3 4 5 6 7 8 |
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하고 있는 정보 보기
|
1 2 3 4 5 6 7 8 9 |
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별로 볼수 있음.
|
1 2 3 4 5 |
SELECT * FROM v$instance_cache_transfer WHERE cr_busy > 0 ORDER BY INSTANCE, cr_busy DESC; |
– 10g, cluster wait 보기(현재 수행중인 long running sql에대한 정보는 들어가지 않는다. 끝난 sql의 정보만 들어간다.)
|
1 2 3 4 5 |
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 찾기
|
1 2 3 4 5 6 7 |
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이 된다.)
|
1 2 3 4 |
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별 사용량 보기
|
1 |
select * from dba_tablespace_usage_metrics; |
— 10g, advisor 수행 task보기
|
1 |
select * from DBA_ADVISOR_TASKS order by execution_end desc; |
— DB cache advisor 보기
|
1 2 3 4 5 6 7 8 9 10 |
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이 지정되어 있어야 한다.)
|
1 2 3 4 5 |
SELECT target_mttr, estimated_mttr, writes_mttr, optimal_logfile_size FROM v$instance_recovery; |
– segment advisor 수행 흔적 보기
|
1 2 3 4 |
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로 확인 하기)
|
1 2 3 4 5 6 |
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별)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 변동 결과 보기.
|
1 |
select * from V$SGA_RESIZE_OPS; |
참고 : http://www.dbguide.net