Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Explain Plan Jump to:  
Category: >> Oracle PL/SQL >> Explain Plan Bookmark and Share

<< lastnext >>

Snippet Name: Explain Plan

Description: EXPLAIN PLAN output shows how Oracle executes SQL statements and displays the execution plans chosen by the Oracle optimizer.

Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle's tasks 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 you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 07th, 2009

Explain Plan Syntax 
 
 
EXPLAIN PLAN FOR
SELECT object_name
FROM user_objects
WHERE UPPER(object_type) LIKE 'TAB%';
 
SELECT * FROM TABLE(DBMS_XPLAN.display);
 
Output looks LIKE:
--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |    88 |    4  (25)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |    4  (25)|
|  2 |   NESTED LOOPS OUTER    |              |  999 |  7992 |    3   (0)|
|  3 |    INDEX FAST FULL SCAN | user_objects |  999 |  3996 |    3   (0)|
--------------------------------------------------------------------------
/*
Fields  within the plan table
The key fields within the plan table are operation, option, 
object_name, id, and parent_id. The pair operation and 
object_name define what operation would be done on (or with) 
object_name. If an operation has an id which other operations 
have as parent_id, it means the other operations feed their 
result to the parent.
 
Possible values for operation are:
 
    * DELETE STATEMENT
    * INSERT STATEMENT
    * SELECT STATEMENT
    * UPDATE STATEMENT
    * AND-EQUAL
    * CONNECT BY
    * CONCATENATION
    * COUNT
    * DOMAIN INDEX
    * FILTER
    * FIRST ROW
    * FOR UPDATE
    * HASH JOIN
    * INDEX
    * INLIST ITERATOR
    * INTERSECTION
    * MERGE JOIN
    * MINUS
    * NESTED LOOPS
    * PARTITION,
    * REMOTE
    * SEQUENCE
    * SORT
    * TABLE ACCESS
    * UNION
    * VIEW 
*/


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 152 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?