来源:相老师:

1. 监控事例的等待
  select   event,
    sum(decode(wait_Time,0,0,1)) "Prev",
    sum(decode(wait_Time,0,1,0)) "Curr",
    count(*) "Tot"
       from  v$session_Wait
       group by event
       order by 4;
  
2. 回滚段的争用情况
      select   name, waits, gets, waits/gets "Ratio"
       from  v$rollstat a, v$rollname b
       where  a.usn = b.usn;
  
3. 监控表空间的 I/O 比例
      select   df.tablespace_name name,df.file_name "file",f.phyrds pyr,
        f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
       from  v$filestat f, dba_data_files df
       where  f.file# = df.file_id
       order by df.tablespace_name;
  
4. 监控文件系统的 I/O 比例
      select   substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
        a.status, a.bytes, b.phyrds, b.phywrts
       from  v$datafile a, v$filestat b
       where  a.file# = b.file#;
  
5.在某个用户下找所有的索引
      select   user_indexes.table_name,
        user_indexes.index_name,
        uniqueness,
        column_name
       from  user_ind_columns, user_indexes
       where  user_ind_columns.index_name = user_indexes.index_name and
        user_ind_columns.table_name = user_indexes.table_name
       order by user_indexes.table_type, user_indexes.table_name,
        user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
      select   a.value + b.value "logical_reads",
        c.value "phys_reads",
        round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
       from  v$sysstat a, v$sysstat b, v$sysstat c
       where  a.statistic# = 38 and b.statistic# = 39 and
        c.statistic# = 40;
  
