Execution plan

Execution plan= detailed steps neccesary to execute a SQL statement.
Order of the operators and their implementation is decided by the query optimizer

Display Explain plan

Meerdere manieren. meest bekende EXPLAIN PLAN command of V$SQL_PLAN

bijv
SQL> EXPLAIN PLAN FOR
2 SELECT srvr_id
3 FROM servers s
4 WHERE EXISTS (
5 SELECT srvr_id
6 FROM serv_inst i
7 WHERE s.srvr_id = i.srvr_id);
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2840037858
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
Morgan's Library - www.morganslibrary.org
How To Read and Interpret an Explain Plan
| 0 | SELECT STATEMENT | | 11 | 286 | 4 (25)| 00:00:01 |
| 1 |   NESTED LOOPS | | 11 | 286 | 4 (25)| 00:00:01 |
| 2 |       SORT UNIQUE | | 999 | 12987 | 3 (0)| 00:00:01 |
| 3 |           INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 12987 | 3 (0)| 00:00:01 |
|* 4 |     INDEX UNIQUE SCAN | PK_SERVERS | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")
Note
-----


Cost
Cost is an internal unit. Only for plan comparisons. Optimizer selects the execution plan with the lowest cost.  Cost represtents the estimated resource usage for that plan. ( IO, CPU and network resources)


Cardinality
estimated number of rows that will be returned by each operation. Optimizer bepaalt dit op basis van complexe set formules, waarbij gekeken wordt naar Statistics.
Cardinality kan afwijzken zelfs als statistics zijn up to date