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