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

lizhuquan0769 2013-01-25 04:29:11
由于需要在Java程序上获取到这些数据,求大神们告诉我应该通过哪些SQL语句查询出来
...全文
3619 2 点赞 打赏 收藏 举报
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yiyongle 2014-09-01
]通过cmd进入sql
sqlplus / as sysdba
查询内存语句SQL> SELECT * FROM v$osstat;

  • 打赏
  • 举报
回复
ksdy138141314 2013-01-25
查看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;
  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle 高级技术
加入

3439

社区成员

Oracle 高级技术相关讨论专区
申请成为版主
帖子事件
创建了帖子
2013-01-25 04:29
社区公告
暂无公告