– 10g\uc758 statistics_level=typical\uc774\uc0c1\uc774\uba74 gathering\ub428.(but this view does not include undo block or undo header transfers)<\/p>\n
SELECT object_name, \r\n statistic_name, \r\n SUM(value) waits \r\nFROM v$segment_statistics \r\nWHERE statistic_name = 'buffer busy waits' \r\nGROUP BY object_name, \r\n statistic_name \r\nORDER BY waits DESC; <\/pre>\n– 10g, transaction\uc5d0 \ub300\ud55c rollback\ud558\uace0 \uc788\ub294 \uc815\ubcf4 \ubcf4\uae30<\/p>\n
SELECT * FROM v$fast_start_servers; \r\n\r\nSELECT message FROM v$session_longops; \r\n\r\nSELECT state, \r\n undoblocksdone, \r\n undoblockstotal, \r\n cputime \r\nFROM v$fast_start_transactions; <\/pre>\n– 10g, global cache activity\ub97c class\ubcc4\ub85c \ubcfc\uc218 \uc788\uc74c.<\/p>\n
SELECT * \r\nFROM v$instance_cache_transfer \r\nWHERE cr_busy > 0 \r\nORDER BY INSTANCE, \r\n cr_busy DESC; <\/pre>\n– 10g, cluster wait \ubcf4\uae30(\ud604\uc7ac \uc218\ud589\uc911\uc778 long running sql\uc5d0\ub300\ud55c \uc815\ubcf4\ub294 \ub4e4\uc5b4\uac00\uc9c0 \uc54a\ub294\ub2e4. \ub05d\ub09c sql\uc758 \uc815\ubcf4\ub9cc \ub4e4\uc5b4\uac04\ub2e4.)<\/p>\n
SELECT Substr(sql_text, 1, 100), \r\n cluster_wait_time wait_time_microsecond \r\nFROM v$sql \r\nWHERE cluster_wait_time > 0 \r\nORDER BY 2 DESC; <\/pre>\n– 10g, blocked session \uacfc blocking session \ucc3e\uae30<\/p>\n
SELECT * FROM v$session; \r\n\r\nSELECT sid, \r\n blocking_session, \r\n event \r\nFROM v$session \r\nWHERE blocking_session_status = 'VALID'; <\/pre>\n– 9i, 10g, PGA target Advsior \ubcf4\uae30(estd_overalloc_count\uac00 0\uc778 \uac00\uc7a5 \uc791\uc740 target_MB\uac00 \uc801\uc808\ud55c PGA_target\uc774 \ub41c\ub2e4.)<\/p>\n
SELECT Round(pga_target_for_estimate \/ 1024 \/ 1024) target_mb, \r\n estd_pga_cache_hit_percentage cache_hit_perc, \r\n estd_overalloc_count \r\nFROM v$pga_target_advice; <\/pre>\n— 10g, tablespace\ubcc4 \uc0ac\uc6a9\ub7c9 \ubcf4\uae30<\/p>\n
select * from dba_tablespace_usage_metrics;<\/pre>\n— 10g, advisor \uc218\ud589 task\ubcf4\uae30<\/p>\n
select * from DBA_ADVISOR_TASKS order by execution_end desc;<\/pre>\n— DB cache advisor \ubcf4\uae30<\/p>\n
SELECT size_for_estimate, \r\n buffers_for_estimate, \r\n estd_physical_read_factor, \r\n estd_physical_reads \r\nFROM v$db_cache_advice \r\nWHERE name = 'DEFAULT' \r\n AND block_size = (SELECT value \r\n FROM v$parameter \r\n WHERE name = 'db_block_size') \r\n AND advice_status = 'ON'; <\/pre>\n— 10g, Redo advisor \ubcf4\uae30(init.ora\uc5d0 Fast_start_mttr_target\uc774 \uc9c0\uc815\ub418\uc5b4 \uc788\uc5b4\uc57c \ud55c\ub2e4.)<\/p>\n
SELECT target_mttr, \r\n estimated_mttr, \r\n writes_mttr, \r\n optimal_logfile_size \r\nFROM v$instance_recovery; \r\n<\/pre>\n– segment advisor \uc218\ud589 \ud754\uc801 \ubcf4\uae30<\/p>\n
select * from DBA_AUTO_SEGADV_SUMMARY order by start_time desc;\r\nAUTO_TASKID SNAPID SEGMENTS_SE SEGMENTS_PR TABLESPACE_ TABLESPACE_ RECOMMENDAT STAR END_\r\n------------- ----------- ----------- ----------- ----------- ----------- ----------- ---- ----\r\n 1 2231 0 0 0 0 0 2006\/03\/01 15:02:25.773803 <\/pre>\n– reclaimable_space (tablespace level\ub85c \ud655\uc778 \ud558\uae30)<\/p>\n
SELECT tablespace_name, \r\n allocated_space, \r\n reclaimable_space, \r\n Trunc(reclaimable_space \/ allocated_space, 2) * 100 reclaimable_pct \r\nFROM TABLE(dbms_space.Asa_recommendations('TRUE', 'TRUE', 'ALL')) \r\nORDER BY reclaimable_pct DESC; <\/pre>\n– 10g, segment advisor \uc218\ud589\uad8c\uace0 \ubcf4\uae30 ( table\ubcc4)<\/p>\n
SELECT Trunc(reclaimable_space \/ allocated_space, 2) reclaimable_pct, \r\n Trunc(reclaimable_space \/ allocated_space, 2) reclaim_raito, \r\n segment_owner, \r\n segment_name, \r\n segment_type, \r\n partition_name, \r\n allocated_space, \r\n used_space, \r\n reclaimable_space, \r\n chain_rowexcess chian_ratio, \r\n Substr(recommendations, 1, 40) recommendations \r\nFROM TABLE (dbms_space.Asa_recommendations()) a \r\nORDER BY Trunc(reclaimable_space \/ allocated_space, 2) DESC, \r\n To_number(reclaimable_space) DESC; <\/pre>\n– 10g, auto sga\uc77c\uacbd\uc6b0, memory size \ubcc0\ub3d9 \uacb0\uacfc \ubcf4\uae30.<\/p>\n
select * from V$SGA_RESIZE_OPS;<\/pre>\n\ucc38\uace0 : http:\/\/www.dbguide.net<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
– 10g\uc758 statistics_level=typical\uc774\uc0c1\uc774\uba74 gathering\ub428.(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\uc5d0 \ub300\ud55c … Continue reading