Database Reference
In-Depth Information
PLAN_HASH_VALUE PLAN_HASH_2
--------------- --------------------
1144362 3993509485
SQL>
SQL> with test as (select /*+ materialize */ 'X' dummy from dual)
2 select /* 2 */ count(*) from dual d, test t where
3 d.dummy = t.dummy;
COUNT(*)
----------
1
SQL>
SQL> select plan_table_output from table (sys.dbms_xplan.display_cursor('','','LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------
SQL_ID 1sqtx80jwwfp9, child number 0
-------------------------------------
with test as (select /*+ materialize */ 'X' dummy from dual) select /*
2 */ count(*) from dual d, test t where d.dummy = t.dummy
Plan hash value: 4038214887
----------------------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | | | 6 (100)|
|
| 1 | TEMP TABLE TRANSFORMATION | | | | |
|
| 2 | LOAD AS SELECT | | | | |
|
| 3 | FAST DUAL | | 1 | | 2 (0)|
00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 5 | |
|
| 5 | NESTED LOOPS | | 1 | 5 | 4 (0)|
00:00:01 |
| 6 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)|
00:00:01 |
|* 7 | VIEW | | 1 | 3 | 2 (0)|
00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_FFEA2B | 1 | 3 | 2 (0)|
00:00:01 |
----------------------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("D"."DUMMY"="T"."DUMMY")
26 Zeilen ausgewählt.
SQL>
SQL> select plan_hash_value, extractvalue(xmltype(other_xml), '/*/info[@type =
"plan_hash_2"]') plan_hash_2 from v$sql_plan where sql_id='1sqtx80jwwfp9' and other_xml is not
null;
PLAN_HASH_VALUE PLAN_HASH_2
--------------- --------------------
4038214887 3993509485
