ORACLE 系统命令

论坛中有位老兄贴的，忘了是谁了~   
    
  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   count(name)   num_instances   ,type   ,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   2;     
    
  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,'free   space'   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;