in Oracle

StatsPack and AWR Reports — Bits and Pieces — 1

I am planning to put up a few posts on snippets from StatsPack and AWR reports. This is my first post.
Note : Some figures / details may be slightly changed / masked to hide the real source.

Logical I/O and Change rates :
1. From a 9.2 StatsPack Report:

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: Std Block Size: 4K
Shared Pool Size: Log Buffer:

Load Profile
~~~~~~~~~~~~ Per Second
---------------
Redo size: 56,031.63
Logical reads: 68,286.24
Block changes: 314.88
Physical reads: 842.92
Physical writes: 134.76

With a 4KB Block Size 68,286.24 Logical Reads translates to slightly over 266MB/second. Logical I/O is CPU-bound. Database activity is Read-Intensive with a high rate of Reads relative to Writes.

2. From an 11.2 AWR Report :
Cache Sizes

Begin End
Buffer Cache: Std Block Size: 16K
Shared Pool Size: Log Buffer:

Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s):
DB CPU(s):
Redo size: 1,593,717.1
Logical reads: 51,884.5
Block changes: 4,222.6
Physical reads: 64.1
Physical writes: 137.7

With a 16KB Block Size, 51,884.5 Logical Reads translates to slightly over 810MB/second. This consumes CPU cycles. However, here the number of Block Changes is noticeably high in this environment. This is also reflected in the high Redo rate — slightly over 5,471MB/hour (Note : “Redo size” is in Bytes).

CPU Consumption :
1. From a 9.2 StatsPack Report :

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 37.5 2.1
CPU used when call started 37.6 2.1

This indicates 0.375seconds of CPU usage per second — i.e. approximately 37.5% of 1 CPU (let’s take this as an older non-multicore architecture). If the server has 4 CPUs, CPU consumption is 9.4%

2. From an 11.2 AWR Report :

Instance Activity Stats

  • Ordered by statistic name
Statistic Total per Second per Trans
… deleted rows ….
… deleted rows ….
CPU used by this session 46.15
CPU used when call started 45.87

This indicates 0.462seconds of CPU usage per second — i.e. approximately 46.2% of 1 Core. This is also reflected in the Load Profile section :

Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s):
DB CPU(s): 0.5 0.1 0.00 0.00
How many CPUs does this machine have ? AWR reports this :

Host Name Platform CPUs Cores Sockets Memory (GB)
Linux x86 64-bit 16 16 2

That means we are using less than half of 1 of 16 cores ! This translates to CPU consumption of 3.125% The server has too many CPU cores !