AWR report in Oracle

This is for would be DBAs! My hair is getting grayed now - so I took the liberty to share some of my knowledge which I have gathered so far. The first step I though would be to show would be DBAs how to read AWR report.

First thing First - to generate a AWR report you have to use ?/rdbms/admin/awrrpt.sql script, proved the begin snap and end snap and you get a neat looking report on the DB performance. The report is quite exhaustive and can be a very good pointer to all your performance point. Below is a sample report (partial) -

First lets take a look at Top 5 timed events -

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc buffer busy 967,039 31,837 33 39.4 Cluster
CPU time 15,581 19.3
enq: TX - row lock contention 83,389 8,890 107 11.0 Applicatio
gc current block busy 187,560 8,342 44 10.3 Cluster
buffer busy waits 605,185 3,356 6 4.2 Concurrenc
-------------------------------------------------------------

Remember this is Top 5 Timed event not necessarily wait events as the name says that these are timed events. First on the list GC BUFFER BUSY, the wait can occur for so many reason . But in a RAC installation GC BUFFER BUSY shows that the buffer was busy waiting to release a BLOCK for other session. Meaning a session was waiting for a block to be update etc but was waiting as there was another process who was working on the same. This common wait comes from concurrent update/delete and causes bottleneck in the system performance. To eliminate the wait you can follow some thumb rule -

1. Find out the object in question (hot objects), infact AWR gives you a quite a lot of information on this. Which we will discuss in the later posts.

2. Make sure you have enough FREELISTS and FREELIST_GROUPS defined on that object.

3. Move the object from ASSM to manually manged tablespace.

4. Use smaller blocks

5. Try to see the application code and see if there is a possibility of logically dividing the concurrent updates(Application partitioning)

The above are just the pointers which you can use, based on your situation and resolve the GC Buffer Wait issue. Again - this is just a pointer detail examination is required and probable solution needs to be tested.

Above all there will be always some GC BUFFER BUSY in a RAC instance - its normal.

Next one on our list is CPU TIME. CPU TIME show the time spent on processing using CPU resources and the reason can be many. Poorly tuned CODE is perhaps the top most in the culprit list. But CPU time also show that if its possible to add in more CPU / increase CPU Speed the problem may get some relief. Remember in 10g onward the default optimizer goal in set to CPU , if its changed in your environment, setting Optimizer goal to CPU may help. Try it out and see for yourself.

Third is an enque where we have row lock contention. The enque clearly show that the multiple concurrent process if fighting for locks on the table row and creating a contention there. Analyze application code and see if that can be partitioned. Some storage parameter change(PCTFREE/PCTUSED) might help in this scenario.

I have touched on the TOP 3 even in the same report today will come back with more in my next post. Fell free to comment on the post and if you have any question - please do let me know.

Arindam