Oracle数据库监控

今天发现了一些不错的oracle数据库监控语句，整理出来用于使用。

sql语句执行情况

select round(100 * a.pct, 2) pct, 
       round(a.elapsed_time/1000000, 2) elapsed_time, 
       round(a.elapsed_time/a.executions/1000) ms_by_exec, 
       round(a.cpu_time/1000000, 2) cpu_time, 
       a.buffer_gets total_cost, 
       round(a.buffer_gets/a.executions) elem_cost, 
       a.executions exec, 
       a.rows_processed nb_rows, 
       s.sql_text 
from (select * 
      from (select elapsed_time, 
                   ratio_to_report(elapsed_time) over () pct, 
                   cpu_time, 
                   buffer_gets, 
                   executions, 
                   rows_processed, 
                   address, 
                   hash_value 
            from  v$sql 
            order by elapsed_time desc) 
      where rownum < 26) a, 
     v$sqlarea s 
where a.address = s.address 
  and a.hash_value = s.hash_value 
  and a.executions <> 0 
order by pct desc, cpu_time desc

session监控

  select sesion.sid, 
    username, 
    osuser, 
    machine, 
    sesion.module, 
    status, 
    optimizer_mode, 
    sql_text
  from v$sqlarea sqlarea, v$session sesion 
 where sesion.sql_hash_value = sqlarea.hash_value(+) 
   and sesion.sql_address    = sqlarea.address(+) 
   and sesion.username is not null 
order by username, sql_text

tablespace

select   TABLESPACE_NAME, 
         INITIAL_EXTENT, 
         NEXT_EXTENT, 
         MIN_EXTENTS, 
         MAX_EXTENTS, 
         PCT_INCREASE, 
         MIN_EXTLEN, 
         STATUS, 
         CONTENTS, 
         LOGGING, 
         EXTENT_MANAGEMENT, 
         ALLOCATION_TYPE 
from     user_tablespaces 
order by TABLESPACE_NAME

tablespace freespace

select   ddf.TABLESPACE_NAME, 
         ddf.BYTES tablespace_size, 
         ddf.BYTES-nvl(DFS.BYTES,0) used, 
         round(((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES)*100,2) pct_used, 
         nvl(dfs.BYTES,0) free, 
         round((1-((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES))*100,2) pct_free 
from    (select t.name TABLESPACE_NAME, 
                sum(BYTES) bytes 
         from   v$datafile d, v$tablespace t 
         where t.ts# = d.ts# 
         group  by t.name) ddf, 
        (select TABLESPACE_NAME, 
                sum(BYTES) bytes 
         from   user_free_space 
         group  by TABLESPACE_NAME) dfs 
where    ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME(+) 
order by ((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES) desc

datafile IO

select   df.NAME filename, 
         ts.name tablespace_name, 
         PHYRDS physical_reads, 
         round((PHYRDS / pd.PHYS_READS)*100,2) pct_reads, 
         PHYWRTS physical_writes, 
         round(PHYWRTS * 100 / pd.PHYS_WRTS,2) pct_writes, 
         fs.PHYBLKRD + fs.PHYBLKWRT total_block_io 
from    (select sum(PHYRDS) phys_reads, 
                sum(PHYWRTS) phys_wrts 
         from v$filestat) pd, 
         v$datafile df, 
         v$filestat fs, 
         v$tablespace ts 
where    df.FILE# = fs.FILE# and df.ts# = ts.ts# 
order by fs.PHYBLKRD + fs.PHYBLKWRT desc

event

select event, 
          time_waited system, 
          (select sum(time_waited) from v$session_event ses where ses.event = sys.event) sessions 
from v$system_event sys 
where event != 'Null event' 
          and event != 'rdbms ipc message' 
          and event != 'pipe get' 
          and event != 'virtual circuit status' 
          and event != 'lock manager wait for remote message' 
          and event not like '% timer' 
          and event not like 'SQL*Net message from %'