News Stay informed about the latest enterprise technology news and product updates.

Lessons Learned: Optimizing SQL

Part of the Lessons Learned series, this week's lesson covers SQL query optimization.

Welcome to's Lessons Learned series. Each week you will get a mini-lesson pertaining to a highly...

specific database administration, application development, data warehousing and business intelligence, or E-Business Suite topic. The lessons are comprised of information already posted on the site in the form of expert responses, tips, articles or white papers written by our panel of gurus. At the end of each month, you will be tested on what you've learned. E-mail us your specific lesson requests today.

Go to the Lessons Learned library for additional lessons and quizzes.

   How does Oracle work with SQL?
   What is SQL tuning?
   When do I use explain plan?
   Which is better: Index or full table scan?
   What SQL tuning tools are available in 10g?

  How does Oracle work with SQL?
[ Return to Table of Contents ]

You have to understand a bit about Oracle's optimizer and how it chooses an execution path/plan for your SQL statement. The optimizer looks at various scenarios for how to accomplish what your SQL statement is requesting and costs each one. This cost is basically a number that the optimizer uses to determine how expensive a particular execution path is. The higher the number, the less likely the optimizer is to choose that path for executing your statement. Out of all possible scenarios it reviews, the optimizer will choose the path which has the lowest cost.

What you give the optimizer to work with is the key. If you pass the optimizer a poorly constructed SQL statement, then you handicap your efforts right from the start.

Excerpted from Karen Morton's "Optimizing SQL."

  What is SQL tuning?
[ Return to Table of Contents ]

SQL tuning is the process of ensuring that the SQL statements that an application will issue will run in the fastest possible time. Just like there may be ten different ways for you to drive from work to your house, there may be ten different ways to execute a query. Normally, you are in a hurry to get home so you take the fastest way. Once in a while, you may find a new way that is even faster. You didn't consider this route before because it was not very intuitive. SQL tuning is similar. Tuning SQL statements is finding the fastest route to answer your question, even if that route is not very intuitive.

Excerpted from Brian Peasland's "What is SQL tuning?"

  When do I use explain plan?
[ Return to Table of Contents ]

Explain plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster.

Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed. This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. The command is of the form:

EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement;

You need to use a statement_id and then give your SQL statement.

Excerpted from Brian Peasland's "What is explain plan in Oracle?"

  Which is better: Index or full table scan?
[ Return to Table of Contents ]

Full tables scans don't always equate to "bad queries." If the optimizer is choosing an FTS over using an index, it thinks that it has chosen the optimal plan for your query. Now, if you are seeing a difference in the response time under each scenario, here are a couple of things to look at that will effect how the optimizer makes decisions.

There are a few init.ora parameters that you may want to look at modifying which will effect the choices the optimizer makes. They are:

1. optimizer_index_caching: you can change this from its default of 0 to something higher, let's say 50. This will cause the optimizer to assume that it will find 50% of index blocks in the cache.
2. optimizer_index_cost_adj: you can change this from its default of 100 to something lower, let's say 50. This lets you tune optimizer behavior for access path selection to be more or less index friendly, that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default makes FTS and index use equal.
3. optimizer_mode: set this to FIRST_ROWS. That way, the optimizer will attempt to select a plan that will minimize response time.

Excerpted from Karen Morton's "Are left outer joins messing up the optimizer?"


  What SQL tuning tools are available in 10g?
[ Return to Table of Contents ]

Automatic Workload Repository (AWR) defaults to a collection interval every 30 minutes and collects data that is the foundation for all of the other self-tuning features. AWR is very much like STATSPACK, especially the level-5 STATSPACK collection mechanism where top SQL is collected every hour, based on your rolling thresholds for high-use SQL. In addition to the SQL, AWR collects detailed run-time statistics on the top SQL (disk reads, executions, consistent gets) and uses this information to adjust the rolling collection threshold. This technique ensures that AWR always collects the most resource-intensive SQL.

Excerpted from "Don Burleson's favorite Oracle10g new features."

Go to the Lessons Learned library for additional lessons and quizzes.



Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.