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)
Buffer Cache: 1,888M 1,888M Std Block Size: 8K
Shared Pool Size: 1,024M 1,024M Log Buffer: 3,168K
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
% 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
A few days ago, I happened to participate in an interesting thread on an OTN forum. We weren’t able to answer all of the questions raised by the original poster, but this discussion stirred some thoughts that eventually lead me to writing this post.
One of the key things that make database a database and not a data dump, is integrity constraints. They allow to implement restrictions on data in a very efficient fashion, and independently of the current application interface. But constraints also play a very important role in providing additional information about data to the query optimizer. Failure to povide that information, or providing it incorrectly that may lead to serious performance issues.
Let’s consider an example.
Bad plan or something else?
The first step in any tuning activity is determining the scope of an issue — so if a query is not performing satisfactory, then the first question is whether it’s query’s execution plan or something else (e.g. a global database issue or even a problem external to the database). Full diagnostics may be time-consuming or even inaccessible, so it’s desirable to resolve this question by just looking at the query and its basic statistics. There is a method of doing that using SQL efficiency, and in this post I’m going to describe it.
The method is not my invention. I read about it in Christian Antognini’s book “Troubleshooting Oracle Performance”, but in fact I was using it in a slightly different form long before. Continue reading
Last week, I attended Latvian Oracle User Group conference Harmony 2012, held in Riga, Latvia on June 1. The event featured several “stars” of the Oracle world, including Tom Kyte, Tanel Poder, Graham Wood, Daniel Morgan and others. Events like this are a great opportunity to catch up with recent trends in Oracle development and tuning, learn new tricks of the trade, hear the best tuning specialist tell how they tackled their most difficult performance cases, and more!