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

2013-01-25 04:29:11 4.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Oracle 12c系列(四)｜资源隔离之IO、内存、CPU 服务器主机提供IO、内存、CPU、存储空间等资源为数据库使用，Oracle使用Flex Diskgroup为数据库提供存储空间并做了相应的资源隔离。下面我们来看下Oracle是如何为不同的PDB做IO、内存、CPU限制的。一、IO 资源隔离 12cR2中，引入了两个参数MAX_IOPS和MAX_MBPS来限制PDB的物理IO。这两个参数只作用于PDB，不对CDB和非多租户环境生效。另外，... 阅读详情

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

...全文

Oracle 性能优化之高消耗的SQL 高资源消耗的 SQL与用户执行 SQL 有关的动态视图有 v$sql、v$sqlarea、v$sqltext、v$sql_plan、 v$sqlstats 等。v$sql 中包含了所有用户执行的所有 SQL 信息，不同用户、不同会话执行相同 SQL 的语义、执行计划可能会不同，这些 SQL 字面值相同（具有相同的 sql_id），通过不同的 child_number 来区分。v$sqlarea 阅读详情

lizhuquan0769

Oracle 高级技术 3,499 成员 18,710 内容

+ 关注 + 加入社区

切换为时间正序

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 的 CPU，内存的使用情况 ，以及 资源使用率高的语句

相关推荐

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