一个跑不出结果的视图的优化

作为银行乙方开发DBA，职责之一就是每个月都给银行各个系统出一份性能优化报告

连续给某个系统优化了好几条坑爹的大SQL之后，接口人今天找到我说 有一个视图从创建开始

和这个视图相关的SQL从来没有跑出来过。最长的一次跑了24小时没出结果。视图单跑也是2个小时跑不出来

用户很是抱怨，甚至现在都没有人愿意去点这个页面了，基本要放弃了，问我能不能拯救一下

视图源码SQL如下：

CREATE OR REPLACE VIEW VW_RPT_ ASselect ct 检查类型 ,cc          错误类型      ,org_l1            一级行编码      ,org_l1_desc       一级行说明      ,org_l2            二级行编码      ,org_l2_desc        二级行说明      ,leaf_code   责任中心编码      ,leaf_desc 责任中心说明      ,occur_bal         发生额      from(With val_yxtd As  (  select /*+ parallel(aa,4) +*/ *  from smg_wrk_relation aa  where exists  (select 1 from wrk_acct_mgr a  inner join instrument_table b  on  substr(a.acct_no,1,16) = substr(b.cust_acct_no,1,16)  where  a.mgr_code = aa.orgin_code1  and a.org_l2 = aa.orgin_code2  )  and  aa.relation_type = 'cc_mgr'),ledger_occur As (select nvl(l.opt_txt10,cost_center) cost_center,occur_bal from ( Select cost_center        ,Sum(occur_bal) occur_bal  From   ledger_stat  Where  src_id = 'SG'  Group  By cost_center) m  left join smg_def_leves l --表中维护总账成本落在金融工具表中经营主体 对应opt_txt10字段维护on l.leaf_code=m.cost_centerand l.field_id=8 and l.opt_txt10 is not null),--459089.36,org_l2 As (Select Distinct level_01_code org_l1                 ,level_01_desc org_l1_desc                 ,level_02_code org_l2                 ,level_02_desc org_l2_desc  From   dim_tree_3)Select '责任中心为营销团队但无有效账户' ct      ,'没有维护营销团队与客户经理关系的数据' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,           0) occur_balFrom   vw_cost_center c      ,org_l2         e      ,ledger_occur   bWhere  c.opt_txt1 = 'MK'And    c.opt_txt20 = e.org_l2And    c.leaf_code = b.cost_center(+)And    Not Exists (Select 1        From   smg_wrk_relation f        Where  c.leaf_code = f.relation_code        And    f.relation_type = 'cc_mgr')Union AllSelect '责任中心为营销团队但无有效账户' ct      ,'维护营销团队与客户经理关系，但仍不存在有效账户的数据' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,            0) occur_balFrom   vw_cost_center c      ,org_l2         e      ,ledger_occur   bWhere  c.opt_txt1 = 'MK' And c.opt_txt20 = e.org_l2 And c.leaf_code = b.cost_center(+) And Exists (Select 1  From   smg_wrk_relation f  Where  c.leaf_code = f.relation_code  And    f.relation_type = 'cc_mgr') And Not Exists (Select 1  From   val_yxtd f  Where  c.leaf_code = f.relation_code)Union AllSelect '责任中心表示为支行但是无对应账户' ct      ,'责任中心表示为支行但是无对应账户' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,             0) occur_balFrom   vw_cost_center c      ,ledger_occur   b      ,org_l2         eWhere  Not Exists (Select 1  From   instrument_table d  Where  c.leaf_code = d.org_unit_id) And c.opt_txt1 = 'BR' And c.opt_txt20 = e.org_l2 And c.leaf_code = b.cost_center)order by org_l1,org_l2,ct,cc

先用我定制的这个SQL语句来获取PLAN中所有的表的信息（使用中遇到问题请留言告知，谢谢）：

