Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

Explaining SQL from the library cache

I published an important article in Oracle Magazine in December 2000.  In this article, I described a  script called access.sql script to grab all of the SQL in the library cache and stores it in a table called sqltemp. From this table, all of the SQL is explained into a single plan table. This plan table is then queried to produce the report that follows.

The Full-table scan report is the most valuable report from the script. Next we see all of the SQL statements that performed full-table scans, and the number of times that a full-table scan was performed. Also note the C and K columns. The C column indicates if an Oracle7 table is cached, and the K column indicates whether the Oracle8 table is assigned to the KEEP pool. As you will recall, small tables with full-table scans should be placed in the KEEP pool.

Mon Jan 29                                                     page    1
                          full table scans and counts
                Note that "C" indicates in the table is cached.
OWNER          NAME                      NUM_ROWS  C K   BLOCKS  NBR_FTS
-------------- ------------------------  --------- - - -------- --------
SYS            DUAL                                N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PROFILE             N K        2   16,178
DONALD         PAGE                      3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                        434 N          8    7,355
DONALD         PAGE_IMAGE                   18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                    476 N K      192    2,087
DONALD         PRINT_PAGE_RANGE                 10 N K       32      874
ARM                JANET_BOOKS                  20 N          8       64
PERFSTAT     STATS$TAB_STATS                       N         65       10

In the preceding report, you see several huge tables that are performing full-table scans. For tables that have less than 200 blocks and are doing legitimate full-table scans, we will want to place these in the KEEP pool.  For large tables, you want to extract the SQL and see if the full-table scan can be replaced with an index range scan by adding an index.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.