 |
|
The Index Range
Scan Report
Oracle Tips by Burleson Consulting |
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 ANNI_HIGHLIGHT
HL_PAGE_USER_IN_IDX
16 7,975
DONALD ANNI_STICKY
ST_PAGE_USER_IN_IDX
8 7,296
DONALD PAGEER
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_I
8 170
PERFSTAT STATS$SYSSTAT
STATS$SYSSTAT_PK
845 32
12 rows selected.
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 PAGEER
ISBN_SEQ_IDX
39,973
DONALD BOOK
BOOK_UNIQUE_ID
6,450
DONALD ANNI_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 full-index scans.
As you will recall, the Oracle optimizer will sometimes perform an
full-index 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 PAGER
ISBN_SEQ_IDX
744
The Oracle database engine commonly uses
full-index 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 the db_file_multiblock_read_count 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.
Limitations of the access.sql Reports
The techniques for generating these reports
are not as flawless as they 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 = ‘table_owner’;
This would change the schema owner immediately before
explaining the SQL statement.
Now that we have covered the SQL reporting,
let’s move on to look at how the individual SQL statements are
extracted and explained.
Conclusion
The purpose of this chapter is to introduce
you to the benefits and challenges of Oracle SQL tuning and
introduce some of the tools we will be using to easily tune SQL
statements. As we proceed through this text, we will be looking at
detailed techniques for ensuring that your database performs at
optimal levels.
Oracle SQL tuning is one of the most
rewarding activities in Oracle database tuning. There is nothing
like the feeling of tuning a SQL statement and taking it from a
two-hour execution time to a 20-second execution time. With the
proper approach and diligence, the Oracle Remote DBA can become the hero
and dramatically improve the performance of the entire database.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
