從V$SESSMETRIC檢視中找出當前最佔用資源的會話

hooca發表於2014-09-26
SSM會話
METRIC：度量值，是一個相對值，用來比較多個會話所佔用的資源，並非實際值。


點選(此處)摺疊或開啟

  1. select
  2. TO_CHAR(m.end_time,'DD-MM-YYYY HH24:MI:SS') e_dttm, -- Interval End Time
  3. m.intsize_csec/100 ints, -- Interval size in sec
  4. s.username usr,
  5. m.session_id sid,
  6. m.session_serial_num ssn,
  7. ROUND(m.cpu) cpu100, -- CPU usage 100th sec
  8. m.physical_reads prds, -- Number of physical reads
  9. m.logical_reads lrds, -- Number of logical reads
  10. m.pga_memory pga, -- PGA size at end of interval
  11. m.hard_parses hp,
  12. m.soft_parses sp,
  13. m.physical_read_pct prp,
  14. m.logical_read_pct lrp,
  15. s.sql_id
  16. from v$sessmetric m, v$session s
  17. where (m.physical_reads > 100
  18. or m.cpu > 100
  19. or m.logical_reads > 100)
  20. and m.session_id = s.sid
  21. and m.session_serial_num = s.serial#
  22. 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

根據以上的SQL_ID，可以查出具體的SQL語句。

點選(此處)摺疊或開啟

  1. SELECT SQL_TEXT FROM V$SQLAREA WHERE SQL_ID='8h1qaqha580hh'

select /*+ FULL(bom) */ id from bom where id=100001000
下面貼出V$SESSMETRIC各列的含義：

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1281486/，如需轉載，請註明出處，否則將追究法律責任。

相關文章