 |
|
The Full-Table Scan Report
Oracle Tips by Burleson Consulting |
As I noted in Chapter 6, this is one of the
most valuable SQL tuning reports. Here we see all of the SQL
statements that performed full-table scans, and the number of times
that a full-table scan was performed. We also see the number of rows
and blocks in each table, and a flag that indicates whether the
table is cached or in the KEEP pool.
We will go into greater detail on checking
full-table scans on Chapter 10, but the main focus of this report is
to ensure that the small tables are in the KEEP pool, and that the
queries against the large tables are not “false” full-table scans
that could be changed into an index range scan.
Mon Jan 29
page 1
full table scans and counts
Note that "C" indicates in the table is cached.
“K” indicates the table is in the KEEP Pool
OWNER NAME
NUM_ROWS C K BLOCKS NBR_FTS
-------------- -------------------- ------------ - - --------
--------
SYS
DUAL
N 2
97,237
SYSTEM
SQLPLUS_PRODUCT_PRO
N K 2 16,178
DONALD PAGER
3,450,209 N 932,120 9,999
DONALD RWU_PAGE
434 N 8
7,355
DONALD PAGER_IMAGE
18,067 N 1,104 5,368
DONALD SUBSTANT
476 N K 192 2,087
DONALD
PRINT_PAGER_RANGE
10 N K 32
874
ARSD
JANET_BOOKS
20 N 8
64
PERFSTAT STATS$TAB_STATS
N 65
10
The access_report.sql script also
provides some great statistics on index usage. Next, let’s look at
the index range scan report.
The Index Range Scan Report
Next we see the report for index range scans.
The most common method of index access in Oracle is the index range
scan. An index range scan is used when the SQL statement contains a
restrictive clause that requires a sequential range of values that
are indexes for the table. Tables with a high number of index range
scans may benefit from row-resequencing to reduce the amount of
physical disk I/O.
Mon Jan 29
page 1
Index range scans and counts
OWNER TABLE_NAME
INDEX_NAME TBL_BLOCKS
NBR_SCANS
--------- -------------------- -------------- ------------
------------
DONALD ANNO_HIGHLIGHT
HL_PAGE_USER_IX
16 7,975
DONALD ANNO_STICKY
ST_PAGE_USER_IX
8 7,296
DONALD PAGE
ISBN_SEQ_IDX
120 3,859
DONALD TOC_ENTRY
ISBN_TOC_SEQ_I
40 2,830
DONALD PRINT_HISTORY
PH_KEY_IDX
32 1,836
DONALD SUBSCRIPTION
SUBSC_ISBN_USEX 192
210
ARSD JANET_BOOK_RANGES
ROV_BK_RNG_BOO
8 170
PERFSTAT STATS$SYSSTAT
STATS$SYSSTAT_P 845
32
These index reports are critical for several areas of
Oracle SQL tuning.
-
Index usage Ensuring that
the application is actually using a new index. Remote DBAs can now obtain
empirical evidence that an index is actually being used after it
has been created. Indexes that are not being used cause additional
overhead for SQL insert and update statements and
also waste valuable disk space.
-
Row resequencing The index range scan
report is great for finding out which tables might benefit from
row resequencing. Tables that have a large amount of index range
scan activity will benefit from having the rows resequenced into
the same order as the index. Resequencing can result in a tenfold
performance improvement, depending on the average length of the
rows in the table.
The Index Unique Scan Report
Here is a report that lists index unique
scans, which occur when the Oracle database engine uses an index to
retrieve a specific row from a table. The Oracle database commonly
uses these types of “probe” accesses when it performs a JOIN and
probes another table for the JOIN key from the driving table. This
report is also useful for finding out those indexes that are used to
identify distinct table rows as opposed to indexes that are used to
fetch a range of rows.
Mon Jan 29
page 1
Index unique scans and counts
OWNER TABLE_NAME
INDEX_NAME
NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD BOOK
BOOK_ISBN
44,606
DONALD PAGE
ISBN_SEQ_IDX
39,973
DONALD BOOK
BOOK_UNIQUE_ID
6,450
DONALD ANNO_DOG_EAR
DE_PAGE_USER_IDX
5,339
DONALD TOC_ENTRY
ISBN_TOC_SEQ_IDX
5,186
DONALD PRINT_PERMISSIONS
PP_KEY_IDX
1,836
DONALD RDRUSER
USER_UNIQUE_ID_IDX
1,065
DONALD CURRENT_LOGONS
USER_LOGONS_UNIQUE_I
637
ARSD JANET_BOOKS
BOOKS_BOOK_ID_PK
54
DONALD ERROR_MESSAGE
ERROR_MSG_IDX
48
The Full-Index Scan Report
The next report shows all index full scans.
As you will recall, the Oracle optimizer will sometimes perform an
index full scan in lieu of a large sort in the TEMP tablespace. You
will commonly see full-index scans in SQL that have the order by
clause.
Mon Jan 29
page 1
Index full scans and counts
OWNER TABLE_NAME
INDEX_NAME
NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD BOOK
BOOK_ISBN
2,295
DONALD PAGE
ISBN_SEQ_IDX
744
Limitations of the access.sql Reports
The technique for generating these reports is
not as flawless as it may appear. Because the “raw” SQL statements
must be explained in order to obtain the execution plans, you may
not know the owner of the tables. One problem with native SQL is
that the table names are not always qualified with the table owner.
To ensure that all the SQL statements are completely explained, many
Remote DBAs sign on to Oracle and run the reports as the schema owner.
A future enhancement would be to issue the
following undocumented command immediately before each SQL statement
is explained so that any Oracle database user could run the reports:
ALTER
SESSION SET current_schema = ‘tableowner’;
This would change the schema owner immediately before
explaining the SQL statement.
Conclusion
Oracle provides a wealth of tools that
quickly enable you to examine the run-time details of Oracle SQL
execution. We can use the standard explain plan utility, TKPROF, or
more advanced techniques such as Oracle’s COE script and
access.sql to give us detailed information about the physical
access path to our table rows.
Next, let’s turn our attention to tools that
can be used for locating the most significant SQL statements.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