WITH X AS (SELECT /*+ MATERIALIZE */ OBJECT_OWNER, OBJECT_NAME, LISTAGG(OBJECT_ALIAS, ' | ') WITHIN GROUP(ORDER BY OBJECT_NAME) OBJECT_ALIAS, OBJECT_TYPE  FROM (SELECT OBJECT_OWNER,               OBJECT_NAME,               CASE                 WHEN OBJECT_TYPE LIKE 'TABLE%' THEN                  SUBSTR(OBJECT_ALIAS, 1, INSTR(OBJECT_ALIAS, '@') - 1)                 ELSE                  'NOALIAS'               END OBJECT_ALIAS,               OBJECT_TYPE,               TIMESTAMP,               MAX(TIMESTAMP) OVER(ORDER BY TIMESTAMP DESC) AS MAX_TIME          FROM PLAN_TABLE         WHERE OBJECT_NAME IS NOT NULL) WHERE TIMESTAMP = MAX_TIME GROUP BY OBJECT_NAME, OBJECT_OWNER, OBJECT_TYPE),Z AS (SELECT B.OWNER,         X.OBJECT_TYPE,         B.SEGMENT_NAME OBJECT_NAME,         X.OBJECT_ALIAS ALIAS,         C.PARTITIONED,         CASE           WHEN C.PARTITIONED = 'YES' THEN            SUM(B.BYTES / 1024 / 1024) OVER(PARTITION BY C.TABLE_NAME)           ELSE            B.BYTES / 1024 / 1024         END SIZE_MB,         C.NUM_ROWS,         TRUNC(D.SAMPLE_SIZE / DECODE(D.NUM_ROWS, 0, 1, D.NUM_ROWS) * 100) || '%' ESTIMATE_PERCENT,         D.LAST_ANALYZED,         CASE           WHEN D.STALE_STATS = 'YES' OR D.LAST_ANALYZED IS NULL THEN            '统计信息过期'           ELSE            '统计信息未过期'         END STATUS,         ROW_NUMBER() OVER(PARTITION BY D.TABLE_NAME ORDER BY D.PARTITION_NAME) FLAG    FROM DBA_SEGMENTS B, DBA_TABLES C, DBA_TAB_STATISTICS D, X   WHERE B.OWNER || B.SEGMENT_NAME || B.PARTITION_NAME =         D.OWNER || D.TABLE_NAME || D.PARTITION_NAME     AND D.OWNER || D.TABLE_NAME = C.OWNER || C.TABLE_NAME     AND B.OWNER || B.SEGMENT_NAME || SUBSTR(B.SEGMENT_TYPE, 1, 5) =         X.OBJECT_OWNER || OBJECT_NAME ||SUBSTR(X.OBJECT_TYPE,1,5)     AND B.SEGMENT_TYPE LIKE 'TABLE%'     AND B.SEGMENT_NAME NOT LIKE '%BIN$%'  UNION ALL  SELECT B.OWNER,         X.OBJECT_TYPE,         B.SEGMENT_NAME OBJECT_NAME,         X.OBJECT_ALIAS ALIAS,         C.PARTITIONED,         CASE           WHEN C.PARTITIONED = 'YES' THEN            SUM(B.BYTES / 1024 / 1024) OVER(PARTITION BY C.INDEX_NAME)           ELSE            B.BYTES / 1024 / 1024         END SIZE_MB,         C.NUM_ROWS,         TRUNC(D.SAMPLE_SIZE / DECODE(D.NUM_ROWS, 0, 1, D.NUM_ROWS) * 100) || '%' ESTIMATE_PERCENT,         D.LAST_ANALYZED,         CASE           WHEN D.STALE_STATS = 'YES' OR D.LAST_ANALYZED IS NULL THEN            '统计信息过期'           ELSE            '统计信息未过期'         END STATUS,         ROW_NUMBER() OVER(PARTITION BY D.TABLE_NAME ORDER BY D.PARTITION_NAME) FLAG    FROM DBA_SEGMENTS B, DBA_INDEXES C, DBA_IND_STATISTICS D, X   WHERE B.OWNER || B.SEGMENT_NAME || B.PARTITION_NAME =         D.OWNER || D.INDEX_NAME || D.PARTITION_NAME     AND D.OWNER || D.INDEX_NAME = C.OWNER || C.INDEX_NAME     AND B.OWNER || B.SEGMENT_NAME || SUBSTR(B.SEGMENT_TYPE, 1, 5) =         X.OBJECT_OWNER || OBJECT_NAME ||SUBSTR(X.OBJECT_TYPE,1,5)     AND B.SEGMENT_TYPE LIKE 'INDEX%'     AND B.SEGMENT_NAME NOT LIKE '%BIN$%')SELECT OWNER,       OBJECT_TYPE,       OBJECT_NAME,       ALIAS,       PARTITIONED,       SIZE_MB,       NUM_ROWS,       ESTIMATE_PERCENT,       LAST_ANALYZED,       STATUS  FROM Z WHERE FLAG = 1;

结果如下：

首先：这个视图里面有order by，我在上一篇文章里面说过，视图里面的order by没意义，直接去掉！！！！！

根据上面表信息可以看出  这里面唯一的大表INSTRUMENT_TABLE  被访问了两次，然后能称得上大的表就是WRK_ACCT_MGR和SMG_WRK_RELATION。PLAN对这3个表的处理方式，决定了整个SQL性能的优劣。【小表无论走什么访问路径和连接方式都不可能引起性能问题】

所以我们把这几个表所在的SQL单拿出来，只需要分析这部分的SQL即可

第一段SQL和PLAN如下:

