DB CPU time% 的计算公式

（1）       Database 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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-747812/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-747812/