点击(此处)折叠或打开
-
select
-
TO_CHAR(m.end_time,'DD-MM-YYYY HH24:MI:SS') e_dttm, -- Interval End Time
-
m.intsize_csec/100 ints, -- Interval size in sec
-
s.username usr,
-
m.session_id sid,
-
m.session_serial_num ssn,
-
ROUND(m.cpu) cpu100, -- CPU usage 100th sec
-
m.physical_reads prds, -- Number of physical reads
-
m.logical_reads lrds, -- Number of logical reads
-
m.pga_memory pga, -- PGA size at end of interval
-
m.hard_parses hp,
-
m.soft_parses sp,
-
m.physical_read_pct prp,
-
m.logical_read_pct lrp,
-
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;
| E_DTTM | INTS | USR | SID | SSN | CPU100 | PRDS | LRDS | PGA | HP | SP | PRP | LRP | SQL_ID |
| 26-09-2014 10:59:23 | 15.12 | HK | 159 | 5 | 0 | 846 | 32224 | 1011080 | 0 | 2 | 100 | 99.76162 | 8h1qaqha580hh |
点击(此处)折叠或打开
- SELECT SQL_TEXT FROM V$SQLAREA WHERE SQL_ID='8h1qaqha580hh'
select /*+ FULL(bom) */ id from bom where id=100001000
V$SESSMETRIC
V$SESSMETRIC displays the metric values for all sessions.
| Column | Datatype | Description |
|---|---|---|
| BEGIN_TIME | DATE | Begin time of the interval |
| END_TIME | DATE | End time of the interval |
| INTSIZE_CSEC | NUMBER | Interval size (in hundredths of a second) |
| SESSION_ID | NUMBER | Session ID |
| SESSION_SERIAL_NUM | NUMBER | Session serial number |
| CPU | NUMBER | CPU usage |
| PHYSICAL_READS | NUMBER | Number of physical reads |
| LOGICAL_READS | NUMBER | Number of logical reads |
| PGA_MEMORY | NUMBER | PGA size at the end of the interval |
| HARD_PARSES | NUMBER | Number of hard parses |
| SOFT_PARSES | NUMBER | Number of soft parses |
| PHYSICAL_READ_PCT | NUMBER | Physical read ratio |
| LOGICAL_READ_PCT | NUMBER | Logical read ratio |