Oracle Database - Execution/Query Plan
About
The output from the optimizer is an execution plan (also known as query plan of simply plan) that describes an optimum method of execution. The plans shows the combination of the steps Oracle Database uses to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement.
You can get the execution plan through various mechanisms:
- e.g. using the "EXPLAIN PLAN utility, select" from the cursor cache,
- or use the advanced workload repository.
You can influence the execution plan (e.g. the query optimizer) by
- setting the optimizer goal (either throughput or response time)
- gathering representative statistics
- using hints to instruct the optimizer about how a statement should be executed.
- changing some initialization parameters
The query plan might not be the same from one version of Oracle Database to the next. In recent versions, the optimizer might make different decisions, because better information is available.
Articles Related
Reviewing plans and tuning
It's important when reviewing and tuning queries to emulate what your application does. You cannot tune a query with literals and expect a query that contains bind variables to have the same performance characteristics.
Tuning this sql :
SELECT * FROM some_table WHERE COLUMN = 55
is different of tuning this one :
SELECT * FROM some_table WHERE COLUMN = :bind_variable
Performance Tool kit
In single-user mode :
In multi-user mode :