With val_yxtd As  (  select /*+ parallel(aa,4) +*/ *  from smg_wrk_relation aa  where exists  (select 1 from wrk_acct_mgr a  inner join instrument_table b  on  substr(a.acct_no,1,16) = substr(b.cust_acct_no,1,16)  where  a.mgr_code = aa.orgin_code1  and a.org_l2 = aa.orgin_code2  )  and  aa.relation_type = 'cc_mgr')Plan hash value: 247310325 ----------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                                | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |----------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                         |                    |   297K|   328M|       |   412K  (1)| 01:22:34 |        |      |            ||   1 |  PX COORDINATOR                          |                    |       |       |       |            |          |        |      |            ||   2 |   PX SEND QC (RANDOM)                    | :TQ10003           |   297K|   328M|       |   412K  (1)| 01:22:34 |  Q1,03 | P->S | QC (RAND)  ||   3 |    VIEW                                  | VM_NWVW_2          |   297K|   328M|       |   412K  (1)| 01:22:34 |  Q1,03 | PCWP |            ||   4 |     HASH UNIQUE                          |                    |   297K|    49M|    51M|   412K  (1)| 01:22:34 |  Q1,03 | PCWP |            ||   5 |      PX RECEIVE                          |                    |   297K|    49M|       |   412K  (1)| 01:22:34 |  Q1,03 | PCWP |            ||   6 |       PX SEND HASH                       | :TQ10002           |   297K|    49M|       |   412K  (1)| 01:22:34 |  Q1,02 | P->P | HASH       ||   7 |        HASH UNIQUE                       |                    |   297K|    49M|    51M|   412K  (1)| 01:22:34 |  Q1,02 | PCWP |            ||*  8 |         FILTER                         |                    |   297K|    49M|       |   412K  (1)| 01:22:34 |  Q1,02 | PCWP |            ||   9 |          PX RECEIVE                      |                    |   260K|    39M|       | 57333   (1)| 00:11:28 |  Q1,02 | PCWP |            ||  10 |           PX SEND HASH                   | :TQ10001           |   260K|    39M|       | 57333   (1)| 00:11:28 |  Q1,01 | P->P | HASH       ||  11 |            FILTER                        |                    |   260K|    39M|       | 57333   (1)| 00:11:28 |  Q1,01 | PCWP |            ||  12 |             FILTER                |                    |   261K|    39M|       | 57333   (1)| 00:11:28 |  Q1,01 | PCWP |            ||  13 |              PX BLOCK ITERATOR           |                    |       |       |       |            |          |  Q1,01 | PCWC |            ||  14 |               TABLE ACCESS BY INDEX ROWID| SMG_WRK_RELATION   | 32717 |  3929K|       |  1916   (1)| 00:00:23 |  Q1,01 | PCWP |            ||* 15 |                INDEX RANGE SCAN          | IN_SMG_RELATION_01 |     8 |       |       |     6   (0)| 00:00:01 |  Q1,01 | PCWP |            ||* 16 |              INDEX RANGE SCAN            | IN_WRK_ACCT_MGR_01 |     8 |       |       |     6   (0)| 00:00:01 |  Q1,01 | PCWP |            ||  17 |             TABLE ACCESS BY INDEX ROWID  | WRK_ACCT_MGR       |     8 |   280 |       |     9   (0)| 00:00:01 |  Q1,01 | PCWP |            ||  18 |          BUFFER SORT                     |                    |       |       |       |            |          |  Q1,02 | PCWC |            ||  19 |           PX RECEIVE                     |                    |    26M|   404M|       |   355K  (1)| 01:11:05 |  Q1,02 | PCWP |            ||  20 |            PX SEND HASH                  | :TQ10000           |    26M|   404M|       |   355K  (1)| 01:11:05 |        | S->P | HASH       ||  21 |             TABLE ACCESS FULL            | INSTRUMENT_TABLE   |    26M|   404M|       |   355K  (1)| 01:11:05 |        |      |            |-------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------    8 - access(SUBSTR("ACCT_NO",1,16)=SUBSTR("B"."CUST_ACCT_NO",1,16))  15 - filter("AA"."RELATION_TYPE"='cc_mgr')  16 - access("AA"."ORGIN_CODE2"="A"."ORG_L2" AND "AA"."ORGIN_CODE1"="A"."MGR_CODE") Note-----   - dynamic sampling used for this statement (level=6)

这个SQL所涉及的三个表正好都是我上述所说的大表。这个SQL最终返回3w行，走filter显然不是很好

1.第15步aa.relation_type = 'cc_mgr' 走index range scan后返回3w条数据（aa表总共135w+条数据），看上去还算合理，但是需要3w次回表（第14步），3w次回表是一笔不小的cost
2.aa表作为驱动表去驱动a表返回结果同样需要 回表3w次
3.最坑爹的是aa表和a表nl的结果集作为驱动表去驱动b表，关联条件substr(a.acct_no,1,16) = substr(b.cust_acct_no,1,16)，被驱动表b无法走索引
只能走全表。也就是说这个2600w行占用空间11GB的b表需要被table access full 3w次.。。。。。这是一件很恐怖的事情。

