22 October, 2014

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
BeginEnd
Buffer Cache:
Std Block Size:16K
Shared Pool Size:
Log Buffer:
Load Profile
Per SecondPer TransactionPer ExecPer Call
DB Time(s):



DB CPU(s):



Redo size:1,593,612.1
Logical reads:51,872.5
Block changes:4,212.4
Physical reads:63.8
Physical writes:133.1

With a 16KB Block Size, 51,872.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
StatisticTotalper Secondper Trans
... deleted rows ....


... deleted rows ....


CPU used by this session46.85
CPU used when call started46.27

This indicates 0.468seconds of CPU usage per second -- i.e. approximately 46.8% of 1 Core.  This is also reflected in the Load Profile section :
Load Profile
Per SecondPer TransactionPer ExecPer Call
DB Time(s):



DB CPU(s):0.50.10.000.00

How many CPUs does this machine have ?  AWR reports this :
Host NamePlatformCPUsCoresSocketsMemory (GB)
Linux x86 64-bit16162

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 !

3 comments:

theoracledream said...

Hi Hemant,

If you could confirm this,

The per second statistics for CPU used by this session is for 1 core , does not show CPU used by all cores available to database.

Thanks,
Swapnil

Hemant K Chitale said...

Swapnil,

The statistic is for all CPU time -- i.e. across all the CPU cores.

Thus, for example a report that shows 3 seconds of CPU used per second is equivalent to 100% of 3 cores or 75% of 4 cores being used.

Hemant

theoracledream said...

Thank You Hemant