SQL tuning tips - How to find the fastest execution plans

Times are changing, and Oracle developers now understand that it's not enough to write a SQL statement that gives the right answer.  That's only half the job;  an Oracle professional must also ensure that the SQL statement is optimized!  This is especially important because there are many "correct" ways to write a giver SQL query, all of which give the correct results, but with vastly different response time!

When you are tuning SQL for the fastest response time, there are several factors that experts examine:

- The "cost" figures in the execution plans

- The actual speed of the query (the SQL*Plus "set timing on" command")

- The number of logical I/O's required to fetch the desired rows (the "consistent gets" execution statistic

When tuning SQL, many Oracle professionals only focus on the execution plans and the associated “cost” figures.  We have already discussed why the “cost” figures in a SQL execution plan do not always reflect the fastest execution plan, and that’s another reason why it’s critical to always “set timing on” and time your query’s actual execution speed and the number of logical I/O’s (consistent gets).  

Many people analyze their execution plans, and choose the one that they believe has the best execution plan, without actually timing the SQL for execution speed.   If you do not check the time and the plan, you may end using expensive new features.  

Rampant TechPress author and Oracle ACE Laurent Schneider has this great tip for ensuring that your SQL is always optimized for minimizing I/O operations:

http://www.dba-oracle.com/t_sql_tuning_execution_statistics_gets.htm



*****************************************
How to do intelligent Oracle tuning?

After years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  To try Ion for free, see here:

www.ion-dba.com

*****************************************

Need a health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf