Database Reference
In-Depth Information
The example consists of running a two-way join on the tables
EMPLOYEES
and
DEPARTMENTS
with varying optimizer parameter settings. The first iteration runs in the default optimizer
environment and results in a nested loops join, a full scan on
DEPARTMENTS
, and an index access
to
EMPLOYEES
. The smaller table
DEPARTMENTS
is used as the driving table in the join.
For the second iteration,
OPTIMIZER_INDEX_COST_ADJ
was set to its maximum value of 10000,
provoking full table scans by tagging index accesses with a higher cost. This latter setting results
in a hash join and full table scans on both tables. Let's walk through the example step by step.
First, the buffer cache is flushed
3
and
STATISTICS_LEVEL
is changed.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> ALTER SESSION SET statistics_level=all;
Session altered.
Next, we declare three SQL*Plus bind variables to mimic the bind variables
:B1
,
:B2
, and
:B3
that we saw in the SQL trace file reproduced in the previous section and assign the bind
variable values reported in the trace file. Since SQL*Plus does not support variables with data
type
DATE
,
VARCHAR2
is used for the variable
HIRED
and the function
TO_DATE
is applied to convert
from
VARCHAR2
to
DATE
.
SQL> VARIABLE sal NUMBER
SQL> VARIABLE hired VARCHAR2(10)
SQL> VARIABLE dname VARCHAR2(64)
SQL> EXEC :sal :=4999.99; :hired:='31.12.1995'; :dname:='Shipping'
PL/SQL procedure successfully completed.
Except for more readable bind variable names than :
B
n
and the addition of
TO_DATE
, the
statement text found in the trace file is used.
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
Next,
DBMS_XPLAN
is called to retrieve the execution plan of the last statement executed. I
have split the execution plan output in two parts to make it more readable. The uppercase “E”
in the columns “E-Time”, “E-Bytes”, and “E- Rows” is short for estimated. Note that this first
iteration reports on child cursor number 0.
3.
Flushing the buffer cache requires at least release Oracle10
g
.
