A query plan (or query execution plan) is an ordered set of steps used to access information in a SQL relational database management system. This is a specific case of the relational model concept of access plans.

Since SQL is declarative, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a query is submitted to the database, the query optimizer evaluates all of the different possible plans for executing the query and returns what it considers the best alternative.

Query plans are a very important tool in performance tuning of a query or database.

= Database tuning=

Reviewing the query plan can present opportunities for new Index (database) or changes to existing indexes. It can also show that the database is not properly taking advantage of existing indexes (see query optimizer).

= Query tuning =

The query optimizer will not always choose the best query plan for a given query. In some databases the query plan can be reviewed, problems found, and then the query optimizer given hints on how to improve it. In other databases alternatives to express the same query (other queries that return the same results) can be tried.

Some databases like Oracle provide a Plan table for query tuning.This plan table will return the cost and time for executing a Query. In oracle there are 2 optimization techniques 1) CBO or Cost Based Optimization 2) RBO or Rule Based Optimization

The RBO is slowly being deprecated. For CBO to be used, All the tables in the database must be analyzed. To analyze a table, a package dbms_statistics can be made use of.

The others methods for query optimization include 1) SQL Trace 2) Oracle Trace 3) TKPROF

= See Also =