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

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

Oracle SQL tuning in three simple steps

After I was chosen by Oracle Corporation to write the Oracle Press book “Oracle high-performance SQL tuning,” many readers are befuddled by the 600+ pages of detailed instructions on tuning Oracle SQL.  While this level of detail is necessary for a large detailed book, the goals of SQL tuning can be encapsulated into just a few simple points.

Let’s take a high-level look at the main goals of SQL tuning.  If you achieve these goals, you should have tuned more than 90% of your database:

  1. Remove unnecessary large-table full-table scans

This is the most important goal of all SQL tuning because unnecessary large-table full-table scans consume a high amount of disk I/O that can drag-down the performance of the whole database.  Small-table full table scans are acceptable, so long as these tables are cached in the KEEP pool, but the trick is to evaluate the number of rows returned by a large-table full-table scan to determine if an index range scan will make the query run faster and with less disk I/O.  In general, and SQL query that accesses more than 40% of the rows in a table will run faster with a full-table scan, depending on the degree of parallelism for the table and the setting for db_file_multiblock_read_count.  For those queries that return less than 40% of table rows, you should always try to add indexes and hints to remove the full-table scan and force an index range scan.

  1. Verify optimal index usage

When multiple indexes are present on a table, the Oracle SQL optimizer must choose the index with the highest selectivity to service the query.  If you are using rule-based SQL optimization, the optimizer commonly chooses a sub-optimal index to service a table access, and you job is to force te use of the most selective index.

  1. Verify optimal table join techniques

As we know, Oracle can join tables with a sort-merge join, a nested loop join, a hash join or a STAR join.  It is your job to tune the SQL joins to ensure that the fastest table join method is being used.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


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

Oracle® is the registered trademark of Oracle Corporation.