Database Reference
In-Depth Information
Projection information lists the subset of columns accessed by the query for each step in
the execution plan. The word projection is an academic term from relational algebra. For the
second iteration, let's change the optimizer environment and see how this affects the execu-
tion plan.
SQL> ALTER SESSION SET optimizer_index_cost_adj=10000;
Session altered.
SQL> SELECT e.last_name, e.first_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id
AND e.salary > :sal
AND e.hire_date > to_date(:hired, 'dd.mm.yy')
AND d.department_name=:dname;
LAST_NAME FIRST_NAME SALARY DEPARTMENT_NAME
------------------------- -------------------- ---------- ---------------
Weiss Matthew 8000 Shipping
Fripp Adam 8200 Shipping
Vollman Shanta 6500 Shipping
Mourgos Kevin 5800 Shipping
Note that the child cursor number reported in the next code example is 1. This is due to the
different plan, which results from a changed optimizer environment. The plan hash value has
also changed, while the
SQL_ID
has remained the same. It is undocumented which parameter
changes force the optimizer to consider a new plan. When tuning a statement, it is wise to add
a unique comment to the statement text before each execution. This forces a hard parse and
ensures that the optimizer considers all aspects of the current environment, which may include
updated object and system statistics as well as modified initialization parameters.
Due to caching, a single disk read occurred when the statement was run the second time
(column “Reads”). This time, I have split the execution plan into three parts for better readability,
since memory usage statistics from the hash join made it even wider.
SQL> SELECT *
FROM table (DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALL ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b70r97ta66g1j, child number 1
-------------------------------------
SELECT e.last_name, e.first_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id
AND e.salary > :sal
AND e.hire_date > to_date(:hired, 'dd.mm.yy')
AND d.department_name=:dname
Plan hash value: 2052257371
