Execution Plans - Troubleshooting Oracle Performance - page 335

---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 | VIEW | | 1 | 14 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | 14 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 1 |
| 4 | NESTED LOOPS | | 4 | 13 |
| 5 | NESTED LOOPS | | 4 | 13 |
| 6 | RECURSIVE WITH PUMP | | 4 | 14 |
|* 7 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 13 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMP | 13 | 13 |
---------------------------------------------------------------------------------
3 - filter("MGR" IS NULL)
7 - access("EMP"."MGR"="MGR"."EMPNO")
filter("EMP"."MGR" IS NOT NULL)
Reading an execution plan containing the UNION ALL (RECURSIVE WITH) operation is the same as reading one
containing the CONNECT BY WITH FILTERING operation. As a matter of fact, the purpose of both operations is basically
the same. Just notice that the PUMP operation used in the execution plan also differs. While in the former it's called
RECURSIVE WITH PUMP , in the latter it's called CONNECT BY PUMP . In any case, the difference, for the purpose of reading
the execution plan, doesn't matter.
Divide and Conquer
In the previous sections, you saw how to read the three types of operations execution plans are composed of. All
the execution plans you've seen so far were quite easy (short). More often than not, though, you have to deal with
complex (long) execution plans. That's not because most SQL statements are complex but because it's likely that
simple SQL statements are correctly optimized by the query optimizer, and as a result, you never have to question
their performance.
The essential thing to recognize is that reading long execution plans is no different from reading short ones. All
you need is to methodically apply the rules provided in the previous sections. With them, it doesn't matter how many
lines an execution plan has. You simply proceed in the same way.
To show you how to proceed with an execution plan that's longer than a few lines, let's take a look at the
operations carried out by the execution plan shown in Figure 10-9 (Figure 10-10 shows a graphical representation
of its parent-child relationships). I'm not providing the SQL statement used to generate it intentionally. For our
purposes, you're simply not interested in the SQL statement. The execution plan, on the other hand, is the key.