BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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:

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter