如何查看 Oracle 的 CPU，内存的使用情况 ，以及 资源使用率高的语句

Question

本帖最后由 lizhuquan0769 于 2013-01-25 16:30:29 编辑

Answer 1

查看session io：
  select b.username, a.*
  from v$sess_io a, v$session b
 where a.sid in
       (select x.sid
          from v$session x
         where x.status = 'ACTIVE'
           and x.PADDR not in (select paddr from v$bgprocess))
   and a.sid = b.sid
   and username is not null
   and username <> 'SYS';查询高水位：
select TABLE_NAME,HWM,AVG_USED_BLOCKS,                                                                           
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt 
from                                                                                                             
     (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,                                                       
       DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),                                 
                                0, 1,                                                                            
                                ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)                 
                           ) + 2 AVG_USED_BLOCKS                                                                 
        FROM USER_SEGMENTS A,                                                                                    
             USER_TABLES B                                                                                       
        WHERE SEGMENT_NAME = TABLE_NAME                                                                          
         and TABLE_NAME in ('XXX')                                                                               
         and SEGMENT_TYPE = 'TABLE'                                                                              
      ); 
查询undo的占用：
select s.username,s.SID, u.name, r.RSSIZE, r.WRITES, r.HWMSIZE
  from v$transaction t, v$rollstat r, v$rollname u, v$session s
 where s.taddr = t.addr
   and t.xidusn = r.usn
   and r.usn = u.usn
 order by r.rssize desc;                            session status：select /*+rule*/      a.sid,
                      a.username as "用户名",
                      j.EVENT as "等待事件",
                      /*j.P1TEXT,
                      j.P1,
                      j.P2TEXT,
                      j.p2,
                      j.p3text,
                      j.p3,
                      j.STATE,*/              
                      a.osuser as "OS用户名",
                      a.machine "OS机器名",
                      a.program "OS程序名",
                      a.module "模块名",
                      a.action "动作名",
                      i.name as "操作命令",
                      a.type "用户类型",
                      a.logon_time "登陆时间",
                      g.ospid "OS进程号",
                      c.logical_reads "逻辑读",
                      c.physical_reads "物理读",
                      c.io_write "写IO",
                      d.cputimes "CPU执行时间",
                      e.memsize "共享内存占用",
                      f.redosize "REDO大小",
                      'select sql_text from v$sqltext where address=''' || b.address ||
                      ''' and hash_value=''' || b.hash_value || ''' order by piece' sql_text,
                      'select sql_text from v$sqltext where address=''' || k.address ||
                      ''' and hash_value=''' || k.hash_value || ''' order by piece' prev_sql_text
                 from (select sid,
                              username,
                              osuser,
                              machine,
                              program,
                              module,
                              action,
                              command,
                              type,
                              logon_time,
                              paddr,
                              taddr,
                              lockwait,
                              sql_address,
                              sql_hash_value,
                              prev_sql_addr,
                              prev_hash_value
                         from v$session
                        where status = 'ACTIVE'
                          and username is not null
                          and username <> 'SYS'
                          and type <> 'BACKGROUND') a,
                      AUDIT_ACTIONS i,
                      v$session_wait j,
                      (select address, hash_value, sql_text from v$sqltext where piece = 0) b,
                      (select address, hash_value, sql_text from v$sqltext where piece = 0) k,
                      (select sid,
                              round((block_gets + consistent_gets) * 8 / 1024) logical_reads,
                              round(physical_reads * 8 / 1024) physical_reads,
                              round((block_changes + consistent_changes) * 8 / 1024) io_write
                         from v$sess_io) c,
                      (select sid, value / 100 cputimes
                         from v$sesstat
                        where statistic# = 11) d,
                      (select sid, sum(value) memsize
                         from v$sesstat
                        where statistic# in (15, 20)
                        group by sid) e,
                      (select sid,
                              sum((decode(statistic#, 115, value, 0) +
                                  decode(statistic#, 117, value, 0)) /
                                  (decode(statistic#, 120, value, 0) + 16)) redosize
                         from v$sesstat
                        group by sid) f,
                      (select addr, spid ospid from v$process) g
                where (a.sql_address = b.address(+) and a.sql_hash_value = b.hash_value(+))
                  and (a.prev_sql_addr = k.address(+) and a.prev_hash_value = k.hash_value(+))
                  and (a.sid = c.sid(+))
                  and (a.sid = d.sid(+))
                  and (a.sid = e.sid(+))
                  and (a.sid = f.sid(+))
                  and (a.paddr = g.addr(+))
                  and a.command = i.action
                  and (a.sid = j.sid(+))
                  order by c.logical_reads desc nulls last;

Answer 2

]通过cmd进入sql  
sqlplus / as sysdba
查询内存语句SQL> SELECT * FROM v$osstat;