oracle监控数据库性能和用户的语句

监控数据库性能的SQL语句
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 owner='&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;