DB CPU time% 的計算公式

：

該指標是說明cputime佔db call time的比率。該指標越高，說明sql語句在做更多有效的操作(例如：sort、group等)，而沒有花費太多的時間在wait方面(例如：io wait，row lock，hot blocks)

我們可以透過消減等待事件來提升cputime usage%.

按照Absolute threshold進行評估，warning：<40% ; critical：<20%

 

 

WITH cpu AS (
SELECT INSTANCE_NUMBER, SNAP_ID, STAT_NAME,
       VALUE - LAG(VALUE, 1, 0) OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) cpu
  FROM DBA_HIST_SYS_TIME_MODEL
 WHERE STAT_NAME = 'DB CPU'
   AND SNAP_ID BETWEEN 51348 AND 54199),
DB AS (
SELECT INSTANCE_NUMBER, SNAP_ID, STAT_NAME,
       VALUE - LAG(VALUE, 1, 0) OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) dbtime
  FROM DBA_HIST_SYS_TIME_MODEL
 WHERE STAT_NAME = 'DB time'
   AND SNAP_ID BETWEEN 51348 AND 54199)
SELECT CPU.INSTANCE_NUMBER, CPU.SNAP_ID, round(CPU.CPU/1000000), round(db.DBTIME/1000000), ROUND(CPU.CPU/DB.DBTIME*100, 2) CPU_PERCENT
  FROM CPU, DB
 WHERE CPU.INSTANCE_NUMBER = DB.INSTANCE_NUMBER
   and CPU.SNAP_ID = DB.SNAP_ID;