Explain Plan
Encyclopedia
An explain plan is a feature provided by the majority of relational database
s. When the database engine is provided with an SQL
statement it computes the access path
used to execute the query
. This output is called the Query Execution Plan
and is used for troubleshooting performance issues in a database.
is done by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.
When processing an EXPLAIN PLAN, Oracle analyzes the statement and fills a special table with the Execution plan for that statement.
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s. When the database engine is provided with an SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
statement it computes the access path
Path (computing)
A path, the general form of a filename or of a directory name, specifies a unique location in a file system. A path points to a file system location by following the directory tree hierarchy expressed in a string of characters in which path components, separated by a delimiting character, represent...
used to execute the query
Information retrieval
Information retrieval is the area of study concerned with searching for documents, for information within documents, and for metadata about documents, as well as that of searching structured storage, relational databases, and the World Wide Web...
. This output is called the Query Execution Plan
Query plan
A query plan is an ordered set of steps used to access or modify information in a SQL relational database management system. This is a specific case of the relational model concept of access plans....
and is used for troubleshooting performance issues in a database.
Example with Oracle
Reading or writing data in an Oracle databaseOracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
is done by issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan. If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.
When processing an EXPLAIN PLAN, Oracle analyzes the statement and fills a special table with the Execution plan for that statement.