获取当前session占用的资源。
select
to_char(m.BEGIN_TIME,'yyyy-mm-dd HH24:MI:SS') "结束时间的间隔",
to_char(m.END_TIME,'yyyy-mm-dd HH24:MI:SS') "结束时间的间隔",
m.INTSIZE_CSEC/100 "间隔时间/秒",
s.USERNAME usr,
m.SESSION_ID "sessionID",
m.SESSION_SERIAL_NUM "SESSION序列号",
m.CPU "cpu使用率",
m.PHYSICAL_READS "物理读",
m.LOGICAL_READS "逻辑读",
m.PGA_MEMORY "end后PGA大小",
m.HARD_PARSES "硬解析",
m.SOFT_PARSES "软解析",
m.PHYSICAL_READ_PCT "物理读比率",
m.LOGICAL_READ_PCT "逻辑读比率",
s.SQL_ID
from
v$sessmetric m,v$session s
where
(m.PHYSICAL_READS>100
or m.CPU>100
or m.LOGICAL_READS>100)
and m.session_id=s.SID
and m.SESSION_SERIAL_NUM=s.SERIAL#
order by m.PHYSICAL_READS DESC,m.CPU desc,m.LOGICAL_READS desc;
按照读的次数找
select B.USERNAME ,
A.SQL_ID,
A.ADDRESS "语句在SGA的偏移地址",
a.VERSION_COUNT "语句cursor数量",
A.EXECUTIONS "执行次数",
a.ELAPSED_TIME "解析和执行所用时间",
A.BUFFER_GETS,
A.DISK_READS ,
A.PARSE_CALLS "解析次数",
A.SQL_TEXT
from V$SQLAREA A, DBA_USERS B
where A.PARSING_USER_ID = B.USER_ID
and (A.BUFFER_GETS > 100000 or A.DISK_READS > 100000)
order by A.BUFFER_GETS + 100 * A.DISK_READS desc;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23754390/viewspace-700373/ ，如需转载，请注明出处，否则将追究法律责任。
