An execution plan is a step-by-step explanation of how a database engine will execute the query. It outlines the sequence of operations, the indexes that will be used, and the methods for joining tables.
The primay purpose of an explain plan is to give you insight into the query’ performance. By anaalyzing the explain plan, you can see where the database might run efficiently or where it might enclunter performance bottlenecks.
🎯Taxonomy of Explain Plan:
Execution Plan (Access Path): The path followed by the database to execute a query.
Cost: An estimate of the resources (like CPU and I/O) needed to perform a query.
Operation: The specific database action, such as reading from a table or performing an index scan.
Rows: The estimated number of rows that each operation will handle.
Filter: Conditions that rows must meet to proceed to the next step in the query processing.
Join Method: The type of join used, such as nested loop or hash join.
Bytes: Provides an estimate of the amount of data that will be processed by each operation.
🎯Running EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT last_name FROM employees;
EXPLAIN PLAN
SET STATEMENT_ID = ‘st1’ FOR
SELECT last_name FROM employees;
EXPLAIN PLAN
INTO my_plan_table
FOR
SELECT last_name FROM employees;
EXPLAIN PLAN
SET STATEMENT_ID = ‘st1’
INTO my_plan_table
FOR
SELECT last_name FROM employees;
Displaying PLAN_TABLE Output
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
SELEC vT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(‘MY_PLAN_TABLE’, ‘st1’,’TYPICAL’));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
🎯Explain Plan: Purpose & Views
🟦Purpose:
Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table.
This statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted.
The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and data types as this table. The common name of this script is UTLXPLAN.SQL.
Get Cloud Evolving Data’s stories in your inbox
Join Medium for free to get updates from this writer.
🟦Oracle Database provides information on cached cursors through several dynamic performance views:
For information on the work areas used by SQL cursors, query V$SQL_WORKAREA.
For information on the execution plan for a cached cursor, query V$SQL_PLAN.
For execution statistics at each step or operation of an execution plan of cached cursors (for example, number of produced rows, number of blocks read), query V$SQL_PLAN_STATISTICS.
For a selective precomputed join of the preceding three views, query V$SQL_PLAN_STATISTICS_ALL.
Execution statistics at each step or operation of an execution plan of cached cursors are displayed in V$SQL_PLAN_MONITOR if the statement execution is monitored. You can force monitoring using the MONITOR hint.
🟦Difference between Explain Plan and Execution Plan:
To avoid any possible confusion, let us clearly define EXPLAIN PLAN vs EXECUTION PLAN in Oracle. Both are just sets of instructions for SQL statement that tell the database how to access and combine data to produce the required output.
As mentioned earlier, EXPLAIN PLAN is basically what the Oracle optimizer is planning to do. However, the plan does not always work as expected. Sometimes you have to change and modify it on the fly so that it fits a particular situation. Here is when the EXECUTION PLAN comes in: it is an actual set of steps you end up taking. To sum up, EXPLAIN PLAN predicts how Oracle will process your query, and EXECUTION PLAN describes the steps it actually took.
🎯Optimizer Purpose & CBO
🟦Optimizer:
The Optimizer is one of the most fascinating components of the Oracle Database, since it is essential to the processing of every SQL statement. The Optimizer determines the most efficient execution plan for each SQL statement based on the structure of the query, the available statistical information about the underlying objects, and all the relevant optimizer and execution features.
🟦Purpose of the Optimizer:
The optimizer attempts to generate the most optimal execution plan for a SQL statement.
The optimizer choose the plan with the Database Buffer Cache among all considered candidate plans. The optimizer uses available statistics to calculate cost. For a specific query in a given environment, the cost computation accounts for factors of query execution such as I/O, CPU, and communication.
🟦Cost-Based Optimization:
Query optimization is the overall process of choosing the most efficient means of executing a SQL statement. SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, & process.
The database optimizes each SQL statement based on statistics collected about the accessed data. The optimizer determines the optimal plan for a SQL statement by examining multiple access methods, such as full table scan or index scans, different join methods such as nested loops and hash joins, different join orders, and possible transformations.
For a given query and environment, the optimizer assigns a relative numerical cost to each step of a possible plan, and then factors these values together to generate an overall cost estimate for the plan. After calculating the costs of alternative plans, the optimizer chooses the plan with the lowest cost estimate. For this reason, the optimizer is sometimes called the cost-based optimizer (CBO) to contrast it with the legacy rule-based optimizer (RBO).
🎯Components of Optimizer:
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries. It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, and partitioning.
The explain plan is by far the most useful tool at our disposal when it comes to investigating why the Optimizer makes the decisions it makes. By breaking down the explain plan and reviewing the four key elements of: cardinality estimations, access methods, join methods, and join orders; you can determine if the execution plan is the best available plan.
🟦Components of Optimizer:
After a query parse then it is handled by the optimizer. It has three components:
Query Transformer: The query transformer transforms our query into a better one.The Oracle tries to find the best query that returns the same result but in a better performing way.
Estimator: It optimizes our queries by their costs. The cost is a combination of the CPU usage, Memory Usage and the Disc Usage of our query.
Plan Generator: The plan generator calculates various plans for our query by checking different access paths, join methods and join orders.
It generates a plan, handles it to the estimator to estimate its cost, and creates another plan, and this goes on like that until all the possible execution plans are created.
🎯How Many Access Paths are available to the Optimizer?
Oracle supports nine different Access Methods or Paths
Access Paths: How Oracle gets data
The optimizer generates the best execution plans for our queries.
Optimizer verifies all available access paths by factoring in information based on statistics for the tables or indexes accessed by the SQL statement.
