我开始学习Oracle查询优化。我有一个关于跟踪文件的问题。
例如，跟踪文件中有一个查询计划(很抱歉，如果没有格式化，您不必阅读它，只需注意SYS_TEMP_0FD9D6611_3CFB8B(：
TEMP TABLE TRANSFORMATION (cr=4,525,645 pr=1 pw=1 time=6.1696s)
LOAD AS SELECT (cr=2 pr=0 pw=1 time=0.0006s)
TABLE ACCESS FULL TABLE3 (cr=2 pr=0 pw=0 time=0.0000s cost=2 size=12 card=4)
SORT AGGREGATE (cr=4,525,643 pr=1 pw=0 time=6.1689s)
NESTED LOOPS OUTER (cr=4,525,643 pr=1 pw=0 time=6.8775s cost=4,522,314 size=42,943,800 card=2,260,200)
TABLE ACCESS FULL TABLE1 (cr=5,241 pr=0 pw=0 time=0.6861s cost=1,429 size=13,561,200 card=2,260,200)
VIEW (cr=4,520,402 pr=1 pw=0 time=5.2771s cost=2 size=13 card=1)
TABLE ACCESS FULL SYS_TEMP_0FD9D6616_3CFB8B (cr=4,520,402 pr=1 pw=0 time=4.1430s cost=2 size=12 card=4)
当请求具有物化块时，例如：
with tmp as
(select /*+ materialize */ * from table t
where t.val = 'A')
然后在查询计划中的跟踪文件中，出现以下行：
STAT #398394272 id=12 cnt=4 pid=11 pos=1 obj=-40016367 op='TABLE ACCESS FULL SYS_TEMP_0FD9D6611_3CFB8B (cr=4 pr=1 pw=0 time=324 us cost=2 size=12 card=4)'
当请求中的物化块只有一个时，就很容易理解它具体指的是上面的with块。
但是当有很多块with时，从跟踪文件中不清楚object SYS_TEMP_0FD9D6611_3CFB8B属于哪个块。
我试图通过名称object SYS_TEMP_0FD9D6611_3CFB8B和对象编号obj=-40016367进行匹配，但从文件中仍然不清楚是什么。
你能告诉我如何确定代词指的是哪个材料块吗？
在我工作中处理的问题中，有10-40个这样的材料块。正因为如此，在请求计划中很容易混淆。
或者我一般都做错了什么？我的最终目标是了解ETL过程中的请求挂起的位置。如果我试图分析错误的东西，建议如何最好地做。
执行计划通常是一个很好的指标。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")