AWR report case study: stating the obvious

It’s been a while since I came across an interesting and complex case. However, yesterday on an OTN forum thread I saw a case which was interesting by its simplicity. Even though it’s almost trivial on the technical level, it’s very useful to highlight typical tuning “rookie mistakes” (I can remember quite a few cases from not so long ago, when I did similar mistakes, too).

The author posts a question about “library cache: mutex X” events that are ruining performance of his 2-node RAC cluster. The original post doesn’t contain any specifics except for CPU utilization percentage on both nodes.

Within a few hours, a few replies appear, most of them either trying to shed light on this particular wait event or sharing similar experiences. I asked the original poster to provide key sections of an AWR report (workload profile, top events, database/host CPU, top SQL), which he soon did:

Continue reading “AWR report case study: stating the obvious”

Troubleshooting log file sync waits — a simple case study

Yesterday I was asked to give my opinion on the AWR below:

DB Name	DB Id	Instance	Inst num	Release	        RAC	Host
*****	*****	*****   	1	        10.2.0.4.0	NO	******

	        Snap Id	Snap Time	   Sessions  Cursors/Session
Begin Snap:	66340	6/20/2012 14:30	   117	  9.3
End Snap:	66341	6/20/2012 15:00	   115	  9.1
Elapsed:		30.16 (mins)
DB Time:		35.57 (mins)

Report Summary

Cache Sizes

	                Begin	End
Buffer Cache:	        1,888M	1,888M	Std Block Size:	8K
Shared Pool Size:	1,024M	1,024M	Log Buffer:	3,168K

Load Profile

	                Per Second	Per Transaction
Redo size:	        504,437.43	3,150.59
Logical reads:	        23,547.86	147.07
Block changes:	        1,900.01	11.87
Physical reads:	        1,931.69	12.06
Physical writes:	50.85	        0.32
User calls:	        478.35	        2.99
Parses:	                20.39	        0.13
Hard parses:	        0.05	        0
Sorts:	                8.67	        0.05
Logons:	                0.1	        0
Executes:	        250.29	        1.56
Transactions:	        160.11

% Blocks changed per Read:	8.07	Recursive Call %:	18.31
Rollback per transaction %:	0	Rows per Sort:	178.25

...

Top 5 Timed Events

Event	                Waits	        Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		                1,882		        88.2
log file sync	        293,178	        1,217	4	        57	                Commit
log file parallel write	290,903	        961	3	        45	                System I/O
db file scattered read	301,788	        45	0	        2.1	                User I/O
db file parallel write	11,839	        31	3	        1.5	                System I/O

Continue reading “Troubleshooting log file sync waits — a simple case study”

Library cache locks: a case study

Recently I was asked to help with a case involving library cache locks, and even though it was really simple, I think it’s good for illustrating a few points in performance analysis.

As it often happens, it all started with customers complaining about “the database being slow”. I asked for more specific details, and I found that it wasn’t the entire database, but rather a reporting subsystem. Since I didn’t have specific session sid or sql_id at hand, I started with analyzing a 30-min AWR report, and since there wasn’t much time to look at everything, I just focused on top timed events:

Event                   Waits          Time (s)  Avg Wait(ms) %Total  Wait Class

CPU time                               16,188               46.2
db file sequential read 18,230,956     11,787     1         33.6     User I/O
db file parallel write  278,480        4,509      16        12.9     System I/O
library cache lock      1,131          3,297      2,915     9.4      Concurrency
log file sync           120,356        1,686      14        4.8      Commit

Continue reading “Library cache locks: a case study”