Re: Monitoring software
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: cicciuxdba@xxxxxxxxx, ORACLE-L <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 2 Jul 2010 12:24:18 -0700
For tuning queries there are 3 things to watch out for SQL
1. resource usage
2. elapsed time
3. future response time
Issue 1 easy to do with an interface like OEM's performance page or DB
Optimizers profiling page. Even your managers can understand. I've had
managers roll their eyes at statspack reports and the like and continue to
have meetings,discussions and arguments for weeks and then I give the same
manager and group a breakdown of performance like the OEM Top Activity page
or DB Optimizer profile page and all of a sudden everyone gets it. "Oh,
there is a SQL statement taking up 90% of the CPU resources and our CPU is
being maxed out, we should tune that one SQL statement even if it takes a
week of dev time". like no-duh. Also such an interface will point out if
there are concurrency issues clearly and if the database requires
configuration, or if the machine really is underpowered for the load
required:
http://sites.google.com/site/embtdbo/wait-event-documentation/database-tuning
Issue 2 is where users come in. If users expect certain response times, then
monitoring changes in response time is the way to go. OEM has a nice feature
for this where a snapshot of current response times is taken and then future
regular snapshots are compared.
Issue 3 as Stephane Faroult points out is important. One approach is
querying DBA_HIST_SQLSTATS and seeing if a SQL statement is using more and
more time, CPU, IOs, LIOs etc over the week, month etc. The problem with
this approach is DBA_HIST_SQLSTATS is only showing the top SQL what about
the "little" sql that will soon be the big bad SQL?
Stephane also has a nice example in his book, The Art of SQL Tuning, where a
"fast" query, say less than 1/10sec that doesn't use an index can bring down
the performance of the whole system, including well written fast queries,
when that "fast" sql is executed often. DB Optimizer or OEM's Top Activity
will easily identify the problem query when they become a problem, but one
way of identifying such queries before they are a problem would be running a
script like:
http://db-optimizer.blogspot.com/2010/05/identifying-execution-plan-problems.html
which shows LIOs per line in the execution plan, identifying operations that
perform high amounts of LIO and could possibly be tuned better. I got this
idea from Cristian Antognini in his awesome book "Troubleshooting Oracle
Performance" p341. The script also has a second goal of identifying where
the Oracle optimizer make mistakes in its calculations that could have lead
to the wrong plan. This idea is based on Wolfgang Breitling's TCF or tuning
by cardinality feedback
http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf
But even if you do/can identify the SQL to tune and you've got a bunch of
Java programmers, how can you rightfully expect them to know how to tune
it? It's a lot of work knowing how to tune queries, so why make everyone
learn it. Really there should be dedicated resources to tune queries. I ask
for this and was denied such a resource when working on OEM 10g, but it only
makes sense. There should be a mentor or a resource. This often turns out to
be the DBA who has his/her own set of tasks and is probably overworked and
often instead of being a resource the DBA becomes someone to blame. To end
the blame game, the solution to this for me is to try and make identifying
bad SQL and tuning SQL as easy as possible, so easy that the developers can
do it themselves. The following link is an example of a talk by Jonathan
Lewis on things to look for and steps to take in tuning SQL and then my
attempts to roll much of that work into a software interface that developers
can use:
http://db-optimizer.blogspot.com/2010/06/jonathan-lewis-webinar-replay.html
DISCLAIMER: I work on DB Optimizer so my opinion is of course biased (and my
opinions don't reflect my employer)
Best
Kyle Hailey
http://db-optimizer.blogspot.com/
Other related posts:
