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
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: