Oracle监控相关面试题笔试题

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 /*+ rule*/ 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_segments
union all
select owner, trunc(sum(bytes) / 1024 / 1024) "Size (M)"
  from dba_segments
 group by owner