Execution Plans - Troubleshooting Oracle Performance - page 315

The display_cursor Function
The display_cursor function returns execution plans stored in the library cache. Note that, in a Real Application
Clusters environment, it's not possible to get an execution plan stored in a remote instance. As for the display
function, the return value is an instance of the dbms_xplan_type_table collection. The function has the following
input parameters:

sql_id specifies the parent cursor whose execution plan is returned. The default value is
NULL . If the default value is used, the execution plan of the last SQL statement executed by the
current session is returned.

•

cursor_child_no specifies the child number that, along with sql_id , identifies the child
cursor whose execution plan is returned. The default value is 0. If NULL is specified, all child
cursors of the parent cursor identified by the sql_id parameter are returned.

•

format specifies which information is displayed. The same values are supported as in the
parameter format of the display function. In addition, if execution statistics are available
(in other words, if the statistics_level initialization parameter is set to all or the gather_
plan_statistics hint is specified in the SQL statement), the modifiers described in
Table 10-4 are also supported. The default value is typical .

•

■ as pointed out in Chapter 2, sometimes the sql_id and child_number columns aren't sufficient to identify
a child cursor in the v$sql view. in such a case, because of bug 14585499, and up to and including version 11.2.0.3,
the display_cursor function returns wrong data. to recognize this problem, look for the following error message in the
display_cursor function output:

Caution

An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested
number of rows

You can use the display_cursor_ora-01422.sql script to reproduce the bug.

To use the display_cursor function, the caller requires the SELECT privilege on the following dynamic
performance views: v$session , v$sql , v$sql_plan , and v$sql_plan_statistics_all . The select_catalog_role role
and the select any dictionary system privilege provide these privileges, among others.

■ the modifiers listed in table 10-4 have the side effect of removing from the output the following columns
related to the query optimizer estimations: Bytes , TempSpc , Cost (%CPU) , Time . if you want one of these columns in the
output, you have to explicitly specify it either through a primitive value or a modifier.

Note

The following example shows a query that uses the gather_plan_statistics hint to enable the generation of
the execution statistics. The display_cursor function is then instructed to display the disk I/O statistics for the last
execution. Because no physical read or writes took place, only logical reads ( Buffers ) are displayed. Here's an excerpt
of the output generated by the display_cursor.sql script:

SQL> SELECT /*+ gather_plan_statistics */ count(pad)