Database Reference
In-Depth Information
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"<=19 AND "N">=6)
If you use the
EXPLAIN PLAN
statement and the
display
function when the
current_schema
session parameter is
set to a schema that owns a plan table that has the default name, you have to add the schema name to both the
INTO
clause of the
EXPLAIN PLAN
statement and the
table_name
parameter. Failing to do so causes the
display
function to
raise an error message. The following example illustrates:
SQL> ALTER SESSION SET current_schema = franco;
SQL> EXPLAIN PLAN FOR SELECT * FROM t;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE
SQL> EXPLAIN PLAN
INTO franco.plan_table
FOR SELECT * FROM t;
SQL> SELECT * FROM table(dbms_xplan.display(table_name=>'
franco.plan_table
'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
It's also possible with the
display
function to query a plan table that has a structure based on the
v$sql_plan_
statistics_all
view. This feature is useful when you want to persist information that, by design, is only temporarly
available in the library cache. Since such a plan table contains additional information, when querying it through the
display
function, the
format
parameter supports the additional modifiers described in the next section, specifically
in Table
10-4
. The following example shows how you could take advantage of this feature to persist information about
the last SQL statement to be executed:
SQL> SELECT /*+ gather_plan_statistics */ count(*) FROM t;
COUNT(*)
----------
1000