数据库运行监视
--查看SGA统计信息: select * from v$sgastat order by nvl(pool, 1), bytes desc; --查看排序情况: select a.value memory, b.value disk, trunc(1 - b.value / a.value) * 100 ratio from v$sysstat a, v$sysstat b where a.name = 'sorts (memory)' and b.name = 'sorts (disk)'; --查看日志切换情况: select * from (select * from v$log_history order by first_time desc) where rownum < 100; --查看锁资源: select b.session_id, b.oracle_username, b.os_user_name, b.process, b.locked_mode, a.owner, a.object_name, a.object_id, a.object_type, b.xidusn, b.xidslot, b.xidsqn from all_objects a, v$locked_object b where a.object_id = b.object_id; --查看库缓存命中率: select namespace, gets, gethits, trunc(gethitratio * 100, 2) gethitratio, pins, pinhits, trunc(pinhitratio * 100, 2) pinhitratio, reloads, invalidations, dlm_lock_requests, dlm_pin_requests, dlm_pin_releases, dlm_invalidation_requests, dlm_invalidations from v$librarycache; --查看数据缓存命中率: select a.*, trunc((1 - phys / (gets + con_gets)) * 100, 2) "HIT RATIO" from (select sum(decode(name, 'physical reads', value, 0)) phys, sum(decode(name, 'db block gets', value, 0)) gets, sum(decode(name, 'consistent gets', value, 0)) con_gets from v$sysstat ) a ; --查看WorkArea情况: select name, value from v$sysstat where name like '%workarea%'; --查看当前会话等待事件: select * from v$session_wait; --查看数据库大小: select '合计' OWNER, trunc(sum(bytes) / 1024 / 1024) "Size (M)" from dba_segments union all select owner, trunc(sum(bytes) / 1024 / 1024) "Size (M)" from dba_segments group by owner; --查看等待事件的统计信息: Select event, total_waits, time_waited, average_wait from v$system_event order by total_waits desc;