所以这个SQL优化的主要手段就是消除FILTER或者NL，因为一旦走了这种“传值类”的连接方式。b表就需要被扫描3w+次。当然最好的方式就是走hash连接，大表只扫一次。

FILTER产生的原因有很多，诸如 子查询里面有主查询的过滤条件、or exists、子查询被固化等等。但是这里面的FILTER并不是因为SQL的写法引起的，而是CBO认为最好的连接方式

根据我的经验 exists/not exists 这种传值类产生FILTER的概率比in/not in高，当然需要排除子查询里面出现NULL的情况。所以我个人比较推荐半连接/反连接用in/not in代替exists/not exists。所以将这段SQL改写如下，

其实就算我不改写也可以用hint的方式使其走hash连接。我改写的目的只是为了说明in/not in可以走正常执行计划。

  With val_yxtd As  ( select /*+ materialize full(aa) parallel(aa 4)*/ *  from smg_wrk_relation aa  where (aa.orgin_code1,aa.orgin_code2) in(select a.mgr_code,a.org_l2 from wrk_acct_mgr a  inner join instrument_table b  on  substr(a.acct_no,1,16) = substr(b.cust_acct_no,1,16))  and  aa.relation_type = 'cc_mgr'

表aa走table access full比走索引+回表的效率要高。 我这里加了full(aa) 的hint 

这一段SQL总共返回3w行数据，所以加materialize hint固化，可以把这个结果数据量小的结果集作为临时表（当这个结果集被引用1次以上的时候会自动固化结果集）

第二段SQL和PLAN如下：

Select '责任中心表示为支行但是无对应账户' ct      ,'责任中心表示为支行但是无对应账户' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,             0) occur_balFrom   vw_cost_center c      ,ledger_occur   b      ,org_l2         eWhere  Not Exists (Select 1  From   instrument_table d  Where  c.leaf_code = d.org_unit_id) And c.opt_txt1 = 'BR' And c.opt_txt20 = e.org_l2 And c.leaf_code = b.cost_center)Plan hash value: 3375556997 ------------------------------------------------------------------------------------------------------------------| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |                             |     2 |  1910 |       |   356K  (1)| 01:11:22 ||   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          ||   2 |   LOAD AS SELECT           | SYS_TEMP_0FDA48FE9_26499B86 |       |       |       |            |          ||   3 |    HASH GROUP BY           |                             |   348 | 22620 |       |   896   (2)| 00:00:11 ||*  4 |     HASH JOIN RIGHT OUTER  |                             | 54301 |  3446K|       |   893   (1)| 00:00:11 ||*  5 |      TABLE ACCESS FULL     | SMG_DEF_LEVES               |    14 |   728 |       |    68   (0)| 00:00:01 ||*  6 |      TABLE ACCESS FULL     | LEDGER_STAT                 | 54301 |   689K|       |   824   (1)| 00:00:10 ||   7 |   LOAD AS SELECT           | SYS_TEMP_0FDA48FEA_26499B86 |       |       |       |            |          ||   8 |    HASH UNIQUE             |                             |  4125 |  2578K|  2760K|   576   (1)| 00:00:07 ||   9 |     TABLE ACCESS FULL      | DIM_TREE_3                  |  4125 |  2578K|       |    15   (0)| 00:00:01 ||* 10 |   HASH JOIN                |                             |     2 |  1910 |       |   355K  (1)| 01:11:04 ||* 11 |    HASH JOIN               |                             |     1 |   315 |       |   355K  (1)| 01:11:03 ||* 12 |     HASH JOIN ANTI         |                             |     1 |    44 |       |   355K  (1)| 01:11:03 ||* 13 |      TABLE ACCESS FULL     | SMG_DEF_LEVES               |   124 |  4712 |       |    68   (0)| 00:00:01 ||  14 |      TABLE ACCESS FULL     | INSTRUMENT_TABLE            |    26M|   151M|       |   355K  (1)| 01:11:01 ||  15 |     VIEW                   |                             |   348 | 94308 |       |     3   (0)| 00:00:01 ||  16 |      TABLE ACCESS FULL     | SYS_TEMP_0FDA48FE9_26499B86 |   348 | 22620 |       |     3   (0)| 00:00:01 ||  17 |    VIEW                    |                             |  4125 |  2578K|       |    99   (0)| 00:00:02 ||  18 |     TABLE ACCESS FULL      | SYS_TEMP_0FDA48FEA_26499B86 |  4125 |  2578K|       |    99   (0)| 00:00:02 |------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):---------------------------------------------------    4 - access("L"."LEAF_CODE"(+)="COST_CENTER")   5 - filter("L"."OPT_TXT10"(+) IS NOT NULL AND "L"."FIELD_ID"(+)=8)   6 - filter("SRC_ID"='SG')  10 - access("OPT_TXT20"="E"."ORG_L2")  11 - access("LEAF_CODE"="B"."COST_CENTER")  12 - access("LEAF_CODE"="D"."ORG_UNIT_ID")  13 - filter("OPT_TXT20" IS NOT NULL AND "OPT_TXT1"='BR' AND "FIELD_ID"=8) Note-----   - dynamic sampling used for this statement (level=2)

