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   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;  

19.监控空闲空间情况
SQL>  select a.tablespace_name,100*a.sum_bytes/b.sum_bytes           
  2  from (select distinct tablespace_name,sum(bytes) sum_bytes
  3   from dba_free_space
  4   group by tablespace_name) a,
  5   (select distinct tablespace_name,sum(bytes) sum_bytes
  6   from dba_data_files
  7   group by tablespace_name) b
  8  where a.tablespace_name=b.tablespace_name;
20.物理读和内存读较高的语句
SELECT
t.HASH_VALUE,
t.EXECUTIONS,
t.DISK_READS,
round(t.DISK_READS/t.EXECUTIONS) AS perDiskReads,
t.BUFFER_GETS,
round(t.BUFFER_GETS/t.EXECUTIONS) AS perBufferReads,
t.ELAPSED_TIME,
round(t.ELAPSED_TIME/t.EXECUTIONS) AS perElayTime,
t.CPU_TIME,
round(t.CPU_TIME/t.EXECUTIONS) AS perCpuTime,
t.FIRST_LOAD_TIME,
t.SQL_TEXT
FROM v$sql t
WHERE (t.DISK_READS/t.EXECUTIONS >
500 OR t.BUFFER_GETS/t.EXECUTIONS > 20000)
AND t.EXECUTIONS > 0
ORDER BY 6 DESC;