Oracle 12c性能调整-针对全局临时表删除查询

Oracle性能专家,

我的任务是调整一个运行时间超过18小时的sql beast,具体取决于它试图从全局临时表中删除的行数。表上定义了一个索引,但优化器正在执行哈希连接-以下是表的详细信息和表的解释计划结果-

    DELETE FROM 
       T1
    WHERE ROWID IN
    (
        SELECT ROWID FROM 
        (
            SELECT 
              ROWID,
              ROW_NUMBER() OVER (PARTITION BY A,B,C,D ORDER BY C,D) DUP
              FROM T1
              WHERE FLAG1 = 0
        )
        WHERE DUP > 1
    );

  COMMIT;

表定义如下所示-

CREATE GLOBAL TEMPORARY TABLE "T1"      
   (    
A   VARCHAR2(50 BYTE), 
B   NUMBER(10,0), 
C   VARCHAR2(20 BYTE), 
D   NUMBER, 
A1  FLOAT(126), 
B1  FLOAT(126), 
C1  FLOAT(126), 
D1  FLOAT(126), 
A2  NUMBER, 
B2  NUMBER, 
C2  FLOAT(126), 
D2  FLOAT(126), 
A3  FLOAT(126), 
B3  FLOAT(126), 
C3  FLOAT(126), 
D3  FLOAT(126), 
A4  FLOAT(126), 
B4  FLOAT(126), 
FLAG1   NUMBER
) ON COMMIT PRESERVE ROWS ;     
CREATE INDEX T1IDX ON T1 ("A", "B", "C", "D") ; 

解释计划结果如下-

Query Plan                                 Rows        Rowsource Time
DELETE STATEMENT   Cost = 3936614
DELETE  T1                                              1109
       NESTED LOOPS                         1           1
         VIEW  VW_NSO_1                     220M        0
           SORT UNIQUE                      1           163
             VIEW                           220M        2
               WINDOW SORT                  220M        355
                 TABLE ACCESS FULL T1       220M        94
  TABLE ACCESS BY USER ROWID T1             1           313

还有一点很重要,当上述查询运行的时间比平时长时,我们还会遇到偶尔的ORA-01652和ORA-30036(分别是Undo和Temp表空间扩展错误)。过去几周,我们一直在增加临时空间,以暂时缓解错误。我在这里添加表空间信息-

  Tablespace Name               SizeinMB        FreeMB
  ----------------              ---------       --------
  T1_Sp1_DATA_TS                 3712           180.88
  T1_PE1_INDEX_TS                1              0.94
  SYSAUX                         1160           60.06
  T1_SYS_BLOB_TS                 525            81.13
  T1_SIF_EXPORT_TS               5              4
  T1_SIF_TS                      1              0.69
  T1_FL1_INDEX_TS                3590           173.06
  Staging_DATA_TS                1436           165.63
  T1_FLR_pf1_TS                  2219           238
  T1_Sp1_dv1_TS                  1004           2.75
  T1_Sp1_pf1_TS                  5868           8.75
  T1_SYS_DATA_TS                 34             3.63
  T1_SYS_el1_TS                  159            11.88
  T1_Sp1_INDEX_TS                5785           309.69
  T1_e1_INDEX_TS                 5              4
  USERS                          66740          21538.06
  T1_FL1_DATA_TS                 1932           95.38
  T1_BLOB_TS                     12415          591.44
  T1_Sp1_Fx1_TS                  3249           215.75
  T1_ST1_INDEX_TS                2              0.94
  T1_SIF_INDEX_TS                2              0.38
  SYSTEM                         405            7.19
  T1_FL1_Fx1_TS                  6475           351.63
  T1_ST1_DATA_TS                 1              0.13
  T1_SA_INDEX_TS                 5              4
  T1_NET_DATA_TS                 13             0.19
  T1_Staging_DATA_TS             872404.9375    176406.69
  T1_FL1_sc1_TS                  4071           254.63
  T1_SA_DATA_TS                  5              4
  T1_NET_BLOB_TS                 26757          1291.38
  T1_NET_INDEX_TS                57             3.63
  T1_SYS_INDEX_TS                33             4.88
  T1_Sp1_ps1_TS                  2129           103.75
  T1_e1_DATA_TS                  5              4
  T1_SA_BLOB_TS                  5              4
  T1_SI1_BLOB_TS                 2              0.25
  T1_PE1_DATA_TS                 1              0.94
  TEMP                           196605.96875   

我想知道优化查询以使其运行更快的最佳方法是什么?我将尝试强制删除索引提示或NLJ提示,看看是否有帮助,但如果你们有更好的想法,我将不胜感激。

这是Oracle 12c,我们有所有全局临时表的会话级统计数据。我仍在学习12c的一些特性,所以不确定如何在这张表中显示大部分会话级别的统计信息。

谢谢 布伦顿