Database Reference
In-Depth Information
There are three noworkload statistics parameters.
CPUSPEEDNW (noworkload CPU speed)
IOSEEKTIM (I/O seek time in ms)
IOTFRSPEED (I/O transfer speed in KB/s)
The unit of CPU speed is not MHz, but rather some undocumented measure proprietary
to Oracle Corporation Clearly, the output of the CBO is an execution plan. But this is not the
only output from the optimizer. Additionally, it provides the following items:
Estimated number of rows in the statement's results set
Estimated number of bytes processed
Query block names
Filter predicates
Access predicates
Column projection information
Values of peeked bind variables
An outline, i.e., a set of hints that forces the chosen execution plan (requires Oracle10
g
or newer)
Outline data reference query block names and are an ideal basis for testing alternative
plans with hints. The information in the preceding list may also be retrieved with
DBMS_XPLAN.
DISPLAY_CURSOR
starting with Oracle10
g
. However, undocumented format options are required
for retrieving the outline and peeked binds. Use the format option OUTLINE to include the
outline and PEEKED_BINDS to retrieve peeked bind variable values. There's also an undocu-
mented format option ADVANCED that also includes an outline, but not peeked bind variable
values. Here is an example:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null,
'OUTLINE PEEKED_BINDS -PROJECTION -PREDICATE -BYTES'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9w4xfcb47qfdn, child number 0
-------------------------------------
SELECT e.last_name, e.first_name, d.department_name FROM hr.employees e,
hr.departments d WHERE e.department_id=d.department_id AND
d.department_id=:dept_id AND e.employee_id=:emp_id AND first_name=:fn
Plan hash value: 4225575861
