作为开发DBA，职责之一就是每个月都给各个系统出一份性能优化报告
连续给某个系统优化了好几条坑爹的大SQL之后，接口人今天找到我说 有一个视图从创建开始
和这个视图相关的SQL从来没有跑出来过。最长的一次跑了24小时没出结果。视图单跑也是2个小时跑不出来
用户很是抱怨，甚至现在都没有人愿意去点这个页面了，基本要放弃了，问我能不能拯救一下
视图源码SQL如下：
CREATE OR REPLACE VIEW VW_XXXXXXXX AS
select ct
,cc
,org_l1
,org_l1_desc
,ooo_l2
,ooo_l2_desc
,leaf_code
,leaf_desc
,occur_bal
from
(With val_yxtd As
( select /*+ parallel(aa,4) +*/ *
from aaa_aaa_relation aa
where exists
(select 1 from aaa_acct_aaa a
inner join ibbbbbbbbb_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.ooo_l2 = aa.orgin_code2
)
and aa.relation_type = 'cc_mgr'),
ledger_ooooo As
(select nvl(l.opt_txt10,cost_center) cost_center,occur_bal from (
Select cost_center
,Sum(occur_bal) occur_bal
From aaaaaa_stat
Where src_id = 'SG'
Group By cost_center) m
left join aaa_aaa_leves l
on l.leaf_code=m.cost_center
and l.field_id=8 and l.opt_txt10 is not null),--459089.36,
ooo_l2 As
(Select Distinct level_01_code org_l1
,level_01_desc org_l1_desc
,level_02_code ooo_l2
,level_02_desc ooo_l2_desc
From aaa_aaaa_3)
Select ' ' ct
,' ' cc
,e.org_l1
,e.org_l1_desc
,e.ooo_l2
,e.ooo_l2_desc
,c.leaf_code
,c.leaf_desc
,nvl(b.occur_bal,
0) occur_bal
From vw_cost_cccccc c
,ooo_l2 e
,ledger_ooooo b
Where c.opt_txt1 = 'MK'
And c.opt_txt20 = e.ooo_l2
And c.leaf_code = b.cost_center(+)
And Not Exists (Select 1
From aaa_aaa_relation f
Where c.leaf_code = f.relation_code
And f.relation_type = 'cc_mgr')
Union All
Select 'xx' ct
,'xx' cc
,e.org_l1
,e.org_l1_desc
,e.ooo_l2
,e.ooo_l2_desc
,c.leaf_code
,c.leaf_desc
,nvl(b.occur_bal,
0) occur_bal
From vw_cost_cccccc c
,ooo_l2 e
,ledger_ooooo b
Where c.opt_txt1 = 'MK'
And c.opt_txt20 = e.ooo_l2
And c.leaf_code = b.cost_center(+)
And Exists (Select 1
From aaa_aaa_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 All
Select 'xxx' ct
,'xxx' cc
,e.org_l1
,e.org_l1_desc
,e.ooo_l2
,e.ooo_l2_desc
,c.leaf_code
,c.leaf_desc
,nvl(b.occur_bal,
0) occur_bal
From vw_cost_cccccc c
,ledger_ooooo b
,ooo_l2 e
Where Not Exists (Select 1
From ibbbbbbbbb_table d
Where c.leaf_code = d.org_unit_id)
And c.opt_txt1 = 'BR'
And c.opt_txt20 = e.ooo_l2
And c.leaf_code = b.cost_center)
order by org_l1,ooo_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没意义，直接去掉！！！！！
根据上面表信息可以看出 这里面唯一的大表IBBBBBBBBB_TABLE 被访问了两次，然后能称得上大的表就是AAA_ACCT_AAA和AAA_AAA_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 AAA_AAA_RELATION a
inner join IBBBBBBBBB_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| AAA_AAA_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 | IBBBBBBBBB_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 AAA_AAA_relation aa
where (aa.orgin_code1,aa.orgin_code2) in(select a.mgr_code,a.org_l2 from wrk_acct_mgr a
inner join iBBBBBBBBB_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_bal
From vw_cost_center c
,ledger_occur b
,org_l2 e
Where 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 AAA_AAA_RELATION aa
where (aa.orgin_code1,aa.orgin_code2) in(select a.mgr_code,a.org_l2 from AAA_ACCT_AAA a
inner join IBBBBBBBBB_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 AAAAAA_STAT
Where src_id = 'SG'
Group By cost_center) m
left join AAA_AAA_LEVES l --表中维护总账成本落在金融工具表中经营主体 对应opt_txt10字段维护
on l.leaf_code=m.cost_center
and 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 DID_DDDD_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_bal
From vw_cost_center c
,org_l2 e
,ledger_occur b
Where c.opt_txt1 = 'MK'
And c.opt_txt20 = e.org_l2
And c.leaf_code = b.cost_center(+)
And Not Exists (Select 1
From AAA_AAA_RELATION f
Where c.leaf_code = f.relation_code
And f.relation_type = 'cc_mgr')
Union All
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_bal
From vw_cost_center c
,org_l2 e
,ledger_occur b
Where c.opt_txt1 = 'MK'
And c.opt_txt20 = e.org_l2
And c.leaf_code = b.cost_center(+)
And Exists (Select 1
From AAA_AAA_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 All
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_bal
From vw_cost_center c
,ledger_occur b
,org_l2 e
Where Not Exists (Select 1
From IBBBBBBBBB_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 | AAA_AAA_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 | IBBBBBBBBB_TABLE | 26M| 404M| | 355K (1)| 01:11:05 | | | |
| 16 | TABLE ACCESS FULL | AAA_ACCT_AAA | 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 | AAA_AAA_LEVES | 2 | 156 | | 68 (0)| 00:00:01 | | | |
|* 21 | TABLE ACCESS FULL | AAAAAA_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 | DID_DDDD_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 | AAA_AAA_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_AAA_AAA_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 | AAA_AAA_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_AAA_AAA_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_AAA_AAA_LEVES | 1 | | | 0 (0)| 00:00:01 | | | |
|* 78 | TABLE ACCESS BY INDEX ROWID| AAA_AAA_LEVES | 1 | 38 | | 1 (0)| 00:00:01 | | | |
| 79 | TABLE ACCESS FULL | IBBBBBBBBB_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"."AAA_AAA_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里面所有表的信息有助于你更快定位性能瓶颈
扫一扫
391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
