Skip to Main Content
  • Questions
  • Operating System "Busy Time" Verses DB CPU Time

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Steve.

Asked: January 21, 2016 - 12:43 am UTC

Last updated: February 17, 2016 - 12:45 am UTC

Version: 11.2.0.2

Viewed 1000+ times

You Asked

Hi Tom,

I am told by the Unix folks that the database server is using 100% CPU. This seems to be verified by the OS Busy Time -
if I divide by 5,868,901 (BUSY TIME) /100 (convert to seconds) /60 (convert to minutes) /41 (elapsed minutes) is appox 24 - number of CPUs.

However, the DB CPU load shows 5.3 (CPUS) - (close to the similar calculation of DB CPU seconds divided out).

These leaves a gap of almost 19 CPUs doing something which I can not account.

Any ideas on what the gap could be from or where else to look would be much appreciated.

Thanks,
Steve

PS: I am told there is nothing else running on the running on the Unix box.



WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Startup Time Release RAC
xxxxx ####### xxxxx 1 22-Nov-15 05:11 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory (GB)
xxxxx AIX-Based Systems (64-bit) 24 6 109.00

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 59457 20-Jan-16 17:10:20 3284 28.7
End Snap: 59460 20-Jan-16 17:52:06 3087 30.0
Elapsed: 41.76 (mins)
DB Time: 1,973.74 (mins)
Report Summary
Cache Sizes

Begin End
Buffer Cache: 18,176M 18,176M Std Block Size: 8K
Shared Pool Size: 6,144M 6,144M Log Buffer: 16,536K
Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 47.3 0.4 0.00 0.01
DB CPU(s): 5.3 0.1 0.00 0.00
Redo size: 6,533,896.7 55,657.5
Logical reads: 709,295.6 6,042.0
Block changes: 37,217.3 317.0
Physical reads: 23,360.1 199.0
Physical writes: 1,285.2 11.0
User calls: 4,151.7 35.4
Parses: 428.2 3.7
Hard parses: 17.0 0.1
W/A MB processed: 149.7 1.3
Logons: 0.2 0.0
Executes: 13,005.3 110.8
Rollbacks: 0.0 0.0
Transactions: 117.4
Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.96 Redo NoWait %: 100.00
Buffer Hit %: 98.62 In-memory Sort %: 100.00
Library Hit %: 99.53 Soft Parse %: 96.04
Execute to Parse %: 96.71 Latch Hit %: 99.87
Parse CPU to Parse Elapsd %: 14.99 % Non-Parse CPU: 99.19
Shared Pool Statistics

Begin End
Memory Usage %: 55.37 54.73
% SQL with executions>1: 80.78 86.03
% Memory for SQL w/exec>1: 71.22 80.59
Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
db file sequential read 13,784,072 29,059 2 24.54 User I/O
DB CPU 13,355 11.28
enq: TX - row lock contention 4,823 11,109 2303 9.38 Application
db file parallel read 218,474 1,688 8 1.43 User I/O
log file sync 229,584 1,417 6 1.20 Commit
Host CPU (CPUs: 24 Cores: 6 Sockets: )

Load Average Begin Load Average End %User %System %WIO %Idle
42.04 38.62 83.4 14.3 1.3 2.3
Instance CPU

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
22.8 23.4 0.0
Memory Statistics

Begin End
Host Mem (MB): 111,616.0 111,616.0
SGA use (MB): 24,576.0 24,576.0
PGA use (MB): 15,461.1 15,290.2
% Host Mem used for SGA+PGA: 35.87 35.72

Time Model Statistics
Total time in database user-calls (DB Time): 118424.2s
Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
sql execute elapsed time 114,363.19 96.57
DB CPU 13,355.21 11.28
PL/SQL execution elapsed time 2,156.47 1.82
parse time elapsed 1,087.47 0.92
hard parse elapsed time 904.52 0.76
hard parse (sharing criteria) elapsed time 97.37 0.08
PL/SQL compilation elapsed time 55.99 0.05
hard parse (bind mismatch) elapsed time 47.45 0.04
sequence load elapsed time 15.25 0.01
repeated bind elapsed time 6.75 0.01
inbound PL/SQL rpc elapsed time 5.41 0.00
connection management call elapsed time 3.45 0.00
RMAN cpu time (backup/restore) 2.65 0.00
failed parse elapsed time 0.51 0.00
DB time 118,424.15
background elapsed time 3,257.38
background cpu time 356.86
Back to Wait Events Statistics
Back to Top


Operating System Statistics
*TIME statistic values are diffed. All others display actual values. End Value is displayed if different
ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
Statistic Value End Value
AVG_BUSY_TIME 244,429
AVG_IDLE_TIME 5,771
AVG_IOWAIT_TIME 3,093
AVG_SYS_TIME 35,583
AVG_USER_TIME 208,760
BUSY_TIME 5,868,901
IDLE_TIME 139,847
IOWAIT_TIME 75,557
SYS_TIME 856,289
USER_TIME 5,012,612
LOAD 42 39
OS_CPU_WAIT_TIME 10,780,400
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 2,218,082,304
VM_OUT_BYTES 769,720,320
PHYSICAL_MEMORY_BYTES 117,037,858,816
NUM_CPUS 24
NUM_CPU_CORES 6
NUM_LCPUS 24
NUM_VCPUS 6
GLOBAL_RECEIVE_SIZE_MAX 2,359,296
GLOBAL_SEND_SIZE_MAX 2,359,296
TCP_RECEIVE_SIZE_DEFAULT 16,384
TCP_RECEIVE_SIZE_MAX 9,223,372,036,854,775,807
TCP_RECEIVE_SIZE_MIN 4,096
TCP_SEND_SIZE_DEFAULT 16,384
TCP_SEND_SIZE_MAX 9,223,372,036,854,775,807
TCP_SEND_SIZE_MIN 4,096
Back to Wait Events Statistics
Back to Top



and we said...

We can definitely assume its the database, since the TotalCPU is up near 24.

"DB CPU" is the amount of time that the database was "directly" burning CPU, eg, we had some data in memory and we were scanning/modifying it.

However, indirectly, we may also burn a lot of CPU in doing things like I/O. There's a commonly used quote in Oracle "Everything is a CPU problem" :-) Ever wondered why storage arrays have powerful CPU's on them ? Because it takes CPU to do I/O.

Look at your DB Time figure (ie, DB CPU + DB Wait time). Its massive.

And now look at "db file sequential read"

13,784,072 in 41mins = 5600 reads per second. You're belting away at your I/O infrastructure pretty hard.

So start there - head to the SQL section of the report and look for high executions and/or high I/O counts.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

AIX counts time different

Christo Kutrovsky, February 16, 2016 - 6:56 pm UTC

Hi Connor, your answer would be correct for Linux. But on AIX there's an "adjusted" CPU usage based on core usage (vs. thread usage).

Read this article here:
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power%20Systems/page/Understanding%20CPU%20utilization%20on%20AIX

Basically DB time - uses "adjusted" core time, while the OS stats use the non-adjusted THREAD cpu times.


Chris Saxon
February 17, 2016 - 12:45 am UTC

Having worked on a large AIX system for many years - I long ago gave up trying to get the db's view of resource utilisation to match AIX's view, with lpars and fractions of cpu's and over/under allocation allowances etc etc :-)

Thanks for stopping by, and for the link.