Before you can
identify problem SQL in your database, you have to ask the
question of what is ‘bad SQL.’ What criteria do you use
when you begin the hunt for problem SQL in your critical
systems?
Understand that even the seasoned experts
disagree on what constitutes efficient and inefficient SQL;
so there’s no way to sufficiently answer this question to
every Oracle professional’s satisfaction. What follows are
some general criteria you can use when evaluating the output
from various database monitors or personal diagnostic
scripts:
-
Overall Response (Elapsed) Time –
This is how much time the query took to parse, execute,
and fetch the data needed to satisfy the query. It should
not include the network time needed to make the round trip
from the requesting client workstation to the database
server.
-
CPU Time – This
is how much CPU time the query took to parse, execute, and
fetch the data needed to satisfy the query.
-
Physical I/O – Often used as the
major statistic in terms of identifying good vs. bad SQL,
this is a measure of how many disk reads the query caused
to satisfy the user’s request. While you certainly want
to control disk I/O where possible, it’s important that
you not focus solely on physical I/O as the single
benchmark of inefficient SQL. Make no mistake, disk
access is slower than memory access and also consumes
processing time making the physical to logical transition,
but you need to look at the entire I/O picture of a SQL
statement, which includes looking at a statements’ logical
I/O as well.
-
Logical I/O – This is a measure of
how many memory reads the query took to satisfy the user’s
request. The goal of tuning I/O for a query should be to
examine both logical and physical I/O, and use appropriate
mechanisms to keep both to a minimum.
-
Repetition – This is a measure of
how often the query has been executed. A problem in this
area isn’t as easy to spot as the others unless you know
your application well. A query that takes a fraction of a
second to execute may still be causing a headache on your
system if it’s executed erroneously (for example, a query
that executes in a runaway PL/SQL loop) over and over
again
There are other criteria that you can
examine, like sort activity or access plan statistics (that
show items such as Cartesian joins and the like), but more
often than not, these measures are reflected in the criteria
listed above.
Fortunately, Oracle records all the above
measures (at least Oracle9i does), which makes tracking the
SQL that’s been submitted against an Oracle database a lot
easier.
|
|
