Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Fix unnecessary full-table scans

Oracle Tips by Burleson Consulting

January 29, 2010

While not all large-table full-table scans are problematic, a large-table full-table scan is a common symptom of a SQL execution  problem.  Large-table full-table scans in an explain plan (TABLE ACCESS FULL) should always be examined to verify that it is not due to a database problem, such as a missing index.

Also see Avoid full table scan tips  

Unnecessary large-table full-table scans "full-table scans"  (FTS) are an important symptom of sub-optimal SQL and cause unnecessary I/O that can drag down an entire database.

The first step in validating a full-table scan is to evaluate the SQL based on the number of rows returned by the query and your own guess about the number of trips to the data blocks that would be needed to fetch the rows.

Oracle says that if the query returns less than 40 percent of the table rows in an ordered table or seven percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan, but in reality there is no fixed number because it depends on many factors like the db_block_size and db_file_multiblock_read_count.

This decision is partly based on the index key value described by clustering_factor  in the dba_indexes view. However, it is not a simple process.

The choice of a full table scan vs. index access as the "best" access plan for a SQL statement depends on many factors.  The most common cause of unnecessary full-table scans is a optimizer_mode that favors full-table scans (like all_rows) or a missing index, especially a function-based indexes.

These factors are listed in their order of importance:

  • Sub-optimal optimizer_modeThe all_rows mode minimizes computing resources and favors full-table scans, while the first_rows_n mode favors index access.
  • Missing indexesMissing indexes, (especially missing function-based indexes) will cause in unnecessary large-table full-table scans.
  • Bad CBO statisticsBad/stale optimizer statistics (dbms_stats) will cause Oracle to mis-judge the execution plan for a SQL query.
  • Missing CPU and disk statisticsMissing system statistics (dbms_stats.gather_system_stats) will prevent the optimizer from making a smart decision about invoking multi-block I/O.
  • HistogramsFor skewed data columns, missing column histograms will influence the choice of index vs. full-table scan access.
  • Index clusteringThe physical row  order on the data blocks is important to know when choosing an index, and the clustering of the table rows to the desired index (as displayed in dba_indexes clustering factor).
  • Sub-optimal setting for db_file_multiblock_read_count:  The optimization of db_file_multiblock_read_count has been automated in 10g release 2 and beyond to prevent errors.
  • Sub-optimal setting for optimizer_index_cost_adj:  This is a throttle that makes index access more attractive to the optimizer, and lowering the value of optimizer_index_cost_adj will cause the SQL optimizer to favor indexes. WARNING - DO NOT adjust this parameter until you have examined other "root cause" issues like bad metadata statistics.
  • Sub-optimal setting for optimizer_index_caching:  The optimizer_index_caching parameter is set by the DBA to help the optimizer know, on average, how much of an index resides inside the data buffer.  The optimizer_index_caching parameter is a percentage parameter with valid values between zero and 100.
  • Parallel query:  Turning on Oracle parallel query (OPQ) will tell the optimizer that full-scans are less expensive, and it may change the execution plans for hundreds of SQL statements.  If you are on an SMP server with dozens of CPU’s a full-table scan can be parallelized for faster execution.  However, you should never read a data block unless it is needed to get rows for your SQL result set.  Remember, parallel query only effects full-table scans, and it has no effect when performing index access.
  • Block Size:  The number of rows that resides on each data block influences the choice between a full-table scan of an index scan.  This is especially true when you are using RAID with a smaller stripe size, and reading a table with a 2k block size would take significantly more I/O and reading a table with a 32k block size.

The most common tuning tool for addressing unnecessary full-table scans is the addition of indexes, especially function-based indexes.
 
The decision about removing a full-table scan should be based on a careful examination of the amount of logical I/O (consistent gets) of the index scan versus the costs of the full-table scan. 

This decision should be made while factoring in the multi-block reads and possible parallel full-table scan execution. In some cases, an unnecessary full-table scan can be forced to use an index.  This is done by adding an index hint to the SQL statement.

 

If you are looking to enhance your Oracle tuning skills, you may enjoy Rampant TechPress – Oracle Tuning: The Definitive Reference by Donald K. Burleson , with over 900 pages of BC's favorite tuning tips & scripts. 

 You can buy it directly from the publisher and get instant access to the code depot of Oracle tuning scripts.



 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.