Monday, July 1, 2013

DB CPU Greater Than DB Time

According to Oracle documentation DB CPU is a child of DB Time. The document says that parent child relationship of these values is for containment only and child may not add up to the parent. But can the opposite happen? Child values exceed the parent value?
That's what has been happening on several 11gR2 Standard Edition RACs. (with PSU 11.2.0.3.4 and 11.2.0.3.5 applied). The statspack reports shows DB CPU greater than DB Time.
Example 1: 
Time Model System Stats  DB/Inst: GRAVEL/gravel1  Snaps: 1735-1746
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU                                              72.3     116.3
sql execute elapsed time                            30.4      48.9
connection management call elapsed                   7.7      12.4
parse time elapsed                                   1.5       2.4
PL/SQL execution elapsed time                        0.8       1.3
hard parse elapsed time                              0.3        .5
hard parse (sharing criteria) elaps                  0.1        .1
sequence load elapsed time                           0.1        .1
hard parse (bind mismatch) elapsed                   0.0        .0
repeated bind elapsed time                           0.0        .0
DB time                                             62.2
background elapsed time                             81.7
background cpu time                                 21.9
          -------------------------------------------------------------

Example 2:
Time Model System Stats  DB/Inst: GRAVEL/gravel1  Snaps: 1969-1970
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU                                             109.3     104.6
sql execute elapsed time                            58.5      56.0
connection management call elapsed                   7.3       7.0
parse time elapsed                                   3.8       3.6
PL/SQL execution elapsed time                        2.4       2.3
hard parse elapsed time                              1.3       1.2
hard parse (sharing criteria) elaps                  1.3       1.2
sequence load elapsed time                           0.2        .2
PL/SQL compilation elapsed time                      0.0        .0
repeated bind elapsed time                           0.0        .0
hard parse (bind mismatch) elapsed                   0.0        .0
DB time                                            104.5
background elapsed time                             95.3
background cpu time                                 26.2
          -------------------------------------------------------------
DB CPU exceeding DB Time has been happening intermittently for while. Following graphs show the different between DB CPU and DB Time and values below 0 indicate instances where DB CPU greater than DB Time.

After raising a SR Oracle has created "Bug 16300155 STATSPACK REPORTS SHOW DB CPU GREATER THAN DB TIME" and still investigating.



Similar observation was also made on a Enterprise Edition RAC as well.

This had been known to Oracle and already had a bug number (Bug 12713813: DB CPU LOOKS OVER-ESTIMATED) assigned to investigating this. However it was mentioned by Oracle that this bug hasn't been progressed because Oracle couldn't reproduce the issue. For the observed values it was said that DB Time is too little and to raise a SR (for awr related issue) if it's observed again at higher DB Times, although the Oracle engineer agreed that this an oddity and impossibility (that DB CPU is greater than DB Time no matter how small the observed values are).

Update 05 July 2013
AWR related SR was reopened as DB CPU higher than DB Time was observed during high activity time.


Update 18 July 2013
New bug has been created with regard to this issue. Bug 17181272 - AWR REPORTS DB CPU GREATER THAN DB TIME ON RAC DATABASE. Current status is development working.