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










Tuning SQL Table Access

Oracle Tips by Burleson Consulting

This chapter deals with the low-level table access methods that result from running the execution plan for the SQL statement. As you know from Chapter 3, any SQL statement may have several execution plans depending on the CBO statistics, the presence and types of indexes, and the optimizer_mode. At a lower level, each execution plan translates into table access methods. These low-level table access methods determine the overall response time for the SQL statement, so it is imperative that you understand the table access methods and how you can improve the throughput for table access.

When discussing Oracle table access methods, we must always remember that Oracle has only a few ways to access any Oracle table, and that the physical access is always performed by reading a database block. There are several ways to access Oracle data blocks from SQL:

  • Full-table scan This can invoke multi-block reads and parallelism to improve access speed.

  • Index access   When using an index to retrieve a row, the index provides Oracle with the ROWID, which results in a request for a specific Oracle data block. We also see index range scans, where a range of values is retrieved from a table, and these accesses are based on a range of ROWIDs that are collected while reading the index nodes.

  • Fast full-index scan This is a form of index where Oracle reads all of the data blocks within an index using multiblock reads and parallelism. It is the index equivalent of a full-table scan.

To fully understand Oracle table access methods, you must begin by carefully examining full-table scans. The SQL optimizer manages index access within Oracle automatically, but we will examine tricks for improving SQL index access in Chapter 20. Within an individual table, we can perform numerous administrative functions to improve the speed of SQL, including row resequencing and changing the table storage parameters. This chapter will cover the following table access issues:

  • SQL tuning and full-table scans

  • Table access via indexes

  • Changing table access methods

  • Resequencing table rows for reducing disk I/O

  • Oracle storage parameters and table access performance

  • Freelist management and table access performance

When you look at tuning any Oracle SQL statement, you must first understand the ways that an Oracle table can be accessed, and the internal block access techniques. Let’s begin by examining the full-table scan.

SQL Tuning and Full-Table Scans

There are some important rules for when a SQL query should perform a full-table scan. Unnecessary large-table full-table scans cause a huge amount of unnecessary I/O and can drag down an entire database. The tuning expert first evaluates the SQL in terms of the number of rows returned by the query. If the query returns less than 40 percent of the table rows in an ordered table, or 7 percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan. The most common tuning for unnecessary full-table scans is adding indexes. Standard B-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans. The decision about removing a full-table scan should be based on a careful examination of the I/O costs of the index scan versus the costs of the full-table scan, factoring in the multi-block reads and possible parallel execution. In some cases an unnecessary full-table scan can be forced to use an index by adding an index hint to the SQL statement.

In cases where a full-table scan is the fastest access method, the tuning professional should ensure that a dedicated data buffer is available for the rows. In Oracle7, you can issue alter table xxx cache. In Oracle8 and beyond, the small table can be cached by forcing the table into the KEEP pool.

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