Statistics are at the core of determining whether your performance tuning efforts are working or not. James Koopmann dives into performance methodologies, specifically Oracle's performance method, to give us a better understanding of how to tune a database with statistics.
In my last article, I started to look at methods
of performance tuning but it quickly turned into a discussion on
throughput, response time, and some statistics that are available for us to
use. This is because statistics are at the core of determining whether your
tuning efforts are working or if you still have a system that is suffering from
performance problems. This article takes a little bit deeper dive into
performance methodologies, specifically Oracle's performance method, so that we
might begin to understand how we commence to tune a database, with statistics
of course.
The Oracle performance method is rooted in finding those bottlenecks
(performance problems) that are having issues (can be seen by using
statistics). This is probably a good place to state that the use of statistics
can be done either proactively or reactively. Proactively is typically where we
would like to be, having all the time in the world to monitor our database and
detect issues as they begin to rear their ugly head. While it is true that we
can predict and plan for the future there are many more instances, that I've
come up against, that are of a more reactive nature; causing us enormous
amounts of stress only to find that something was deployed within a production
environment without proper QA.
Regardless if a performance issue has been detected proactively or
reactively the steps to prepare ourselves for a tuning opportunity takes
somewhat of a similar form. I have always found, and Oracle agrees, that
talking with a user and getting feedback on the issue they are experiencing is
critical for determining the scope and goals for improvement. I often give the
example, and I think it is a good one, of a clerk who has to produce a report
every morning. This clerk will typically press the submit button, get up and
get a cup of coffee, chat/say hello to a colleague or two, and then proceed
back to his desk to read the report. Under normal circumstances, if he was very
chatty, he gets back to his desk in 10 to 15 minutes, with the report ready to
be read. However, one day, during flu season let say, the clerk isn't as chatty
because he has a sore throat. Clearly, you can see that this clerk may now
complain about the report not running as it should and have a perceived performance
problem. This is why talking to users is so important. Being thorough about the
types of questions you ask is even more important. Not only are questions about
the computer system important but habits and specific tasks of the day could
lead to some very interesting results, namely no new performance goals and
something you don't have to worry about. Now that being said, we could have
easily inserted an angry boss that doesn't like all the chatter that goes on
first thing in the morning. In this case, the clerk might decide to complain
and say that they can't do their work anyway because the report doesn't get
produced for 10 minutes. Now we have a real performance goal and must do
something to accomplish it.
Part of preparing to tune the database always involves checking all systems
involved in the performance of an application, query, tools, whatever. Making
sure there are no red lights on the front of storage arrays (no hardware
issues) as well as ensuring that the operating system resources are not
overused or saturated. This may be difficult for a DBA to perform if they are
not familiar with some of the environment commands. This is where we should
have a list of questions we can quickly ask our system administrators, network
administrators, and fellow DBAs.
While the first preparatory step (question users) is semi non-technical you
can see that we turn our focus to statistics and technical details very
quickly. After getting a glimpse of the system level statistics (operating
system level statistics), or at the same time, we can begin to turn our focus to
the database. As a preparatory step to database tuning we must ensure, (hopefully
before a performance bottleneck is noticed), that STATISTICS_LEVEL has been
properly set. I hope that it is set to at least TYPICAL but from an Oracle
performance tuning method, it needs to be set to ALL to enable the automatic
performance tuning features as well as AWR and ADDM. In that same regard,
Oracle would like you to ensure that the CONTROL_MANAGEMENT_PACK_ACCESS
parameter is set to DIAGNOSTIC+TUNING or DIAGNOSTIC to enable ADDM.
If we have done the preparatory steps as outlined above, it is now time to
actually tune the database. The Oracle method of tuning now dictates that we
actually have three opportunities to tune the database, two of which we have
already mentioned. So we can tune the database:
1. proactively and on a regular basis - proactively and regular means
nothing more than having a set of tools that you can use every day, either by
submitting requests manually or having them done automatically for you. More
than likely the running of these scans/reports will produce a list of items
that you can work on, helping you to fine-tune and reach performance goals.
ADDM, from the Oracle methodology perspective, is a nice tool that can be used
to detect and report on performance problems as well as automatically detecting
some of the most common performance problems found in Oracle databases.
Also, remember that proactively could mean you sitting at a console and
issuing SQL statements to query internal database statistics or using
Enterprise Manager to drill down into expected problem areas.
Proactive really means that you are just doing something now to help
determine if the database is not performing well.
2. reactively after we are told users or applications are having issues
- the opposite of proactive is reactive and reactively fighting performance
problems is something we all wish to avoid; it's too stressful. Regardless, the
Oracle methodology dictates that we still make use of the ADDM reports but
leans heavily on historical analysis, possibly using the Active Session history
(ASH) reports as they report on past activity.
Often times, just because bad performance tends to linger (or else it
wouldn't be recognizable) a reactive performance tuning attempt often turns
into a proactive tuning drill, trying to catch something that is currently
having issues or will soon have issues again.
3. identifying and tuning resource-hungry SQL statements - ADDM is
often at the center of Oracle's method of tuning, and tuning bad SQL is no
different. ADDM can quickly be used to identify high-load SQL statements (those
that are causing the most problems/contention within the database). Tuning
these bad SQL statements can be somewhat of a black-art but there is help in
the form of the SQL Tuning Advisor. Tuning SQL should be looked at from the
standpoint of optimizing data access paths and limiting I/O.
I said it in the last article, said it at the beginning of this one, and
will say it again: tuning a database is firmly rooted in statistical gathering.
Without statistics, and the ability to relate them to workloads we would not be
able to tune our databases. Only after accumulating statistics can we begin to
create a methodology of tuning databases. The Oracle method of performance
tuning is no different, just the steps and the tool.
Related Articles
Methods of Oracle Database Performance Tuning
Getting Ready to Tune Your Oracle Database
Oracle Database 11gR2 I/O Performance Tuning: A Primer
Oracle Database 11g: SQL Performance Analyzer, Part 1
Oracle Database 11g: SQL Performance Analyzer, Part 2
Oracle Database 11g: SQL Performance Analyzer, Part 3
Back to DBAsupport.com
