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

[lizhuquan0769]

由于需要在Java程序上获取到这些数据，求大神们告诉我应该通过哪些SQL语句查询出来

[ksdy138141314]

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

[yiyongle]

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