关于压力测试，监控数据表的问题！

http://topic.csdn.net/u/20090401/10/052E6012-A579-4B5B-B687-761B96C08324.html

 

 

SQL code

  
  

   
   
   
   监控数据库性能的SQL 
查看tablespace的空間使用情況

   
   select
   
    tablespace_name,
   
   sum
   
   (bytes)
   
   /
   
   1024
   
   /
   
   1024
   
    
   
   from
   
    dba_free_space 
   
   group
   
    
   
   by
   
    tablespace_name

   
   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;
  
  

 
 
  
  SQL code 
 
 
 
 

  
  
   
   

    
    
    
    
操作系统监控cpu
    
    /
    
    io.

数据库主要监控下等待事件event,和对应的sql,根据情况再做分析:

    
    SELECT
    
     S.SID, S.USERNAME, SW.EVENT, Q.SQL_TEXT
  
    
    FROM
    
     V$SESSION S, V$SESSION_WAIT SW, V$SQL Q
 
    
    WHERE
    
     S.USERNAME 
    
    <>
    
     
    
    '
    
    SYS
    
    '
    
    
   
    
    AND
    
     Q.HASH_VALUE 
    
    =
    
     S.SQL_HASH_VALUE
   
    
    AND
    
     SW.SID 
    
    =
    
     S.SID
   
    
    AND
    
     S.STATUS 
    
    =
    
     
    
    '
    
    ACTIVE
    
    '
    
    
   
    
    AND
    
     SW.EVENT 
    
    NOT
    
     
    
    LIKE
    
     
    
    '
    
    %SQL*Net%
    
    '
    
    
 
    
    ORDER
    
     
    
    BY
    
     LAST_CALL_ET 
    
    DESC
    
    ;

同时监控日志切换情况:

    
    SELECT
    
     
    
    *
    
     
    
    FROM
    
     V$
    
    LOG
    
    ;



   
   


 
 


 
 

  
  
引用楼主 ocp_toad 的帖子: