v$sqlstats Performance View
Before we discuss the new v$sqlstats
view, let me deviate for a minute to
provide you with some quick SQL
tuning advice. V$SQLAREA is one of
my favorite SQL tuning views. Until
v$sqlstats came along…
If I want to perform a traditional
"top down" tuning approach and tune
the highest resource consuming SQL,
I'll use the statements below to
identify the top resource consuming
queries.
The following query identifies the
SQL responsible for the most disk
reads:
SELECT disk_reads,
executions, disk_reads/executions,
address, sql_text FROM
v$sqlarea WHERE disk_reads >
5000 ORDER BY disk_reads;
The following query identifies the
SQL responsible for the most buffer
hits:
SELECT buffer_gets,
executions, buffer_gets/executions,
address, sql_text FROM
v$sqlarea WHERE buffer_gets
> 10000 ORDER BY buffer_gets;
You can create a more readable
report in SQLPLUS by inserting
report breaks between the output
lines. To generate the report breaks
in SQLPLUS, issue the following
statement before running the query:
BREAK ON disk_reads SKIP 2
--- for the disk read report
and
BREAK ON buffer_gets SKIP 2
--- for the buffer get
report
The first query returns SQL
statements responsible for
generating disk reads greater than
5,000 while the second query returns
SQL statements responsible for
generating buffer reads greater than
10,000. I used these numbers just as
an example but I sometimes use them
as a starting point. I'll then
adjust them up or down accordingly,
based on their output. The numbers
also depend on the system I'm
reviewing. I'll use different
numbers for OLTP environments than I
would for data warehouses.
You'll notice that I divide the
number of disk and buffer reads by
the number of statement executions.
If a statement is generating
1,000,000 disk reads but is executed
500,000 times, it probably doesn't
need tuning.
Heavy disk reads per statement
execution usually means a lack of
proper indexing, poor selection
criteria, etc.. Heavy buffer reads
sometimes means the exact opposite -
indexes are being used when they
shouldn't be.
But I'm personally most interested
in workload, that's why I most often
use the buffer cache hits in my
initial queries.
But the SQLTEXT column in V$SQLAREA
does not provide the entire text of
the SQL statement. That's why I
include the address column in the
report. I can use that value to dump
the entire SQL statement from
V$SQLTEXT using the statement below
(where xxxxxxxx is the value in the
address column from the V$SQLAREA
reports above):
select sql_text from
v$sqltext where address = 'xxxxxxxxx'
order by piece;
Oracle 10G R2 provides a new view
called v$sqlstats that contains a
combination of columns that appear
in V$SQL and V$SQLAREA. The benefits
that v$sqlstats provides are as
follows:
-
Since v$sqlstats contains the
entire text of the SQL statement
AND its associated performance
statistics, we are no longer
required to access both the
V$SQLTEXT and V$SQLAREA to
obtain the information we need.
-
Oracle states that v$sqlstats is
faster and more scalable.
The data in V$SQLAREA has a tendency
to get its contents flushed out just
when you need to get additional
information from it. The v$sqlstats
view provides users with a longer
access window. That's one of the key
benefits to this view.
