Reading Explain Plan - Example 1
SQL> ED
Wrote file afiedt.buf
1 select /*+ GATHER_PLAN_STATISTICS */ department_id,count(*)
2 from employees
3 where salary <3000
4 and job_id='PU_CLERK'
5* group by department_id
SQL> /
DEPARTMENT_ID COUNT(*)
------------- ----------
30 4
SQL> SET LINESIZE 130
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
select /*+ GATHER_PLAN_STATISTICS */ department_id,count(*) from
employees where salary <3000 and job_id='PU_CLERK' group by
department_id
Plan hash value: 1062631129
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | | |
| 1 | HASH GROUP BY | | 1 | 11 | 1 |00:00:00.01 | 2 | 888K| 888K| 393K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1920 | 4 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 46729 | 5 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SALARY"<3000)
3 - access("JOB_ID"='PU_CLERK')
1. Operations 1 and 2 have a single child each (2 and 3, respectively); the execution starts with operation 3 as child feeds parent ( of course there is some exception Rules) and starts with the lowest level
2. Operation 3, scans the index EMP_JOB_IX by applying the access predicate
"JOB_ID"='PU_CLERK‘.
It found 5 rowids (see column A-Rows) from the index and passes them to its parent operation (2).
3. Operation 2 accesses the table employees through the 5 rowids passed from operation 3.
For each rowid, a row is read. Then, it applies the filter predicate "SALARY"<3000. This filter leads to the exclusion of one row. The data of the remaining 4 rows are passed to its parent operation (1).
4. Operation 1 performs a GROUP BY on the rows passed from operation 2. The resulting
set is reduced to 1. Since this is the last operation, the data is sent to the caller.
Regards
BKD
ED: Formatted
