SGA Statistics for SQL
Oracle Tips by Burleson Consulting
There are many ways to get an idea of the
amount of SQL activity against a specific table. The easiest and
fastest method is to run a query against the v$systat view
and see the accumulated values since instance start-up time. The
following script gives interesting information on the execution of
SQL statements on tables.
column value format 999,999,999
name like 'table%';
Here is the output from this simple script. Next we
see the accumulated totals since database start time:
table scans (short tables)
(long tables) 1,965
table scans (rowid ranges)
table scans (cache partitions)
table scans (direct
table scan rows
table scan blocks
table fetch by
table fetch continued row
While these system-wide statistics do not provide the
details about the individual SQL statements, they do provide some
important insight into the SQL performance of the entire database.
Let’s look at these individual metrics.
Table Scans (short tables) This is the
number of full-table scans performed on small tables. It is
optimal to perform full-table scans on short tables rather than
using indexes and to place these small tables in the KEEP buffer
pool. Note that Table Scans (long tables) plus Table Scans (short
tables) is equal to the number of full-table scans.
Table Scans (long tables) This is the
total number of full-table scans performed on large tables. These
should be carefully evaluated to see if the full-table scan can be
removed by adding an index, or if the query speed might be
improved by invoking Oracle parallel query (OPQ).
Table Scan Rows Gotten This is the
number of rows scanned during all full-table scans.
Table Scan Blocks Gotten This is the
number of blocks received via table scans.
Table Fetch by ROWID This is usually
the number of rows that were accessed using an index, normally
with nested loop joins.
Table Fetch by Continued Row This is
the number of rows that are chained to another block. However,
there are several anomalies in this metric, and a high value here
may not necessarily indicate chained rows. Of course, this value
will also be high if tables contain large objects with LOB, BLOB,
or CLOB datatypes, since these rows will commonly exceed the
database block size, thereby forcing the row to chain onto
Again, this data will give you a general impression
about the presence of offensive SQL statements within the library
cache. Next, let’s take a close look into the library cache and see
how we can get details about individual SQL statements.
Inside the Library Cache
The library cache is arguably the most
important area of the SGA. The shared SQL areas and the PL/SQL areas
reside in the library cache, and this is the true center of activity
within the Oracle database. As I mentioned, the activity of SQL
within the library cache is critical to the performance of Oracle. I
already discussed the use of cursor_sharing to make SQL
reusable, but there are some other types of SQL statements that are
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.