Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

The Index Range Scan Report Oracle Tips by Burleson

Here 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 Feb 29                                                   page    1
                          Index range scans and counts

OWNER     TABLE_NAME           INDEX_NAME      TBL_BLOCKS    NBR_SCANS
--------- -------------------- ------------- ------------ ------------
DONALD    ANNO_HIGHLIGHT       HL_PAGE_USER_I          16        7,975
DONALD    ANNO_STICKY          ST_PAGE_USER_I           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_USE         192          210
ARSD      JANET_BOOK_RANGES    ROV_BK_RNG_BOO           8          170
PERFSTAT  STATS$SYSSTAT        STATS$SYSSTAT          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 Feb 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 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 blocks of SQL code that have the ORDER BY clause.

Mon Feb 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

WARNING: Do not confuse the index full scan execution plan with the fast full-index scan. The index full scan reads each index node in SORTED order, while the fast full-index scan is used to retrieve table rows from the index in UNSORTED order.

Let’s make sure you know the differences between an index full scan and a fast full-index scan:

Table 1: The Types of Full-Index Execution Plans

Index full scan Oracle will choose an index full scan when the CBO statistics that indicate that a full-index scan is going to be more efficient than a full-table scan and a sort of the result set. The full-index scan is normally invoked when the CBO determines that a query will return numerous rows in index order, and a full-table scan and sort option may cause a disk sort to the TEMP tablespace.

Fast full-index scan This execution plan is invoked when a index contains all of the values required to satisfy the query and table access is not required. The fast full-index scan execution plan will read the entire index with multi-block reads (using db_file_multiblock_read_count) and return the rows in unsorted order. In Oracle8i, fast full-index scans are available by default in the CBO, while in Oracle8 you must set the fast_full_scan_enabled initialization parameter. In Oracle7, you must set the v733_plans_enabled initialization parameter. You can force a fast full-index scan with the index_fss hint.

To see how the CBO evaluates a query for a full-index scan, let’s take a simple example. 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_multiblock_read_count initialization 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 index order by invoking the full-index scan by reading the rows via the index, thus avoiding a sort.

Now that we have reviewed the use of extraction tools for the library cache, let’s take a look at using third-party GUI tools to locate SQL statements for tuning.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.