7. 监控 SGA 中字典缓冲区的命中率
      select   parameter, gets,Getmisses ,
        getmisses/(gets+getmisses)*100 "miss ratio",
        (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
       from  v$rowcache
       where  gets+getmisses <>0
       group by parameter, gets, getmisses;
  
8. 监控 SGA 中共享缓存区的命中率，应该小于1%
      select   sum(pins) "Total Pins", sum(reloads) "Total Reloads",
        sum(reloads)/sum(pins) *100 libcache
       from  v$librarycache;
      select   sum(pinhits-reloads)/sum(pins) "hit radio",
        sum(reloads)/sum(pins) "reload percent"
       from  v$librarycache;
  
9. 显示所有数据库对象的类别和大小
      select   type,
        count(name) num_instances,
        sum(source_size) source_size,
        sum(parsed_size) parsed_size,
        sum(code_size) code_size,
        sum(error_size) error_size,
        sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
       from  dba_object_size
       group by type
       order by 1;
  
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
      SELECT   name, gets, misses, immediate_gets, immediate_misses,
        Decode(gets,0,0,misses/gets*100) ratio1,
        Decode(immediate_gets+immediate_misses,0,0,
         immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
       FROM  v$latch
       WHERE  name IN ('redo allocation', 'redo copy');
  
11.  监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
      SELECT   name, value
       FROM  v$sysstat
       WHERE  name IN ('sorts (memory)', 'sorts (disk)');
  
12. 监控当前数据库谁在运行什么SQL语句
      SELECT   osuser, username, sql_text
       from  v$session a, v$sqltext b
       where  a.sql_address =b.address order by address, piece;
  
13. 监控字典缓冲区
      SELECT   (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
       FROM  V$LIBRARYCACHE;
      SELECT   (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
       FROM  V$ROWCACHE;
      SELECT   SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
       FROM  V$LIBRARYCACHE;
     后者除以前者,此比率小于1%,接近0%为好。
      SELECT   SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
       FROM  V$ROWCACHE;
  
14. 找ORACLE字符集
      select * from sys.props$ where name='NLS_CHARACTERSET';
  
15. 监控 MTS
  select busy/(busy+idle) "shared servers busy" from v$dispatcher;
  此值大于0.5时，参数需加大
  select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
  select count(*) from v$dispatcher;
  select servers_highwater from v$mts;
  servers_highwater接近mts_max_servers时，参数需加大
  
16. 碎片程度
      select   tablespace_name,count(tablespace_name)
       from  dba_free_space
       group by tablespace_name
       having count(tablespace_name)>10;
  alter tablespace name coalesce;
  alter table name deallocate unused;
      create or replace view ts_blocks_v as
       select   tablespace_name,block_id,bytes,blocks,
         segment_name
        from  dba_free_space
       union all
       select   tablespace_name,block_id,bytes,blocks,
         segment_name
        from  dba_extents;
      select * from ts_blocks_v;
      select   tablespace_name,sum(bytes),max(bytes),count(block_id)
       from  dba_free_space
       group by tablespace_name;
     查看碎片程度高的表
      SELECT   segment_name table_name , COUNT(*) extents
       FROM  dba_segments
       WHERE  owner NOT IN ('SYS', 'SYSTEM')
       GROUP BY segment_name
       HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
  
17. 表、索引的存储情况检查
      select   segment_name,sum(bytes),count(*) ext_quan
       from  dba_extents
       where   tablespace_name='&tablespace_name' and
        segment_type='TABLE'
       group by tablespace_name,segment_name;
      select   segment_name,count(*)
       from  dba_extents
       where  segment_type='INDEX' and wner='&owner'
       group by segment_name;
  
18、找使用CPU多的用户session
     12是cpu used by this session
      select   a.sid,spid,status,substr(a.program,1,40) prog,
        a.terminal,osuser,value/60/100 value
       from  v$session a,v$process b,v$sesstat c
       where  c.statistic#=12 and
        c.sid=a.sid and
        a.paddr=b.addr
       order by value desc;
  
19.对可疑/性能不好的Server Process来进行Trace.,可以用tkprof来分析Trace的结果.比较方便.使用Unix的KSH.
   (1) start_trc:
  #!/bin/ksh
  if (( $# != 1 ))
  then
  echo Usuage: start_trc pid
  fi
  sid_serial=$(print "
  connect / as sysdba;
  set heading off;
  set feedback off;
  select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
  exit;
  " | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
  if [[ -z $sid_serial ]]
  then
  print "Seems that this process $1 is not an Oracle process!"
  exit 1
  fi
  print "
  connect / as sysdba;
  execute dbms_system.set_sql_trace_in_session($sid_serial,true);
  exit;
  " | sqlplus -s /nolog
  
  (2) stop_trc:
  #!/bin/ksh
  if (( $# != 1 ))
  then
  echo Usuage: stop_trc pid
  fi
  sid_serial=$(print "
  connect / as sysdba;
  set heading off;
  set feedback off;
  select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
  exit;
  " | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
  if [[ -z $sid_serial ]]
  then
  print "Seems that this process $1 is not an Oracle process!"
  exit 1
  fi
  print "
  connect / as sysdba;
  execute dbms_system.set_sql_trace_in_session($sid_serial,false);
  exit;
  " | sqlplus -s /nolog
  
20.查看Lock
  SELECT   sn.username, m.sid, m.type,
    DECODE(m.lmode, 0, 'None',
     1, 'Null',
     2, 'Row Share',
     3, 'Row Excl.',
     4, 'Share',
     5, 'S/Row Excl.',
     6, 'Exclusive',
     lmode, ltrim(to_char(lmode,'990'))) lmode,
    DECODE(m.request,0, 'None',
     1, 'Null',
     2, 'Row Share',
     3, 'Row Excl.',
     4, 'Share',
     5, 'S/Row Excl.',
     6, 'Exclusive',
     request, ltrim(to_char(m.request,'990'))) request,
    m.id1, m.id2
   FROM  v$session sn, v$lock m
   WHERE  (sn.sid = m.sid AND m.request != 0) OR
    ( sn.sid = m.sid AND
     m.request = 0 AND
     lmode != 4 AND
     (id1, id2) IN (
       SELECT   s.id1, s.id2
        FROM  v$lock s
        WHERE  request != 0 AND
         s.id1 = m.id1 AND
         s.id2 = m.id2
       )
    )
   ORDER BY id1, id2, m.request;
  
   select   l.sid,s.serial#,s.username,s.terminal,
     decode(l.type,'RW','RW - Row Wait Enqueue',
      'TM','TM - DML Enqueue',
      'TX','TX - Trans Enqueue',
      'UL','UL - User',l.type||'System') res,
     substr(t.name,1,10) tab,u.name owner,
     l.id1,l.id2,
     decode(l.lmode,1,'No Lock',
      2,'Row Share',
      3,'Row Exclusive',
      4,'Share',
      5,'Shr Row Excl',
      6,'Exclusive',null) lmode,
     decode(l.request,1,'No Lock',
      2,'Row Share',
      3,'Row Excl',
      4,'Share',
      5,'Shr Row Excl',
      6,'Exclusive',null) request
    from  v$lock l, v$session s,
     sys.user$ u,sys.obj$ t
    where  l.sid = s.sid and
     s.type != 'BACKGROUND' and
     t.obj# = l.id1 and
     u.user# = t.owner#;
監控登入登出的用戶:
創建如下的兩張表:
create table login_log                        --        登入登出信息表
(
    session_id int not null,        --        sessionid
    login_on_time  date,                --        登入時間      
    login_off_time  date,                --        登出時間      
    user_in_db        varchar2(30),        --        登入的db user
    machine    varchar2(20),           --        機器名
    ip_address varchar2(20),        --        ip地址
    run_program varchar2(20)    --        以何程序登入
);
create table allow_user                        --        網域用戶表
(
    ip_address varchar2(20),                --        ip地址
    login_user_name nvarchar2(20)   --        操作者姓名
);
創建如下的兩個觸發器:
create or replace trigger login_on_info                --        紀錄登入信息的觸發器
after logon on database
Begin
    insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
    select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
    from v$session where AUDSID = USERENV('SESSIONID');  --當前SESSION
END;
create or replace trigger login_off_info        --        紀錄登出信息的觸發器
before logoff on database
Begin
        update login_log set  login_off_time = sysdate
        where session_id = USERENV('SESSIONID');        --當前SESSION      
exception
    when others then
            null;
END;
方法二:
用如下的方式可以審計執行drop動作的事件:
/**
* drop語句的審計日誌表
*/
create table drop_log
(
    session_id int not null,         --        sessionid
    drop_time  date,                         --        drop的時間
    ip_address varchar2(20),         --        ip地址
    object_owner varchar2(30),         --        對象的擁有者
    object_name varchar2(30),         --        對象名稱
    object_type varchar2(20),         --        對象類型
    drop_by_user        varchar2(30) -- 執行drop語句的用戶
);
create or replace trigger drop_info
after drop on mfg0513user.schema                 --        在mfg0513user用戶上創建審計drop的觸發器
begin
    insert into drop_log
                            (session_id,
                            drop_time,
                            ip_address,
                            object_owner,
                            object_name,
                            object_type,
                            drop_by_user)
            values(USERENV('SESSIONID'),
                            sysdate,
                            SYS_CONTEXT('USERENV','IP_ADDRESS'),
                            sys.dictionary_obj_owner,
                            sys.dictionary_obj_name,
                            sys.dictionary_obj_type,
                            sys.login_user);   
end;