Oracle-監控使用系統資源較多的使用者資訊

Oracle-監控使用系統資源較多的使用者資訊

---

下述語句監控當前活動使用者中, 使用cpu,邏輯讀, 物理讀, 解析次數較多的session的相關資訊. 報表結果包括, session id, 系統pid, session狀態, 正在執行的sql語句等.

select se.r1,
       se.sid,
       se.spid,
       se.status,
       se.name,
       se.value,
       sq.sql_text,
       sq.piece,
       se.status,
       se.prog,
       se.terminal,
       se.osuser
  from (select rank() over(partition by e.name order by value desc) r1,
               a.sid,
               b.spid,
               e.name,
               value,
               a.sql_address,
               a.STATUS,
               substr(a.program, 1, 40) prog,
               a.terminal,
               osuser
          from v$session a, v$process b, v$sesstat c, v$statname e
         where c.sid = a.sid
           and a.paddr = b.addr
           and c.STATISTIC# = e.STATISTIC#
               and e.NAME in( 'db block changes',
                              'CPU used by this session',
                              'parse count (hard)',
                              'parse count (total)',
                              'physical reads',
                              'physical reads direct',
                              'session logical reads'
                              )    
           and a.status = 'ACTIVE'
           and value >0
           ) se,
       v$sqltext sq
 where se.sql_address = sq.ADDRESS(+)
   and se.r1 <= 3   
 order by se.status,se.name,se.r1,  sq.PIECE;


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 V$SESSTAT
根據oracleonlinebook中的描述, v$sesstat儲存session從login到logout的詳細資源使用統計.
V$sysstat和v$sesstat差別如下:
?    V$sesstat只儲存session資料, 而v$sysstat儲存所有sessions的累計值.
?    V$sesstat只暫存資料, session退出後資料即清空. V$sysstat則是累積的, 只有當例項被shutdown才會清空.
?    V$sesstat不包括統計項名稱, 如果要獲得統計項名稱則必須與v$sysstat或v$statname連線查詢獲得.
V$sesstat可被用於找出如下型別session
?    高資源佔用
?    高平均資源佔用(登陸後資源使用率)
?    預設資源佔用比(兩快照之間)
V$sesstat常用列說明
Column    Datatype    Description
SID    NUMBER    Session identifier
STATISTIC#    NUMBER    Statistic number (identifier)
Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.
VALUE    NUMBER    Statistic value


監控項說明
CLASS    NAME    desc
1    CPU used by this session    所有session的cpu佔用量, 不包含後臺程式. 這項統計的單位是毫秒.
8    db block changes    造成SGA中資料塊變化的insert,update或delete運算元.
64    parse count (hard)    在shared pool中解析呼叫的未命中次數. 當sql語句執行並且該語句不在shared pool或雖然在shared pool但因為兩者存在部分差異而不能被使用時產生硬解析. 硬解析會帶來cpu和資源使用的高昂開銷, 因為它需要oracle在shared pool中重新分配記憶體, 然後再確定執行計劃, 最終語句才會被執行.
64    parse count (total)    解析呼叫總數, 包括軟解析和硬解析. 當session執行了一條sql語句, 該語句已經存在於shared pool並且可以被使用則產生軟解析.
64    parse time cpu    總cpu解析時間(單位 10ms).包括硬解析和軟解析
64    parse time elapsed    完成解析呼叫的總時間花銷
8    physical reads    OS blocks read數. 包括插入到SGA快取區的物理讀以及PGA中的直讀. 這項統計並非IO請求數.
8    physical reads direct    　
1    session logical reads    邏輯讀請求數
64    sorts (disk)    在硬碟上執行的排序次數
64    sorts (memory)    在記憶體中執行的排序次數
64    sorts (rows)    列排序總數.
64    table scan blocks gotten    全表掃描中讀取的總塊數