oracle sys_b_0,oracle - 跟踪文件oracle。 如何将名称'SYS_TEMP_0FD9D6616_3CFB8B'（或对象ID = -40016362）与特定的实现块（wit...

执行计划通常是一个很好的指标。 LOAD AS SELECT将向您显示创建临时表的阶段，然后对临时表的访问将出现在计划的后面。 通过查看“ LOAD AS SELECT”下的“操作”，希望可以将其绑定到查询中的SQL文本

SQL> create table t as select * from dba_Objects;

Table created.

SQL>

SQL> set autotrace traceonly explain

SQL> with

2 t1 as

3 ( select /*+ materialize */ owner, count(*) c1

4 from t

5 group by owner ),

6 t2 as

7 ( select /*+ materialize */ owner, max(object_id) c2

8 from t

9 group by owner ),

10 t3 as

11 ( select /*+ materialize */ t1.owner, c1,c2

12 from t1,t2

13 where t1.owner = t2.owner )

14 select * from t3;

Execution Plan

----------------------------------------------------------

Plan hash value: 4120770359

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 37 | 3404 |

| 1 | TEMP TABLE TRANSFORMATION | | | |

| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9DA551_C656D5E3 | | |

| 3 | HASH GROUP BY | | 37 | 222 |

| 4 | TABLE ACCESS FULL | T | 82667 | 484K|

| 5 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9DA552_C656D5E3 | | |

| 6 | HASH GROUP BY | | 37 | 407 |

| 7 | TABLE ACCESS FULL | T | 82667 | 888K|

| 8 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9DA553_C656D5E3 | | |

|* 9 | HASH JOIN | | 37 | 5846 |

| 10 | VIEW | | 37 | 2923 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9DA551_C656D5E3 | 37 | 222 |

| 12 | VIEW | | 37 | 2923 |

| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9DA552_C656D5E3 | 37 | 407 |

| 14 | VIEW | | 37 | 3404 |

| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9DA553_C656D5E3 | 37 | 1184 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

9 - access("T1"."OWNER"="T2"."OWNER")