1. 如何查看SGA统计信息？
select * from v$sgastat order by nvl(pool, 1), bytes desc
2. 如何查看日志切换情况？
select * from (select * from v$log_history order by first_time desc) where rownum < 100
3. 如何查看锁资源？
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
4. 如何查看库缓存命中率？
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
5. 如何查看数据缓存命中率？
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
6. 如何查看当前会话等待事件？
select * from v$session_wait
7. 如何查看数据库大小？
select '合计' OWNER, trunc(sum(bytes) / 1024 / 1024) "Size (M)" from dba_segmentsunion allselect owner, trunc(sum(bytes) / 1024 / 1024) "Size (M)" from dba_segments group by owner
