An explain plan in Oracle is a detailed step by step set of instructions that an SQL statement makes in order to run the SQL query. Typically, Oracle will use an optimizer strategy, and will select the explain plan with the lowest cost associated with running it. As a developer, sometimes we might want to view an explain plan to help optimize a query, by modifying our SQL, or adding new indexes to a particular column to help decrease the cost of the best explain plan.
As someone who is not that familiar with explain plans himself, the purpose of the post is to simply introduce the concepts of explain plans, how to run them, and a very quick overview on how to interpret some of the data contained in them.
An Example Query
Lets consider the simple problem of getting some salary information from the HR sample dataset. Our task is to write an SQL query that returns the job title, min, max, and average salary for that job, and how many people have it.
An example query might look like this:
select job_title as "Job Title", min(salary) as "Minimum Salary", max(salary) as "Maximum Salary", avg(salary) as "Average Salary", count(e.job_id) as "# Jobs" from jobs j, employees e where e.job_id = j.job_id group by job_title
The results we get from this would then be:
The query works, so lets run an explain plan to show what steps Oracle actually makes to return these results.
Running an Explain Plan
To run one, we need to prefix our query with “explain plan for”:
explain plan for select job_title as "Job Title", min(salary) as "Minimum Salary", max(salary) as "Maximum Salary", avg(salary) as "Average Salary", count(e.job_id) as "# Jobs" from jobs j, employees e where e.job_id = j.job_id group by job_title
We don’t actually get a result directly from this, instead Oracle simply says “Explained”. What happens here is Oracle stores the plan in an internal table that can be accessed using the dbms_xplan package.
To view our explain plan, you then need to run the dbms_xplan.display function. This function takes in three parameters: the plan table name, the statement_id, and the format.
Run the following query:
select plan_table_output from table(dbms_xplan.display('plan_table', null, 'basic'));
The output of this call can be seen in the figure below:
What this call has done is generate a basic output of the individual steps Oracle performs in order to build up and return the results as seen earlier. Each tabbed indent in the results table indicates another level in a tree of query statements, so what we actually see here is the following tree diagram:
Reading bottom-to-top, and left-to-right, we see that tables employees and jobs are joined, where a groupby function is then ran on the join, and finally a select statement at the top to return the columns that we want.
Altering the Explain Plan
If we were to change the optimization strategy for Oracle, we can actually get a different explain plan:
alter session set optimizer_mode = RULE; explain plan for select job_title as "Job Title", min(salary) as "Minimum Salary", max(salary) as "Maximum Salary", avg(salary) as "Average Salary", count(e.job_id) as "# Jobs" from jobs j, employees e where e.job_id = j.job_id group by job_title ; select plan_table_output from table(dbms_xplan.display('plan_table', null, 'typical'));
The outcome of this query then displays the following:
As we can see, rather than performing hashed joins, Oracle instead uses Nested Loops, modifying how it affects the query.
Currently, our Explain Plan is only showing the basic details, which is the order of functions that Oracle calls to build up our returned data. Lets switch back to using a cost based optimizer, as explain plans can actually give us statistics on the cost of each action.
alter session set optimizer_mode = ALL_ROWS; explain plan for select job_title as "Job Title", min(salary) as "Minimum Salary", max(salary) as "Maximum Salary", avg(salary) as "Average Salary", count(e.job_id) as "# Jobs" from jobs j, employees e where e.job_id = j.job_id group by job_title ; select plan_table_output from table(dbms_xplan.display('plan_table', null, 'typical'));
As we can see, reverting back to a cost based optimizer gives us the same steps when we originally ran our query. However, by using ‘typical’ as our format parameter, we get a whole bunch of information back, including the cost of the plan.
In oracle, Cost based optimizers run the plan with the lowest cost, where the total cost of a plan is indicated on the first row of the results. In this case, this particular plan returns a cost of 7.
In addition to cost, there are several other concepts that are required to underestand an explain plan.
The cardinality is the estimated number of rows each operation returns. For example, the cardinality of accessing the Employees table is 107, while for jobs its only 19. In explain plans, cardinality is returned in the Rows column.
Access method shows how data is accessed from a particular table. For example, a query might need to read all rows in a table, filtering on a where clause, while another might just return a row based on a primary key.
Take the following three queries:
select * from employees where salary = ‘24000’;
select * from employees where first_name = ‘Steven’ and last_name = ‘King’;
select * from employees where employee_id = ‘100’;
They all return the same result when executed on the HR schema.
However, if we generate explain plans for each, they use a different type of access method to retrieve the data:
The first explain plan shows that for employees, a TABLE ACCESS FULL operation was required to find our result as we ran it against a column that was not indexed.
The second uses an INDEX RANGE SCAN as our where statement contained two indexed columns.
The third uses an INDEX UNIQUE SCAN, as the column used was a primary key.
Each access method also has a lower cost associated with it as well, highlighting how indexed columns, and primary keys, can decrease the cost of an explain plan. Typically if your query is running a number of TABLE ACCESS FULL on different tables, you may want to consider indexes to help reduce the cost.
Join methods indicates the algorithm that was used to perform the join operation on two tables. As we saw earlier, changing our optimizer changed our join method where originally we were doing a hash join, but after specifying a RULE optimization strategy, our query plan changed to Nested Loop. There are a number of different methods each with their positives and drawbacks.
Join types indicate the type of join that was performed (e.g. inner, outer, left, right etc). Most joins are inner joins, and thus when Oracle just has “Join”, it actually means inner join. Again, certain join types will be more expensive than other. For example, typically a Left Join is more expensive to run than an Inner Join. Considering your join strategy in your query might help to reduce the overall cost of the plan.
This is the order in which joins occur in. For instance if we were joining three tables together, two might be joined in one call, then the third will be a join of that outcome. Join order is specified by the cost of each join, which is directly influenced by the cardinality of it.
For example, lets say we had table A, B, and C. Joining all tables together should result in 10 rows. Joining A + B results in 80 rows, then + C = 10 rows, while joining A + C results in 40 rows + B = 10 rows. In this example, the join order in oracle will probably be (A + C) + B, as fewer scans are actually required when performing the second join (40 rather than 80).
This blog post has helped introduce some of the very basic concepts behind explain plans, including what they are, how to run them, and how to understand them. While there are far more in-depth materials out there on the web, hopefully this post will be of use to those who just want a quick introduction to them. ]
For further reading, I would recommend the Oracle White Paper on them, as it what this blog post was mostly based on: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf