----------------------------------------------------------------------
-- 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';
출처 : http://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&key=subject&search=10g&pg=2&idx=286 (새 창으로 열기)
-- 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 보기
-- 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 수행 흔적 보기
--
--
--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
select * from DBA_AUTO_SEGADV_SUMMARY order by start_time desc;
-- 10g, segment advisor 수행 권고 보기
-- 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,
--a.*
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
--C1, C2, C3
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/dbqa/dbqa120001.jsp?mode=view&key=subject&search=10g&pg=2&idx=303 (새 창으로 열기)
rss