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 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.


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