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
 
Search WWH ::




Custom Search