上面SQL 所涉及表的大小如下：
vw_cost_center 4335
ledger_occur   2521
org_l2         105
所以这个SQL的主要消耗在INSTRUMENT_TABLE的TABLE ACCESS FULL所以PLAN没问题.（如果其他几个表都是参数表，让INSTRUMENT_TABLE作为驱动表效率更高）


优化完之后整个视图1s就出结果，引用到这个视图的前台页面查询SQL也集体“得救”，最终的SQL和PLAN如下：

select ct 检查类型 ,cc          错误类型      ,org_l1            一级行编码      ,org_l1_desc       一级行说明      ,org_l2            二级行编码      ,org_l2_desc        二级行说明      ,leaf_code   责任中心编码      ,leaf_desc 责任中心说明      ,occur_bal         发生额      from(With val_yxtd As  ( select /*+ materialize full(aa) parallel(aa 4)*/ *  from smg_wrk_relation aa  where (aa.orgin_code1,aa.orgin_code2) in(select a.mgr_code,a.org_l2 from wrk_acct_mgr a  inner join instrument_table b  on  substr(a.acct_no,1,16) = substr(b.cust_acct_no,1,16))  and  aa.relation_type = 'cc_mgr'),ledger_occur As (select/*+ materialize */ nvl(l.opt_txt10,cost_center) cost_center,occur_bal from ( Select cost_center        ,Sum(occur_bal) occur_bal  From   ledger_stat  Where  src_id = 'SG'  Group  By cost_center) m  left join smg_def_leves l --表中维护总账成本落在金融工具表中经营主体 对应opt_txt10字段维护on l.leaf_code=m.cost_centerand l.field_id=8 and l.opt_txt10 is not null),--459089.36,org_l2 As (Select /*+ materialize */Distinct level_01_code org_l1                 ,level_01_desc org_l1_desc                 ,level_02_code org_l2                 ,level_02_desc org_l2_desc  From   dim_tree_3)Select '责任中心为营销团队但无有效账户' ct      ,'没有维护营销团队与客户经理关系的数据' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,           0) occur_balFrom   vw_cost_center c      ,org_l2         e      ,ledger_occur   bWhere  c.opt_txt1 = 'MK'And    c.opt_txt20 = e.org_l2And    c.leaf_code = b.cost_center(+)And    Not Exists (Select 1        From   smg_wrk_relation f        Where  c.leaf_code = f.relation_code        And    f.relation_type = 'cc_mgr')Union AllSelect '责任中心为营销团队但无有效账户' ct      ,'维护营销团队与客户经理关系，但仍不存在有效账户的数据' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,            0) occur_balFrom   vw_cost_center c      ,org_l2         e      ,ledger_occur   bWhere  c.opt_txt1 = 'MK' And c.opt_txt20 = e.org_l2 And c.leaf_code = b.cost_center(+) And Exists (Select 1  From   smg_wrk_relation f  Where  c.leaf_code = f.relation_code  And    f.relation_type = 'cc_mgr') And Not Exists (Select 1  From   val_yxtd f  Where  c.leaf_code = f.relation_code)Union AllSelect '责任中心表示为支行但是无对应账户' ct      ,'责任中心表示为支行但是无对应账户' cc      ,e.org_l1      ,e.org_l1_desc      ,e.org_l2      ,e.org_l2_desc      ,c.leaf_code      ,c.leaf_desc      ,nvl(b.occur_bal,             0) occur_balFrom   vw_cost_center c      ,ledger_occur   b      ,org_l2         eWhere  Not Exists (Select 1  From   instrument_table d  Where  c.leaf_code = d.org_unit_id) And c.opt_txt1 = 'BR' And c.opt_txt20 = e.org_l2 And c.leaf_code = b.cost_center); Plan hash value: 3264994889 ------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                               | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |------------------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                        |                             | 15833 |    15M|       |   375K  (1)| 01:15:06 |        |      |            ||   1 |  TEMP TABLE TRANSFORMATION              |                             |       |       |       |            |          |        |      |            ||   2 |   PX COORDINATOR                        |                             |       |       |       |            |          |        |      |            ||   3 |    PX SEND QC (RANDOM)                  | :TQ10002                    | 32717 |    16M|       |   622K  (1)| 02:04:35 |  Q1,02 | P->S | QC (RAND)  ||   4 |     LOAD AS SELECT                      | SYS_TEMP_0FDA48FF1_26499B86 |       |       |       |            |          |  Q1,02 | PCWP |            ||*  5 |      HASH JOIN SEMI                     |                             | 32717 |    16M|       |   622K  (1)| 02:04:35 |  Q1,02 | PCWP |            ||   6 |       PX RECEIVE                        |                             | 32717 |  3929K|       |  1916   (1)| 00:00:23 |  Q1,02 | PCWP |            ||   7 |        PX SEND HASH                     | :TQ10001                    | 32717 |  3929K|       |  1916   (1)| 00:00:23 |  Q1,01 | P->P | HASH       ||   8 |         PX BLOCK ITERATOR               |                             | 32717 |  3929K|       |  1916   (1)| 00:00:23 |  Q1,01 | PCWC |            ||*  9 |          TABLE ACCESS FULL              | SMG_WRK_RELATION            | 32717 |  3929K|       |  1916   (1)| 00:00:23 |  Q1,01 | PCWP |            ||  10 |       BUFFER SORT                       |                             |       |       |       |            |          |  Q1,02 | PCWC |            ||  11 |        PX RECEIVE                       |                             |    36M|    13G|       |   620K  (1)| 02:04:11 |  Q1,02 | PCWP |            ||  12 |         PX SEND HASH                    | :TQ10000                    |    36M|    13G|       |   620K  (1)| 02:04:11 |        | S->P | HASH       ||  13 |          VIEW                           | VW_NSO_1                    |    36M|    13G|       |   620K  (1)| 02:04:11 |        |      |            ||* 14 |           HASH JOIN                     |                             |    36M|  1797M|   707M|   620K  (1)| 02:04:11 |        |      |            ||  15 |            TABLE ACCESS FULL            | INSTRUMENT_TABLE            |    26M|   404M|       |   355K  (1)| 01:11:05 |        |      |            ||  16 |            TABLE ACCESS FULL            | WRK_ACCT_MGR                |    32M|  1082M|       |   157K  (1)| 00:31:34 |        |      |            ||  17 |   LOAD AS SELECT                        | SYS_TEMP_0FDA48FF2_26499B86 |       |       |       |            |          |        |      |            ||  18 |    HASH GROUP BY                        |                             |     1 |    91 |       |   896   (2)| 00:00:11 |        |      |            ||* 19 |     HASH JOIN RIGHT OUTER               |                             | 54301 |  4825K|       |   893   (1)| 00:00:11 |        |      |            ||* 20 |      TABLE ACCESS FULL                  | SMG_DEF_LEVES               |     2 |   156 |       |    68   (0)| 00:00:01 |        |      |            ||* 21 |      TABLE ACCESS FULL                  | LEDGER_STAT                 | 54301 |   689K|       |   824   (1)| 00:00:10 |        |      |            ||  22 |   LOAD AS SELECT                        | SYS_TEMP_0FDA48FF3_26499B86 |       |       |       |            |          |        |      |            ||  23 |    HASH UNIQUE                          |                             |  4125 |  2578K|  2760K|   576   (1)| 00:00:07 |        |      |            ||  24 |     TABLE ACCESS FULL                   | DIM_TREE_3                  |  4125 |  2578K|       |    15   (0)| 00:00:01 |        |      |            ||  25 |   PX COORDINATOR                        |                             |       |       |       |            |          |        |      |            ||  26 |    PX SEND QC (RANDOM)                  | :TQ20007                    | 15833 |    15M|       |   375K  (1)| 01:15:06 |  Q2,07 | P->S | QC (RAND)  ||  27 |     BUFFER SORT                         |                             | 15833 |    15M|       |            |          |  Q2,07 | PCWP |            ||  28 |      VIEW                               |                             | 15833 |    15M|       |   375K  (1)| 01:15:06 |  Q2,07 | PCWP |            ||  29 |       UNION-ALL                         |                             |       |       |       |            |          |  Q2,07 | PCWP |            ||  30 |        BUFFER SORT                      |                             |       |       |       |            |          |  Q2,07 | PCWC |            ||  31 |         PX RECEIVE                      |                             |       |       |       |            |          |  Q2,07 | PCWP |            ||  32 |          PX SEND ROUND-ROBIN            | :TQ20002                    |       |       |       |            |          |        | S->P | RND-ROBIN  ||* 33 |           FILTER                        |                             |       |       |       |            |          |        |      |            ||* 34 |            HASH JOIN                    |                             | 15677 |    14M|       |   170   (1)| 00:00:03 |        |      |            ||* 35 |             HASH JOIN RIGHT OUTER       |                             |   485 |   146K|       |    71   (2)| 00:00:01 |        |      |            ||  36 |              VIEW                       |                             |     1 |   271 |       |     2   (0)| 00:00:01 |        |      |            ||  37 |               TABLE ACCESS FULL         | SYS_TEMP_0FDA48FF2_26499B86 |     1 |    91 |       |     2   (0)| 00:00:01 |        |      |            ||* 38 |              TABLE ACCESS FULL          | SMG_DEF_LEVES               |   485 | 18430 |       |    68   (0)| 00:00:01 |        |      |            ||  39 |             VIEW                        |                             |  4125 |  2578K|       |    99   (0)| 00:00:02 |        |      |            ||  40 |              TABLE ACCESS FULL          | SYS_TEMP_0FDA48FF3_26499B86 |  4125 |  2578K|       |    99   (0)| 00:00:02 |        |      |            ||* 41 |            INDEX RANGE SCAN             | IN_SMG_WRK_RELATION         |     6 |   102 |       |     3   (0)| 00:00:01 |        |      |            ||* 42 |        HASH JOIN                        |                             |   157 |   157K|       |   349   (1)| 00:00:05 |  Q2,07 | PCWP |            ||  43 |         PX RECEIVE                      |                             |     5 |  1940 |       |   250   (1)| 00:00:04 |  Q2,07 | PCWP |            ||  44 |          PX SEND HASH                   | :TQ20006                    |     5 |  1940 |       |   250   (1)| 00:00:04 |  Q2,06 | P->P | HASH       ||  45 |           NESTED LOOPS SEMI             |                             |     5 |  1940 |       |   250   (1)| 00:00:04 |  Q2,06 | PCWP |            ||* 46 |            HASH JOIN OUTER              |                             |     5 |  1855 |       |   247   (1)| 00:00:03 |  Q2,06 | PCWP |            ||* 47 |             HASH JOIN ANTI              |                             |     5 |   500 |       |   245   (1)| 00:00:03 |  Q2,06 | PCWP |            ||  48 |              BUFFER SORT                |                             |       |       |       |            |          |  Q2,06 | PCWC |            ||  49 |               PX RECEIVE                |                             |   485 | 18430 |       |    68   (0)| 00:00:01 |  Q2,06 | PCWP |            ||  50 |                PX SEND HASH             | :TQ20000                    |   485 | 18430 |       |    68   (0)| 00:00:01 |        | S->P | HASH       ||* 51 |                 TABLE ACCESS FULL       | SMG_DEF_LEVES               |   485 | 18430 |       |    68   (0)| 00:00:01 |        |      |            ||  52 |              PX RECEIVE                 |                             | 32717 |  1980K|       |   177   (1)| 00:00:03 |  Q2,06 | PCWP |            ||  53 |               PX SEND HASH              | :TQ20005                    | 32717 |  1980K|       |   177   (1)| 00:00:03 |  Q2,05 | P->P | HASH       ||  54 |                VIEW                     |                             | 32717 |  1980K|       |   177   (1)| 00:00:03 |  Q2,05 | PCWP |            ||  55 |                 PX BLOCK ITERATOR       |                             | 32717 |  4568K|       |   177   (1)| 00:00:03 |  Q2,05 | PCWC |            ||  56 |                  TABLE ACCESS FULL      | SYS_TEMP_0FDA48FF1_26499B86 | 32717 |  4568K|       |   177   (1)| 00:00:03 |  Q2,05 | PCWP |            ||  57 |             BUFFER SORT                 |                             |       |       |       |            |          |  Q2,06 | PCWC |            ||  58 |              PX RECEIVE                 |                             |     1 |   271 |       |     2   (0)| 00:00:01 |  Q2,06 | PCWP |            ||  59 |               PX SEND HASH              | :TQ20001                    |     1 |   271 |       |     2   (0)| 00:00:01 |        | S->P | HASH       ||  60 |                VIEW                     |                             |     1 |   271 |       |     2   (0)| 00:00:01 |        |      |            ||  61 |                 TABLE ACCESS FULL       | SYS_TEMP_0FDA48FF2_26499B86 |     1 |    91 |       |     2   (0)| 00:00:01 |        |      |            ||* 62 |            INDEX RANGE SCAN             | IN_SMG_WRK_RELATION         | 32717 |   543K|       |     2   (0)| 00:00:01 |  Q2,06 | PCWP |            ||  63 |         BUFFER SORT                     |                             |       |       |       |            |          |  Q2,07 | PCWC |            ||  64 |          PX RECEIVE                     |                             |  4125 |  2578K|       |    99   (0)| 00:00:02 |  Q2,07 | PCWP |            ||  65 |           PX SEND HASH                  | :TQ20003                    |  4125 |  2578K|       |    99   (0)| 00:00:02 |        | S->P | HASH       ||  66 |            VIEW                         |                             |  4125 |  2578K|       |    99   (0)| 00:00:02 |        |      |            ||  67 |             TABLE ACCESS FULL           | SYS_TEMP_0FDA48FF3_26499B86 |  4125 |  2578K|       |    99   (0)| 00:00:02 |        |      |            ||  68 |        BUFFER SORT                      |                             |       |       |       |            |          |  Q2,07 | PCWC |            ||  69 |         PX RECEIVE                      |                             |     1 |   955 |       |   355K  (1)| 01:11:03 |  Q2,07 | PCWP |            ||  70 |          PX SEND ROUND-ROBIN            | :TQ20004                    |     1 |   955 |       |   355K  (1)| 01:11:03 |        | S->P | RND-ROBIN  ||* 71 |           HASH JOIN                     |                             |     1 |   955 |       |   355K  (1)| 01:11:03 |        |      |            ||* 72 |            HASH JOIN ANTI               |                             |     1 |   315 |       |   355K  (1)| 01:11:02 |        |      |            ||  73 |             NESTED LOOPS                |                             |     1 |   309 |       |     3   (0)| 00:00:01 |        |      |            ||  74 |              NESTED LOOPS               |                             |     1 |   309 |       |     3   (0)| 00:00:01 |        |      |            ||  75 |               VIEW                      |                             |     1 |   271 |       |     2   (0)| 00:00:01 |        |      |            ||  76 |                TABLE ACCESS FULL        | SYS_TEMP_0FDA48FF2_26499B86 |     1 |    91 |       |     2   (0)| 00:00:01 |        |      |            ||* 77 |               INDEX UNIQUE SCAN         | PK_SMG_DEF_LEVES            |     1 |       |       |     0   (0)| 00:00:01 |        |      |            ||* 78 |              TABLE ACCESS BY INDEX ROWID| SMG_DEF_LEVES               |     1 |    38 |       |     1   (0)| 00:00:01 |        |      |            ||  79 |             TABLE ACCESS FULL           | INSTRUMENT_TABLE            |    26M|   151M|       |   355K  (1)| 01:11:01 |        |      |            ||  80 |            VIEW                         |                             |  4125 |  2578K|       |    99   (0)| 00:00:02 |        |      |            ||  81 |             TABLE ACCESS FULL           | SYS_TEMP_0FDA48FF3_26499B86 |  4125 |  2578K|       |    99   (0)| 00:00:02 |        |      |            |------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):---------------------------------------------------    5 - access("AA"."ORGIN_CODE1"="MGR_CODE" AND "AA"."ORGIN_CODE2"="ORG_L2")   9 - filter("AA"."RELATION_TYPE"='cc_mgr')  14 - access(SUBSTR("ACCT_NO",1,16)=SUBSTR("B"."CUST_ACCT_NO",1,16))  19 - access("L"."LEAF_CODE"(+)="COST_CENTER")  20 - filter("L"."OPT_TXT10"(+) IS NOT NULL AND "L"."FIELD_ID"(+)=8)  21 - filter("SRC_ID"='SG')  33 - filter( NOT EXISTS (SELECT 0 FROM "ANNE_MAIN"."SMG_WRK_RELATION" "F" WHERE "F"."RELATION_CODE"=:B1 AND "F"."RELATION_TYPE"='cc_mgr'))  34 - access("OPT_TXT20"="E"."ORG_L2")  35 - access("LEAF_CODE"="B"."COST_CENTER"(+))  38 - filter("OPT_TXT20" IS NOT NULL AND "OPT_TXT1"='MK' AND "FIELD_ID"=8)  41 - access("F"."RELATION_TYPE"='cc_mgr' AND "F"."RELATION_CODE"=:B1)  42 - access("OPT_TXT20"="E"."ORG_L2")  46 - access("LEAF_CODE"="B"."COST_CENTER"(+))  47 - access("LEAF_CODE"="F"."RELATION_CODE")  51 - filter("OPT_TXT20" IS NOT NULL AND "OPT_TXT1"='MK' AND "FIELD_ID"=8)  62 - access("F"."RELATION_TYPE"='cc_mgr' AND "LEAF_CODE"="F"."RELATION_CODE")  71 - access("OPT_TXT20"="E"."ORG_L2")  72 - access("LEAF_CODE"="D"."ORG_UNIT_ID")  77 - access("LEAF_CODE"="B"."COST_CENTER" AND "FIELD_ID"=8)  78 - filter("OPT_TXT20" IS NOT NULL AND "OPT_TXT1"='BR') Note-----   - dynamic sampling used for this statement (level=6)

总结：

优化，一定要找SQL的性能瓶颈部分。把瓶颈搞定了，虽然这个PLAN可能不是最优的，但也绝对不会引起大的性能问题。 优化就是要搞定最突出的问题，而绝不是锦上添花。。。。

优化SQL，一定要找大表，因为只能大表才可能引起性能问题。优化之前获取这个SQL里面所有表的信息有助于你更快定位性能瓶颈