This blog entry presents a basic introduction to Oracle execution plans.
What exactly is an Oracle execution plan? An execution plan consists of the steps that the database server will take to accomplish a DML statement, e.g. SELECT, INSERT, DELETE, UPDATE, etc. The database server will make execution plans for some DDL statements as well. The steps derived from an execution plan could instruct the database server to use an index, or use memory rather than performing physical IO, etc.
There are many decisions that the Oracle server can make internally which can affect the performance of operations of such DMLs. So what is the mechanism within Oracle that facilitates this decision making and produces the execution plan? The mechanism is called the Oracle Cost Based Optimizer.
Oracle used to have two types of Optimizers, the Rule based optimizer and the Cost based Optimizer. Starting in Oracle database version 10g, the Cost based optimizer is the default type. The Cost based optimizer takes object statistics into consideration when it develops a plan, while the old Rule based optimizer went by a set of predetermined rules. As a side note, object statistics are not gathered automatically. This is typically a DBA scheduled job. Oracle does come installed with a default job to do this; however not all database objects are analyzed and this can be based on change rate of an object.
There are also features called dynamic sampling and extended statistics which affect statistic gathering and optimizer functionality, but they are beyond the scope of this blog entry. The way that the Cost based optimizer works is the preferred way to get the most optimal performance. It is important to note however, that the optimizer will not always get it right. Those scenarios are beyond the scope of this blog entry as well. When you, via ad hoc queries or via an application, submit DML requests such as a query/SELECT the optimizer needs to decide the most efficient path to accomplish this operation. To make this decision the optimizer takes many factors into account. We will list those factors later in the blog.
Example Table
In this blog we will use an example table. This is a standard heap organized table in my schema. The table structure is as follows:
When we query all rows from our sample table we will see that it contains three records/rows. A row for myself showing that I am 34 years of age, a row for Bob showing he is 30 years or age and a row for John showing he is 22 years of age.
SQL> select * from names; FNAME LNAME AGE -------------------- -------------------- ---------- justin bleistein 34 bob smith 30 john wilson 22
For Oracle to run this query, the database engine had to work out what the most efficient way to get the query result set would be? It said to itself, how can I get the query results this user is asking for with as little cost to the database server as possible from a resource perspective? At a high level the first thing the optimizer did when I submitted the query above was ask some questions about the table being queried – (This is in no particular order):
- What table is this user referencing?
- What columns in the table is this user referencing? This is known as projection.
- Is there a WHERE clause in this query?
- What is the predicate in the WHERE clause?
- How many rows are in this table?
- What is the average row length in this table?
- How many database blocks does this table use?
- What is the cardinality of this table?
- What is the selectivity of this table?
- Are any columns in this table indexed?
- What type of index does this column use?
Where does Oracle find the answer to these questions? Well it looks in many places but the three main places which are relevant to your objects are in the following data dictionary views:
- DBA_TAB_STATISTICS
- DBA_TAB_COL_STATISTICS
- DBA_TAB_MODIFICATIONS
I’ve always used a driving to the mall analogy to demonstrate how object statistics influence the optimizer’s decision as to how best to run a DML. Imagine that you, the driver of the car, is the Oracle optimizer, the many roads and routes to get to the mall are the different execution plans and the mall itself is where the result sets are located. Some of the attributes you as the driver/optimizer will have to consider when deciding which road/execution plan to utilize would be: stop signs, curbs, street, pavement condition, natural terrain, weather, any road construction activity, general traffic conditions, etc.
As you start on your journey to drive to the mall, you notice that Maple Ave. is blocked off due to road construction, so you take a detour via Grapes Ave. Once on Grapes Ave. you drive behind a person driving very, very slow. You get frustrated and make an unexpected left turn onto Main St. You travel well on Main St. with no issues. You then turn onto Cooper Blvd. Cooper Blvd.’s pavement condition is not the best and there are a lot of potholes. You are forced to drive under the speed limit to navigate these potholes safely and to avoid damaging your car. You finally arrive at the mall!
What happened during this journey? Let’s take a look at a comparison chart to bring this analogy together. We will be comparing our drive to the mall with a JOIN SQL query:
As you can see from my above analogy, the way Oracle goes about executing a DML is not that different than you driving your car somewhere. You will take certain conditions and attributes into consideration to plan your route to your destination. You will decide on a certain route due to those conditions and attributes. Those factors will cause you to drive differently and at different speeds. All of this will ultimately affect the time it will take you to arrive at your destination.
There are several ways to obtain an execution plan in Oracle. You can run the EXPLAIN PLAN statement which will not actually run the DML statement but will store a game plan, if you will, as to how the Oracle engine MIGHT want to run it in the PLAN_TABLE table. You can then run a table function query against the DISPLAY PL/SQL function which is in the DBMS_XPLAN PL/SQL package – DBMS_XPLAN.DISPLAY. In recent releases of Oracle there is a public synonym called PLAN_TABLE. The public synonym points to a global temporary table called PLAN_TABLE$. This global temporary table is owned by Oracle user SYS. It is important to note that the execution plan returned by the EXPLAIN PLAN statement may not be the actual plan that Oracle will use when it goes to execute said DML. You can also use auto trace within your session which actually executes the DML and provides some additional statistics.
You can run the DBMS_XPLAN.DISPLAY_CURSOR function to display the execution plan which has already been loaded into the Oracle SGA. These are plans of currently running DML or DML which have recently ran but have not yet been aged out of the SGA.
Finally you can run the DBMS_XPLAN.DISPLAY_AWR function to display the execution plan which has been stored in the AWR repository. Note you need to be licensed for Oracle database Enterprise Edition to use AWR.
Let’s see some examples of running an execution plan:
You can generate an execution plan in your own session for a DML. Note this DML will not actually execute. Once the plan is explained it is put in the PLAN_TABLE. When you run the query below, Oracle will display the execution plan of the last query just explained, in your session:
SQL> explain plan for select * from names; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------- Plan hash value: 4048250084 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 48 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| NAMES | 3 | 48 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- PLAN_TABLE_OUTPUT ----------------- - SQL plan baseline "SQL_PLAN_f9fqvjj5a9tcpe1466801" used for this statement 12 rows selected.
To view the execution plan of DML which is either currently executing or hasn’t been aged out of the shared memory pool of the SGA yet.
SQL> select * from table(dbms_xplan.display_cursor('dh9x4ajkauwn2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dh9x4ajkauwn2, child number 0
-------------------------------------
select * from names
Plan hash value: 783237774
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100) | |
| 1 | TABLE ACCESS FULL| NAMES | 2 | 30 | 3 (0) | 00:00:01 |
13 rows selected.
You will have to obtain the 13 character SQL ID by querying the SQL_ID column of many possible dynamic performance views and data dictionary views. You can get the SQL ID from the PREV_SQL_ID column of the V$SESSION view or from the SQL_ID column of the V$SQL view.
To view the execution plan of DML which has been collected from the dynamic performance views and data dictionary views by AWR during its last snapshot you can run the following. Note, you may have to modify the TOPNSQL parameter of the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS PL/SQL procedure in order to control how much SQL an AWR snapshot will grab. It’s important to note that by default displaying DML’s from the AWR will not display predicates.
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> select * from table(dbms_xplan.display_awr('dh9x4ajkauwn2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dh9x4ajkauwn2
--------------------
select * from names
Plan hash value: 783237774
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100) | |
| 1 | TABLE ACCESS FULL| NAMES | 2 | 30 | 3 (0) | 00:00:01 |
13 rows selected.
In conclusion, we can see that Oracle just doesn’t submit a query and collect the data for display. Everything Oracle does, it will have performance in mind. It will try to work out the fastest way to do something and it will use as much information as possible to determine what the fastest way might me. It is also important to note that other proprietary database management systems such as IBM DB2, Microsoft SQL server and open source ones as well have their own versions of a query optimizer. The optimizer is one of the components that separate a database management system from a flat file database, or comma delimited file, spreadsheet, etc.
