Explain the Explain Plan: Access Methods

At the end of last year, I began a blog series on reading and interpreting Oracle execution plans. In this week’s post, I will tackle the aspect of execution plans that I get the most questions about, Access Methods.

What are Oracle Access Paths or Methods?

Access Methods or Access Paths are the techniques used by Oracle to access the data needed to answer a query. Access Methods can be divided into two categories; data accessed via a table scan or index access. You can find the individual Access Methods chosen by the Optimizer are visible in the Operations column of an Execution table.

How Many Access Paths are available to the Optimizer?

Oracle supports nine different Access Methods today, as shown in the image below.

When will the Optimizer choose each of these methods, and what can I do to influence that decision?

To clearly explain how each of the Access Methods works and when it will be chosen, I’ve created a short video.

What if I don’t get the Access Method I want?

If the Access Method you see in an execution plan is not what you expect, check the cardinality estimates for that object are correct, and the join order allows the access method you desire. Remember, Optimizer transformations (the rewriting of your query to open up additional access methods) can also greatly impact the Access Method.

SQL Tuning Tips and Tricks – Part 5 of the Optimizer Workshop

Teaches you to apply the techniques discussed in the previous sections to help diagnose and correct a number of problematic SQL statements.

The final part of the SQL Tuning workshop focuses on applying the techniques discussed in the previous sections to help diagnose and correct a number of problematic SQL statements and shows how you can use SQL Plan Management or a SQL Patch to influence an execution plan.

How to read a Parallel Execution Plan in Oracle

The volume of data being stored in databases has grown exponentially in recent years. So too has the need to rapidly generate value or business insights from that data.

Parallel execution is the key to processing large volumes of diverse data quickly, as it subdivides complex tasks into a number of small tasks allowing multiple processes to accomplish a single complex task.

However, the use of parallelism can complicate the execution plan displayed. Oracle not only displays the operations needed to complete the SQL statement in the plan but all of the communication steps between the parallel server processes.

So, how should you go about interpreting a parallel execution plan?

In the video below, I give you a step by step guide on how to read parallel plans and what additional information you can glean from them!

What are Query Block Names and how to find them

I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.

What are query blocks?

query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.

The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.

Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.

Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.

How do I find the name of a query block?

To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.

The new section will list the Query Block name for each of the lines in the plan.

SQL_ID 4c8bfsduxhyht, child NUMBER 0
SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno 
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818
| Id  | Operation	   | Name | ROWS  | Bytes | Cost (%CPU)| TIME	  |
|   0 | SELECT STATEMENT   |	  |	  |	  |	5 (100)|	  |
|*  1 |  HASH JOIN SEMI    |	  |	5 |   205 |	5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   280 |	2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |	1 |    21 |	2   (0)| 00:00:01 |
Query Block Name / Object Alias (IDENTIFIED BY operation id):
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / E@SEL$1
3 - SEL$5DA710D3 / D@SEL$2
Predicate Information (IDENTIFIED BY operation id):
1 - access("E"."DEPTNO"="D"."DEPTNO")

As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.

Continue reading “What are Query Block Names and how to find them”