某系统有一条SQL 执行了十几个小时没出结果~
【SQL复杂 案例简单 已经精通oracle连接方式的可自行绕道别的案例~】
SQL语句和执行计划如下：
select tt.org_type,
tt.prod_code,
tt.prod_name,
tt.BRAND_CODE,
tt.iscapital,
tt.cur,
tt.rijun,
tt.yuemo,
tt.jine
from (select case
when t.cust_type = '3' then
'个人'
when t.cust_type = '0' then
case
when t.org_type = '2' then
'同业'
when t.org_type = '1' then
'企业'
else
'机构'
end
else
'不限'
end org_type,
t.prod_code,
t1.prod_name,
t2.BRAND_CODE,
case
when t2.income_characteristic = '1' then
'否'
when t2.income_characteristic in ('2', '3') then
'是'
end as iscapital,
t1.prod_currency cur,
trunc(sum(case
when t.workdate between '20180101' and '20180930' then
t.total_vol
else
0
end) / (select (to_date('20180930', 'yyyymmdd') -
to_date('20180101', 'yyyymmdd')) + 1
from dual),
3) rijun,
sum(case
when t.workdate =
to_char(add_months(to_date(substr('20180930', 1, 6) || '01',
'yyyymmdd'),
1) - 1,
'yyyymmdd') then
t.total_vol
else
0
end) yuemo,
trunc(sum(case
when t.workdate between '20180101' and '20180930' then
t.total_vol
else
0
end) / (select (to_date('20180930', 'yyyymmdd') -
to_date('20180101', 'yyyymmdd')) + 1
from dual),
3) * nvl(t.nav, '1') jine
from T5_AAAA_BBBBB_ORG t
left join T5_AAAA_BBBB t1
on t.prod_code = t1.prod_code
left join T5_AAAA_BBBB_ADDITION t2
on t.prod_code = t2.prod_code
where (t.workdate between '20180101' and '20180930' or
t.workdate =
to_char(add_months(to_date(substr('20180930', 1, 6) || '01',
'yyyymmdd'),
1) - 1,
'yyyymmdd'))
and (1 = 1 OR ('' = '3' and
(t.cust_type = '' or instr(t.cust_type, '') <> 0 or
t.cust_type = '-1')) OR
('' = '1' and t.cust_type <> '3' and
(t.org_type = '' or instr(t.org_type, '') <> 0 or
t.org_type = '-1')) OR
('' = '2' and t.cust_type <> '3' and
(t.org_type = '' or instr(t.org_type, '') <> 0 or
t.org_type = '-1')))
and (1 = 1 OR t1.profit_type = '')
and (1 = 1 OR t2.income_characteristic = '')
and (1 = 1 OR t1.prod_code = '')
and (1 = 1 OR t1.prod_name = '')
and (1 = 1 or t1.prod_lifecycle IN (''))
and (1 = 1 or t2.prodsection = '')
and (t.orgno = '6500' OR t.branch_code = '6500' OR
t.sub_branch_code = '6500' or
(exists
(select 1
from sys_dict_item d
where d.itemval = '6500'
and d.itemkey = t.branch_code
and d.dict = 'branch_relation') and '' = '1'))
and (1 = 0 OR '2' in ('0', '2'))
group by t.prod_code,
t1.prod_name,
t.org_type,
t.cust_type,
t2.BRAND_CODE,
t2.income_characteristic,
t.nav,
t1.prod_currency) tt;
Plan hash value: 267004458
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 375K(100)| |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 183K| 20M| | |
| 4 | CONCATENATION | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 118 | 19225 (1)| 00:03:51 |
| 6 | NESTED LOOPS OUTER | | 1 | 76 | 19223 (1)| 00:03:51 |
| 7 | TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBBB_ORG | 1 | 53 | 19222 (1)| 00:03:51 |
|* 8 | INDEX SKIP SCAN | PK_T5_AAAA_BBBBB_ORG | 1 | | 19221 (1)| 00:03:51 |
| 9 | TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBB_ADDITION | 1 | 23 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_T5_AAAA_BBBB_ADDITION | 1 | | 0 (0)| |
| 11 | TABLE ACCESS BY INDEX ROWID | T5_AAAA_BBBB | 1 | 42 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | PK_T5_AAAA_BBBB | 1 | | 1 (0)| 00:00:01 |
|* 13 | HASH JOIN RIGHT OUTER | | 69859 | 8050K| 134K (1)| 00:26:56 |
| 14 | TABLE ACCESS FULL | T5_AAAA_BBBB_ADDITION | 22584 | 507K| 208 (1)| 00:00:03 |
|* 15 | HASH JOIN RIGHT OUTER | | 67177 | 6232K| 134K (1)| 00:26:53 |
| 16 | TABLE ACCESS FULL | T5_AAAA_BBBB | 22594 | 926K| 243 (1)| 00:00:03 |
| 17 | TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBBB_ORG | 67177 | 3476K| 134K (1)| 00:26:50 |
|* 18 | INDEX SKIP SCAN | PK_T5_AAAA_BBBBB_ORG | 67177 | | 67143 (1)| 00:13:26 |
|* 19 | HASH JOIN RIGHT OUTER | | 113K| 12M| 218K (1)| 00:43:40 |
| 20 | TABLE ACCESS FULL | T5_AAAA_BBBB_ADDITION | 22584 | 507K| 208 (1)| 00:00:03 |
|* 21 | HASH JOIN RIGHT OUTER | | 108K| 9M| 218K (1)| 00:43:37 |
| 22 | TABLE ACCESS FULL | T5_AAAA_BBBB | 22594 | 926K| 243 (1)| 00:00:03 |
| 23 | TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBBB_ORG | 108K| 5640K| 217K (1)| 00:43:35 |
|* 24 | INDEX SKIP SCAN | PK_T5_AAAA_BBBBB_ORG | 109K| | 109K (1)| 00:21:49 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("T"."ORGNO"='6500')
filter(("T"."ORGNO"='6500' AND (("T"."WORKDATE">='20180101' AND "T"."WORKDATE"<='20180930')
OR "T"."WORKDATE"='20180930')))
10 - access("T"."PROD_CODE"="T2"."PROD_CODE")
12 - access("T"."PROD_CODE"="T1"."PROD_CODE")
13 - access("T"."PROD_CODE"="T2"."PROD_CODE")
15 - access("T"."PROD_CODE"="T1"."PROD_CODE")
18 - access("T"."SUB_BRANCH_CODE"='6500')
filter(("T"."SUB_BRANCH_CODE"='6500' AND (("T"."WORKDATE">='20180101' AND
"T"."WORKDATE"<='20180930') OR "T"."WORKDATE"='20180930') AND LNNVL("T"."ORGNO"='6500')))
19 - access("T"."PROD_CODE"="T2"."PROD_CODE")
21 - access("T"."PROD_CODE"="T1"."PROD_CODE")
24 - access("T"."BRANCH_CODE"='6500')
filter(("T"."BRANCH_CODE"='6500' AND (("T"."WORKDATE">='20180101' AND
"T"."WORKDATE"<='20180930') OR "T"."WORKDATE"='20180930') AND LNNVL("T"."SUB_BRANCH_CODE"='6500')
AND LNNVL("T"."ORGNO"='6500')))SQL语句中带OR条件一般要特别注意。这里我们先不分析SQL语句 直接看执行计划。
如果你是一个新手 不会特别详细分析执行计划，那么你就猜！！！通过执行计划可以看出来5-12步 rows全是1，这种情况100%有问题，我们猜测这一块有问题 然后我们去验证：用我之前博客发的脚本取一下SQL语句中的对象信息
拥有者 对象类型 对象名称 是否分区表 大小MB 行数 最近一次分析时间 分析判断
NNN TABLE T5_AAAA_BBBB NO 7 22594 2018-09-29 22:09:54 统计信息未过期
NNN TABLE T5_AAAA_BBBB_ADDITION NO 6 22584 2018-09-26 22:15:14 统计信息未过期
NNN TABLE T5_AAAA_BBBBB_ORG NO 12588 154698846 2018-09-30 22:23:18 统计信息未过期
NNN TABLE T6_PROD_INFO NO .125 78 2018-10-10 22:09:34 统计信息未过期
NNN TABLE T6_PROD_INFO_ADDITION NO .125 78 2018-10-09 22:14:43 统计信息未过期
NNN TABLE T6_PROD_STOCK_ORG NO .1875 1523 2018-09-30 14:11:21 统计信息未过期
NNN INDEX (UNIQUE) PK_T5_AAAA_BBBB NO 1 22594 2018-09-29 22:09:57 统计信息未过期
NNN INDEX (UNIQUE) PK_T5_AAAA_BBBB_ADDITION NO .875 21717 2018-06-26 22:08:18 统计信息过期
NNN INDEX (UNIQUE) PK_T5_AAAA_BBBBB_ORG NO 11177 154547735 2018-09-30 22:23:39 统计信息未过期ID=6 是NESTED LOOPS OUTER，驱动表是大表，1.5亿多行，去驱动2万多行的表T5_AAAA_BBBB_ADDITION，相当的坑，如果t表过滤后的数据很多，几千万甚至上亿，那此步骤跑不出结果。
综上分析，使用HASH代替执行计划中的嵌套循环? HINT： /*+ full(t) ?use_hash(t,t1) swap_join_inputs(t1) */
优化完之后SQL 5分钟出结果！！优化后的执行计划如下：
Plan hash value: 2075181580
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 446K(100)| |
| 1 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 183K| 20M| 22M| 446K (2)| 01:29:17 |
|* 4 | HASH JOIN RIGHT OUTER | | 183K| 20M| | 441K (2)| 01:28:18 |
| 5 | TABLE ACCESS FULL | T5_AAAA_BBBB_ADDITION | 22584 | 507K| | 208 (1)| 00:00:03 |
|* 6 | HASH JOIN RIGHT OUTER| | 176K| 15M| | 441K (2)| 01:28:16 |
| 7 | TABLE ACCESS FULL | T5_AAAA_BBBB | 22594 | 926K| | 243 (1)| 00:00:03 |
|* 8 | TABLE ACCESS FULL | T5_AAAA_BBBBB_ORG | 176K| 9117K| | 441K (2)| 01:28:13 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."PROD_CODE"="T2"."PROD_CODE")
6 - access("T"."PROD_CODE"="T1"."PROD_CODE")
8 - filter((("T"."BRANCH_CODE"='6500' OR "T"."SUB_BRANCH_CODE"='6500' OR "T"."ORGNO"='6500')
AND (("T"."WORKDATE">='20180101' AND "T"."WORKDATE"<='20180930') OR "T"."WORKDATE"='20180930')))
扫一扫
1095
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
