> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/oracle/query-processing-and-execution/execution-plan.md). # Execution Plan ## About An **Execution Plan** in Oracle describes the sequence of operations the Oracle optimizer will perform to execute a SQL statement. It includes steps such as table access, index usage, joins, filters, and sort operations. The plan reflects: * The **order** in which Oracle accesses objects * The **methods** used (e.g., full scan, index scan) * The **cost** associated with each operation * The **estimated number of rows** processed at each step It is generated by the **Cost-Based Optimizer (CBO)** based on object statistics, available indexes, and query structure. ## **Why Execution Plans Matter** Understanding the execution plan is critical for: * Identifying performance issues * Determining whether indexes are used * Detecting unnecessary full table scans or sorts * Choosing better join orders or access paths * Verifying if query rewrites (e.g. subquery to join) are effective ## **How to Generate Execution Plans** #### a. `EXPLAIN PLAN` Command ```sql EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ``` #### b. `AUTOTRACE` in SQL\*Plus or SQL Developer ```sql SET AUTOTRACE ON SELECT * FROM employees WHERE department_id = 10; ``` #### c. `DBMS_XPLAN.DISPLAY_CURSOR` for Real Execution To view the actual plan after running a query: ```sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST')); ``` Use this for getting **real runtime statistics**, not just estimates. ## Understanding Execution Plan Output ### **1. Hierarchical Step IDs (ID and PARENT\_ID)** * Each row in the execution plan represents an **operation**, assigned an **ID**. * The plan is structured in a **hierarchical tree**, where parent-child relationships define the sequence of operations. * **Leaf nodes** perform actual data access (e.g., table scan), while **higher nodes** represent combinations (e.g., joins, sorts). * The **right-most** leaf operations are executed first. Example structure: ``` | Id | Operation | Object | |----|------------------------|---------------| | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL | EMPLOYEES | ``` * Step 2 runs first (full scan), then step 1 aggregates, then step 0 returns the result. ### **2. Operation** The `OPERATION` column describes the type of action Oracle performs at that step. This could be data access, a join, filtering, aggregation, or transformation. Common operations include:
Operation TypeMeaning
TABLE ACCESS FULLFull table scan
INDEX RANGE SCANReads index entries within a range
INDEX UNIQUE SCANReads a single index entry for a unique key
NESTED LOOPSOuter loop for each row, executes inner plan (best for small sets)
HASH JOINUses hashing to join two large row sets (requires memory)
MERGE JOINJoins two sorted inputs (typically sorted on join keys)
SORT ORDER BYSorts rows for ORDER BY clause
FILTERApplies WHERE clause filters or correlated subquery logic
VIEWRepresents a logical view being materialized or queried
### **3. Object (TABLE / INDEX NAME)** The `OBJECT_NAME` column shows the name of the table, index, or view involved in the operation. Important when: * Verifying that the correct **index** is being used. * Ensuring that Oracle is not accessing **unexpected objects** (e.g., a different table due to synonyms or views). * Debugging plans involving **materialized views**, **temporary tables**, or **inline views**. ### **4. Access Predicates vs Filter Predicates** Execution plans sometimes show two types of filtering conditions: * **Access Predicates**: Conditions that **limit the rows retrieved** from a table or index. * These directly **influence index usage**. * Example: `WHERE emp_id = 101` when an index exists on `emp_id`. * **Filter Predicates**: Conditions applied **after** data has been fetched. * These may not prevent rows from being read but filter them afterward. * Example: `WHERE salary > 10000` after a full scan. Access predicates are more efficient because they **reduce data access at the source**, while filter predicates are post-processing. ### **5. Estimated Rows (`Rows`) and Selectivity** * The `Rows` column shows the **estimated number of rows** Oracle expects to process at each step. * This is based on **table statistics** and **column histograms**. * Low selectivity (e.g., a column with only a few distinct values) will result in a high row estimate. * Large errors in row estimates may indicate **stale statistics**, **data skew**, or **missing histograms**, leading to poor plan choices. Understanding the expected row counts helps in: * Validating whether joins will be efficient * Judging if filters are being pushed properly * Ensuring the right join method (nested loop vs hash join) is selected ### **6. Cost** The `Cost` column is a **relative number** representing Oracle’s estimate of how expensive a step is. * The **optimizer calculates cost based on I/O, CPU usage, and memory consumption**, not elapsed time. * **Lower cost** usually implies a better plan, but it's not an absolute measure. * Cost is useful for **comparing two plans**, not for predicting runtime. * High-cost operations are typically full scans, sorts, or joins on large tables. ### **7. Bytes** * Represents the **estimated volume of data** processed at that step (in bytes). * Calculated as: `Estimated Rows * Row Size`. * Helps determine **memory usage** and potential **spill to disk** for operations like sorts or hash joins. ### **8. Cardinality Propagation** Cardinality is the **row count estimation passed between steps**. For example: * If a filter reduces a table of 1 million rows to 10,000 rows, that estimate is passed to the next operation (e.g., join). * Join methods are selected based on **combined cardinalities** of input datasets. Incorrect cardinality estimates can: * Lead to **bad join orders** * Force Oracle to choose a **nested loop** when a **hash join** is better * Cause **excessive memory usage or sorting** ### **9. Join Methods** Three main join methods are commonly used:
Join MethodWhen It's Used
Nested LoopsSmall outer input, fast access to inner rows via index
Hash JoinLarge unsorted data sets, memory-intensive, parallel-friendly
Merge JoinBoth inputs are sorted on join keys
Choosing the wrong join method due to bad row estimates often leads to **performance bottlenecks**. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/oracle/query-processing-and-execution/execution-plan.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.