跟踪文件oracle。如何将名称'SYS_TEMP_0FD9D6616_3CFB8B'(或对象ID = -40016362)与特定的实现块(with as)匹配

我开始学习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,643 pr=1 pw=0 time=6.1689s)
        nesTED LOOPS OUTER (cr=4,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,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,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流程中请求的挂起位置。如果我尝试分析错误,建议如何做最好。

YYM819364374 回答:跟踪文件oracle。如何将名称'SYS_TEMP_0FD9D6616_3CFB8B'(或对象ID = -40016362)与特定的实现块(with as)匹配

执行计划通常是一个很好的指标。 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")
本文链接:https://www.f2er.com/3169874.html

大家都在问