使用 parallel 优化SQL

研发人员反馈，程序查询缓慢 ，业务层面已经优化调整，查询页面仍需12s ，分析下语句是否有优化空间

 分析语句

explain plan for SELECT C.隶属机构I,
C.病人来源,
C.病人序号I,
C.系统序号 AS 申请序号I,
B.标本类型,
B.标本类型I,
B.送检时间,
C.健康序号I,
A.病人序号I AS 报告序号I,
A.系统序号 AS 明细序号I,
A.检验项目组合,
A.检验组合序号I,
A.中文名称,
A.检验项目序号I,
A.英文名称,
A.结果,
A.EXP结果,
A.EXP结果1,
A.EXP结果2,
A.EXP结果3,
A.EXP结果4,
A.项目单位,
A.高低标记,
A.参考值,
A.危急值,
A.危急提示,
A.结果单元格背景色,
A.组合显示顺序,
A.明细显示顺序,
B.微生物否B,
A.危急上限,
A.危急下限
FROM LIS_VI检验明细 A
INNER JOIN (SELECT BR.标本类型I,
DECODE(C.接口说明, '微生物', 1, 0) AS 微生物否B,
BR.送检时间，mx.申请序号I，BR.系统序号,
mx.检验组合I ,
E.名称 AS 标本类型
FROM LIS_D病人信息 BR
INNER JOIN LIS_D检验申请明细 MX
ON BR.系统序号 = MX.病人序号I
INNER JOIN LIS_T检验仪器 C
ON BR.仪器序号I = C.系统序号
INNER JOIN EXP_B标本 E
ON BR.标本类型I = E.系统序号
WHERE (br.标本状态 = '审核' OR br.标本状态 = '打印')
AND (BR.病人类型 = '门诊' OR BR.病人类型 = '住院' OR BR.病人类型 = '急诊')
) B
ON A.病人序号I = B.系统序号
AND (A.检验组合序号I = B.检验组合I OR B.微生物否B = 1)
INNER JOIN EXP_D检验申请列表 C ON C.系统序号 = B.申请序号I
WHERE 申请序号I = '1723' AND (检验组合序号I = '3096' OR 微生物否B = 1);

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2111894973

 

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9850 | 26 (43)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 9850 | 26 (43)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 9805 | 25 (44)| 00:00:01 |
| 3 | NESTED LOOPS | | 6 | 7308 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 6 | 7308 | 1 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 6 | 1074 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 6 | 570 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | EXP_D检验申请列表 | 1 | 56 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_EXP_D检验申请列 | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | LIS_D检验申请明细 | 6 | 234 | 0 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_120913_申请序号| 6 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | LIS_D病人信息 | 1 | 84 | 0 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_LIS_D病人信息 | 1 | | 0 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IK_LIS_T仪器_系统序| 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | LIS_T检验仪器 | 1 | 1039 | 0 (0)| 00:00:01 |
| 15 | VIEW | LIS_VI检验明细 | 696K| 5700M| 22 (41)| 00:00:01 |
| 16 | UNION-ALL | | | | | |
| 17 | NESTED LOOPS OUTER | | 696K| 4478M| 19 (48)| 00:00:01 |
| 18 | NESTED LOOPS OUTER | | 696K| 4443M| 16 (38)| 00:00:01 |
|* 19 | HASH JOIN RIGHT OUTER | | 696K| 4408M| 12 (17)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | LIS_T敏感程度 | 2 | 176 | 3 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 696K| 4350M| 7 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1833K| 4350M| 7 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | LIS_T检验项目 | 810 | 35640 | 7 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | IDX_120885_检验项目| 2264 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| LIS_D报告内容 | 859 | 5461K| 0 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | LIS_T仪器检验组合 | 1 | 52 | 0 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | IDX_121060_仪器序号| 9 | | 0 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | LIS_T检验项目组合明| 1 | 52 | 0 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IDX_121022_检验项目| 2 | | 0 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 13 | 1976 | 3 (0)| 00:00:01 |
| 31 | NESTED LOOPS | | 612 | 1976 | 3 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | LIS_T细菌档案 | 204 | 23052 | 3 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX_120966_细菌序号| 3 | | 0 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | LIS_D细菌报告 | 1 | 39 | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | EXP_B标本 | 1 | 45 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_B标本 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

 

