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.

stat.sql

column value format 999,999,999

select
   name,
   value
from
   v$sysstat
where
   name like 'table%';

Here is the output from this simple script. Next we see the accumulated totals since database start time:

NAME                                                             VALUE
---------------------------------------------------------------- ----------
table scans (short tables)                                       71,718
table scans (long tables)                                         1,965
table scans (rowid ranges)                                           33
table scans (cache partitions)                                        0
table scans (direct read)                                             0
table scan rows gotten                                        4,544,406
table scan blocks gotten                                        131,723
table fetch by rowid                                            172,131
table fetch continued row                                         1,165
 

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 multiple blocks.

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 always reparsed.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.