07 December, 2014

Statistics on this blog

I began this blog on 28-Dec-2006.  For the 8 years 2007 to 2014, I have averaged 56 posts per year.  Unfortunately, this year, 2014, has produced the fewest posts -- 40 including this one.  This includes the "series" on Grid / ASM / RAC and the series on StatsPack / AWR.

2011 was my most prodigious year -- 99 posts.

There were 8,176 page views in July 2010.  To date, there have been more than 930thousand (946thousand at the end of 2014) page views on this blog.  By month, the peak count has been for March 2013 -- 24,346 page views.

My largest viewer counts are from USA, India, UK, Germany and France.  www.google.com has been the largest source of traffic to this blog.

.
.
.



02 December, 2014

StatsPack and AWR Reports -- Bits and Pieces -- 4

This is the fourth post in a series.

Post 1 is here.
Post 2 is here.
Post 3 is here.

Buffer Cache Hit Ratios

Many novice DBAs may use Hit Ratios as indicators of performance.  However, these can be misleading or incomplete.

Here are two examples :

Extract A: 9i StatsPack

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer  Hit   %:   99.06

It would seem that with only 0.94% of reads being physical reads, the database is performing optimally.  So, the DBA doesn't need to look any further.  

Or so it seems.

If he spends some time reading the report, he also then comes across this :

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                           837,955       4,107    67.36
CPU time                                                        1,018    16.70
db file scattered read                             43,281         549     9.00



                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read           837,955          0      4,107      5    403.3
db file scattered read             43,281          0        549     13     20.8

Physical I/O is a significant proportion (76%) of total database time.  88% of the physical I/O is single-block  reads ("db file sequential read").  This is where the DBA must identify that tuning *is* required.

Considering the single block access pattern it is likely that a significant proportion are index blocks as well.  Increasing the buffer cache might help cache the index blocks.


Extract B : 10.2 AWR

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %:99.98Redo NoWait %:100.00
Buffer Hit %:96.43In-memory Sort %:99.99
Library Hit %:97.16Soft Parse %:98.16
Execute to Parse %:25.09Latch Hit %:99.85
Parse CPU to Parse Elapsd %:89.96% Non-Parse CPU:96.00

The Buffer Hit Ratio is very good.  Does that mean that I/O is not an issue ?

Look again at the same report 

Top 5 Timed Events
EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time147,59342.3
db file sequential read31,776,67887,659325.1User I/O
db file scattered read19,568,22079,142422.7User I/O
RMAN backup & recovery I/O1,579,31437,6502410.8System I/O
read by other session3,076,11014,21654.1User I/O

User I/O is actually significant.  The SQLs with the highest logical I/O need to be reviewed for tuning.

.
.
.