2 - access("A"."病人序号I"="BR"."系统序号")
filter(("A"."检验组合序号I"=3096 OR DECODE(SYS_OP_C2C("C"."接口说明"),'微生物',1,0)=1) AND
("A"."检验组合序号I"="MX"."检验组合I" OR DECODE(SYS_OP_C2C("C"."接口说明"),'微生物',1,0)=1))
8 - access("C"."系统序号"=1723)
10 - access("MX"."申请序号I"=1723)
11 - filter(("BR"."标本状态"=U'\5BA1\6838' OR "BR"."标本状态"=U'\6253\5370') AND
("BR"."病人类型"=U'\4F4F\9662' OR "BR"."病人类型"=U'\6025\8BCA' OR "BR"."病人类型"=U'\95E8\8BCA'))
12 - access("BR"."系统序号"="MX"."病人序号I")
13 - access("BR"."仪器序号I"="C"."系统序号")
19 - access("A"."EXP结果"="B"."名称"(+))
20 - filter("B"."有效状态B"(+)=1)
24 - access("A"."检验项目序号I"="C"."系统序号")
26 - filter("A"."检验组合序号I"="E"."检验组合序号I"(+) AND "A"."隶属机构I"="E"."隶属机构I"(+))
27 - access("A"."仪器序号I"="E"."仪器序号I"(+))
28 - filter("A"."检验组合序号I"="D"."检验项目组合序号I"(+) AND "A"."隶属机构I"="D"."隶属机构I"(+))
29 - access("A"."检验项目序号I"="D"."检验项目序号I"(+))
33 - access("A"."细菌序号I"="B"."系统序号")
36 - access("BR"."标本类型I"="E"."系统序号")

 

Note
-----
- dynamic sampling used for this statement (level=2)

已选择69行。

其中最大表 EXP_D检验申请列表 接近80w  使用索引检索已最优 ，内联视图中语句如下
explan plan for SELECT BR.标本类型I,
DECODE(C.接口说明, '微生物', 1, 0) AS 微生物否B,
BR.送检时间，mx.申请序号I，BR.系统序号,
mx.检验组合I ,
E.名称 AS 标本类型
FROM LIS_D病人信息 BR
INNER JOIN LIS_D检验申请明细 MX
ON BR.系统序号 = MX.病人序号I
INNER JOIN LIS_T检验仪器 C
ON BR.仪器序号I = C.系统序号
INNER JOIN EXP_B标本 E
ON BR.标本类型I = E.系统序号
WHERE (br.标本状态 = '审核' OR br.标本状态 = '打印')
AND (BR.病人类型 = '门诊' OR BR.病人类型 = '住院' OR BR.病人类型 = '急诊');

 

sql执行27s
表关联中 连接列已建索引，无法找到更优的条件 ，使用hint

SELECT /*+ parallel(mx,4) parallel(br,4) */ BR.标本类型I,
DECODE(C.接口说明, '微生物', 1, 0) AS 微生物否B,
BR.送检时间，mx.申请序号I，BR.系统序号,
mx.检验组合I ,
E.名称 AS 标本类型
FROM LIS_D病人信息 BR
INNER JOIN LIS_D检验申请明细 MX
ON BR.系统序号 = MX.病人序号I
INNER JOIN LIS_T检验仪器 C
ON BR.仪器序号I = C.系统序号
INNER JOIN EXP_B标本 E
ON BR.标本类型I = E.系统序号
WHERE (br.标本状态 = '审核' OR br.标本状态 = '打印')
AND (BR.病人类型 = '门诊' OR BR.病人类型 = '住院' OR BR.病人类型 = '急诊')
) B

 

 优化后语句由 12s降至 1s内响应