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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm