 |
|
The Full-Table Scan Report
Oracle Tips by Burleson Consulting |
This is the most valuable report of all. 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
ARSD 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. The larger table
full-table scans should also be investigated, and the legitimate
large-table full-table scans should be parallelized with the
alter table parallel degree nn command.
An Oracle database invokes a large-table
full-table scan when it cannot service a query through indexes. If
you can identify large tables that experience excessive full-table
scans, you can take appropriate action to add indexes. This is
especially important when you migrate from Oracle7 to Oracle8,
because Oracle8 offers indexes that have built-in functions. Another
cause of a full-table scan is when the cost-based optimizer decides
that a full-table scan will be faster than an index range scan. This
occurs most commonly with small tables, which are ideal for caching
in Oracle7 or placing in the KEEP pool in Oracle8. This full-table
scan report is critical for two types of SQL tuning:
-
For a small-table full-table scan, cache the
table by using the alter table xxx cache command
(where xxx = table name), which will put the table rows at the
most recently used end of the data buffer, thereby reducing disk
I/O for the table. (Note that in Oracle8 you should place cached
tables in the KEEP pool.)
-
For a large-table full-table scan, you can
investigate the SQL statements to see if the use of indexes would
eliminate the full-table scan. Again, the original source for all
the SQL statements is in the SQLTEMP table. I will talk about the
process of finding and explaining the individual SQL statements in
the next section.
Next, we see the index usage reports. These
index reports are critical for the following areas of Oracle 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. All indexes will appear in this report, so it is
easy to locate those indexes that are not being used.
-
Row resequencing 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 row length. For details on row resequencing
techniques, see Chapter 10.
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.
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_IN_IDX
16 7,975
DONALD ANNO_STICKY ST_PAGE_USER_IN_IDX
8 7,296
DONALD PAGE ISBN_SEQ_IDX
120 3,859
DONALD TOC_ENTRY ISBN_TOC_SEQ_IDX
40 2,830
DONALD PRINT_HISTORY PH_KEY_IDX
32 1,836
DONALD SUBSCRIPTION SUBSC_ISBN_USER_IDX
192 210
ARSD JANET_BOOK_RANGES ROV_BK_RNG_BOOK_ID_
8 170
PERFSTAT STATS$SYSSTAT STATS$SYSSTAT_PK
845 32
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
Although index full scans are usually faster
than disk sorts, you can use one of several
init.ora
parameters to make index full scans even faster. These are the
V77_plans_enabled
parameters in Oracle7, which were renamed to
fast_full_scan_enabled in Oracle8. You can use a
fast full scan as an alternative to a full-table scan when an index
contains all the columns needed for a query. A fast index full scan
is faster than a regular index full scan because it uses multi-block
I/O as defined by the
db_file_multiblock_read_count parameter. It can
also accept a parallel hint in order to invoke a parallel query, in
the same fashion as a full-table scan. The Oracle database engine
commonly uses index full scans to avoid sorting. Say you have a
customer table with an index on the cust_nbr column. The
database could service the SQL command
select * from
customer order by cust_nbr; in two ways:
-
It could perform a full-table scan and then
sort the result set. The full-table scan could be performed very
quickly with
db_file_muiltiblock_read_count init.ora
parameter set, or the table access could be
parallelized by using a parallel hint. However, the result
set must then be sorted in the TEMP tablespace.
-
It
could obtain the rows in customer number order by reading the rows
via the index, thus avoiding a sort.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
