New Client – Days four and five – Statspack
December 13, 2006 Leave a comment
After three days of handover, it was time to see what was going on on the database. So I asked the DBAs for a Statspack report for a typical 15 minute period.
This is an 8i database. Not being a DBA as such, more of a Designer/Developer and occasional Development DBA, I don’t read Statspack reports very often. Now and again. So I always have to look at Statspack in one hand, with something to reinforce my thinking in the other. Normally this is the “Tuning Strategies and Tools” section of Tom Kyte’s “Expert One-on-One Oracle” or the equivalent section in his book “Effective Oracle by Design” (both excellent books although the first book has been largely superceded by his excellent “Expert Oracle Database Architecture”.
The first thing that threw me was the format. It was slightly different than expected. It took me a while to figure it out but seems that the report being run is the 8.0 version. So, my first recommendation is to run the 8i report and investigate whether the perfstat user needs recreating or whether the 8.0 and 8.1 perfstat objects are the same.
Apart from some reformatting, the main information missing in the 8.0 report is the shared pool summary section and some missing statistics and percentages.
Second thing that was unfortunately obvious was that the database is running with timed_statistics set to FALSE. So, second recommendation is to run with that set to TRUE. There is a small overhead with this setting, documented as 2 to 3%, but well worth it. Unfortunately, this means that a significant part of the expected information is missing.
One of the first things that caught my eye was the log_buffer size. It well over 100 Meg. Unless I’m mistaken, one of the conditions of LGWR writing this information to disk is when the log_buffer gets to 1M full. That would suggest that there’s a good 100 Meg wasted. I don’t imagine that this is a big issue, apart from just being wasted memory. So if memory is scarce in other areas, here’s where there’s some spare. So, let’s get this reduced to something more sensible.
Next, in the load profile, hard parses is reasonable high. This suggests that either the shared pool is too small or there is some literal usage rather than bind variables. I expected this as I knew in advance that there are some areas of the application using literals and string concatenation. I followed up on this by checking some of the SQL in the SGA. Sure enough, there she blows! Loads of SQL without bind variables.
I understand the re-engineering of the client is not an option in the short-term. Therefore, I’m thinking that this is a candidate for setting the CURSOR_SHARING parameter. From the SGA, I can see that literal usage is limited to two specific users. So, if we use a sytem event trigger on logon, we can set this parameter at a session level for just these users. Which is nice.
The next thing is that the rollback/transaction percentage is 21%. My initial reaction to this to Holy Moly! I read around a bit, eventually coming across this article from Jonathan Lewis. So, looking at the statistics “rollback changes – undo records applied” and “db block changes”, I conclude that this is probably an automatic rollback from the queries generated by the thid party OR mapping tool. I will do some more investigation at a later stage when I get more familiar with the application/s and the database access layer.
Elsewhere in the report, it looks like nothing ever waits for anything (in terms of time) or maybe, just maybe, it’s that timed_statistics setting.
Elsewhere, we’ve got the top SQL which I will look into later. I’ll make a note of the SQL and try to find out where it’s coming from and what it’s doing. There’s a lot of SQL generation by the applications. Not something that a control freak like myself likes to see.
With the remaining statistics, I feel that I’m sort of drowning in the information. I’ll work through it slowly.
The priority is to get timed_statistics set to true and an 8i report generated.
The other thing I found was that you can upload Statspack reports to OraPerf. This automagically produces a really good report. This rings a distant bell (so much stuff does) as a site that I have used before but had forgotten about.
Making recommendations is one thing. Getting them pushed through into production, that’s another.