BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter