Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

The Goals of SQL Tuning
Oracle Tips by Burleson
 

Oracle SQL tuning is a phenomenally complex subject, and we will begin with a high-level statement of the goals of SQL tuning and get into detail in later chapters.

The Guidelines for SQL Tuning

The cardinal rules of SQL tuning are very straightforward:

1.      There exists only one optimal execution plan for any SQL statement. It is the job of the Oracle DBA to find that execution plan.

2.      Because SQL comes to the Oracle database from external programs, the Oracle DBA must continually monitor the library cache for untuned SQL.

3.      A properly tuned SQL statement will always have the fastest wall-clock execution time. This is usually associated with the execution plan that has the least amount of table I/O.

Now that we have stated the goals, let’s examine some of the specific goals of SQL tuning. There are some general guidelines that all Oracle DBAs must follow in order to improve the performance of their systems. The goals of SQL tuning are simple:

  • Remove unnecessary large-table full-table scans Unnecessary 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 un-ordered table, the query can be tuned to use an index in lieu of the full-table scan. The most common tuning remedy 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 converted to an index scan by adding an index hint to the SQL statement.

  • Cache small-table full-table scans full-table scan is the fastest In cases where a 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 to into the KEEP pool.

  • Verify optimal index usage improving the speed of This is especially important for queries. Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index. This also includes the use of bitmapped and function-based indexes.

These goals may seem deceptively simple, but these tasks make up 90 percent of SQL tuning, and they don’t require a thorough understanding of the internals of Oracle SQL. Next let’s look at a series of tools that we can use to tune SQL statements.


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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter