存储过程 查询优化

现场反馈 查询慢 要10s多语句才跑完

排查发现 调用存储过程 分析如下

列表_住院确认(
Vi机构序号 Number,
Vi终端序号 Number,
Vi操作员序号 Number,


vd申请时间前 DATE,
vd申请时间后 DATE,
vi健康序号 NUMBER,
vs关键词 NVARCHAR2,

rs数据集 out sys_refcursor,

Pb输出状态 OUT NUMBER, PS输出描述 OUT NVARCHAR2) Is

Begin
Pb输出状态 := 0;
open rs数据集 for
Select
A.系统序号
,A.同步序号
,A.隶属机构i
,A.健康序号i,C.健康ID
,B.住院编号 As 就诊编号
,B.拼音简码
,B.姓名||case when A.婴儿序号i>0 then '['||X.姓名||']' end as 姓名
,Case When A.婴儿序号I>0 Then Null Else getage(B.年龄岁,B.年龄月,B.年龄天,0) End As 年龄
,B.性别i,XB.名称 As 性别
,B.床位i,CW.名称 As 床位
,A.病人来源
,A.病人序号I
,A.急诊b,A.急诊
,A.申请编号
,A.症状及体征
,A.诊断描述
,A.检查类别i,A.检查类别
,A.申请医师R,A.申请医师
,A.申请科室i,A.申请科室
,A.执行科室i,A.执行科室
,A.申请时间
,DECODE(MX.状态n,4,2,3,2,MX.状态n) 状态n,MX.状态
,MX.费用金额 As 费用总额
,A.系统序号||to_char(rownum) AS 唯一标识
-- ,MX.确认人r,E.名称 As 确认人
From INS_VD检查申请列表 A
Inner Join (
Select 系统序号,姓名,性别i,年龄岁,年龄月,年龄天,当前床位i As 床位i,拼音简码,住院编号 From INQ_D住院档案
Union All
Select 系统序号,姓名,性别i,年龄岁,年龄月,年龄天,当前床位i As 床位i,拼音简码,住院编号 From INQ_D住院档案Z
) B On A.病人序号i=B.系统序号
Inner Join Crm_D健康档案 C On C.系统序号=A.健康序号i
Inner Join (Select A.单据序号i, A.状态N,Decode(A.状态N,0,'申请',1,'记账','已执行') As 状态,sum(A.费用金额) As 费用金额 From INS_D检查申请明细记录 A
Inner Join INQ_D住院医嘱临时列表 Y On A.系统序号=Y.辅检明细I
WHere A.状态n <=4 And A.作废B=0 And Y.医嘱类别='检查' And (Y.医嘱状态N=2 Or Y.医嘱状态N=3)
Group By A.单据序号i,A.状态N) MX On A.系统序号=MX.单据序号i
Left Join INQ_D住院新生儿列表 X On X.系统序号=A.婴儿序号i
Left Join Doc_B性别 XB On XB.系统序号=B.性别i
Left Join Doc_T病床档案 CW On CW.系统序号=B.床位i
Where A.隶属机构I=vi机构序号 AND A.病人来源='住院' AND (A.健康序号I=vi健康序号 OR (vi健康序号 IS NULL AND (C.姓名 Like '%'||vs关键词||'%' OR C.拼音简码 Like '%'||vs关键词||'%'))) AND A.申请时间 BETWEEN vd申请时间前 AND vd申请时间后;

Pb输出状态 := 1;
close rs数据集;
Return;

Exception
When Others Then
Am_Error(Sqlcode, Sqlerrm);
Pb输出状态 := 0;
Ps输出描述 := Sqlcode || ':' || Sqlerrm;
End INS_PD检查申请列表_住院确认;

 

----------------------------------------------------------------------------

create or replace view ins_vd检查申请列表 as
Select
A.系统序号
,A.同步序号
,A.隶属机构I,B.名称 As 隶属机构
,A.病人来源
,A.病人序号I
,A.婴儿序号I
,A.急诊B,DeCode(A.急诊B,0,'否',1,'是') As 急诊
,A.检查类别I
,C.名称 As 检查类别
,A.申请编号
,A.症状及体征
,A.诊断ICD
,A.诊断描述
,A.申请医师R,D.名称 As 申请医师
,A.申请科室I,E.名称 As 申请科室
,A.申请时间 as 申请时间
,A.病人科室I,F.名称 As 病人科室
,A.执行科室i,G.名称 as 执行科室
,A.状态N,DeCode(A.状态N,0,'申请',1,'已收费',2,'已执行') As 状态
,A.费用总额
,A.已删除B,DeCode(A.已删除B,0,'否',1,'是') As 已删除
,A.删除人R
,A.删除时间
,A.终端序号I
,A.记账单号I
,A.健康序号i
,A.数据来源
From INS_D检查申请列表 A
Inner Join DOC_T机构列表 B On A.隶属机构I = B.系统序号
Inner Join DOC_B检查类别 C On A.检查类别I = C.系统序号
Inner Join DOC_T员工档案 D On A.申请医师R = D.系统序号
Inner Join DOC_T科室档案 E On A.申请科室I = E.系统序号
Inner Join DOC_T科室档案 F On A.病人科室I = F.系统序号
Left Join DOC_T科室档案 G On A.执行科室I = G.系统序号
Where A.已删除b=0;
 

 

显示打开游标后，并未手动关闭 ，ins_vd检查申请列表视图执行计划显示无效率问题，重点分析大表INQ_D住院医嘱临时列表

被驱动表连接列建索引，过滤列建组合索引

问题1 open rs数据集 未关闭

 

解决方案

关闭 close rs数据集，建立索引及组合索引 后 语句秒出
 create iindex inx_78564_申请时间 on ins_d检查申请列表(申请时间) online;
 create index inx_78476_辅检明显I on inq_d住院医嘱临时列表(辅检明显I) online
 creaee index inx_78476_类别_状态N on inq_d住院医嘱临时列表(医嘱类别，医嘱状态N) online

 

原执行计划SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 3449824932

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 339 | 9700 (1)| 00:01:57 |
| 1 | COUNT | | | | | |
|* 2 | HASH JOIN OUTER | | 1 | 339 | 9700 (1)| 00:01:57 |
|* 3 | HASH JOIN | | 1 | 326 | 9699 (1)| 00:01:57 |
| 4 | NESTED LOOPS | | 1 | 313 | 9698 (1)| 00:01:57 |
| 5 | NESTED LOOPS | | 1 | 313 | 9698 (1)| 00:01:57 |
| 6 | NESTED LOOPS OUTER | | 1 | 299 | 9697 (1)| 00:01:57 |
| 7 | NESTED LOOPS | | 1 | 285 | 9696 (1)| 00:01:57 |
| 8 | NESTED LOOPS OUTER | | 1 | 274 | 9695 (1)| 00:01:57 |
| 9 | NESTED LOOPS | | 1 | 259 | 9694 (1)| 00:01:57 |
|* 10 | HASH JOIN OUTER | | 1 | 249 | 9693 (1)| 00:01:57 |
| 11 | NESTED LOOPS | | 1 | 243 | 9692 (1)| 00:01:57 |
| 12 | NESTED LOOPS | | 1 | 122 | 9688 (1)| 00:01:57 |
|* 13 | TABLE ACCESS BY INDEX ROWID | INS_D检查申请列表| 1 | 89 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_78564_申请时 | 1 | | 3 (0)| 00:00:01 |
| 15 | VIEW PUSHED PREDICATE | | 1 | 33 | 9684 (1)| 00:01:57 |
| 16 | SORT GROUP BY | | 1 | 31 | 9684 (1)| 00:01:57 |
|* 17 | HASH JOIN | | 1 | 31 | 9683 (1)| 00:01:57 |
|* 18 | TABLE ACCESS BY INDEX ROWID| INS_D检查申请明细| 1 | 20 | 2 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | IDX_78568_单据序 | 1 | | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | INQ_D住院医嘱临时| 23676 | 254K| 9681 (1)| 00:01:57 |
| 21 | VIEW | | 1 | 121 | 4 (0)| 00:00:01 |
| 22 | UNION ALL PUSHED PREDICATE | | | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | INQ_D住院档案 | 1 | 46 | 2 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_INQ_D住院档案 | 1 | | 1 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | INQ_D住院档案Z | 1 | 46 | 2 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_INQ_D住院档案Z| 1 | | 1 (0)| 00:00:01 |
| 27 | BITMAP CONVERSION TO ROWIDS | | 2 | 12 | 1 (0)| 00:00:01 |
| 28 | BITMAP INDEX FAST FULL SCAN | DOC_B性别_名称_系| | | | |
| 29 | TABLE ACCESS BY INDEX ROWID | DOC_B检查类别 | 1 | 10 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_DOC_B检查类别 | 1 | | 0 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | INQ_D住院新生儿列| 1 | 15 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_INQ_D住院新生 | 1 | | 0 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案 | 1 | 11 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_T员工档案 | 1 | | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DOC_T病床档案 | 1 | 14 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_T病床档案 | 1 | | 0 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | PK_D健康档案 | 1 | | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | CRM_D健康档案 | 1 | 14 | 1 (0)| 00:00:01 |
| 39 | BITMAP CONVERSION TO ROWIDS | | 97 | 1261 | 1 (0)| 00:00:01 |
| 40 | BITMAP INDEX FAST FULL SCAN | DOC_T科室档案_系 | | | | |
| 41 | BITMAP CONVERSION TO ROWIDS | | 97 | 1261 | 1 (0)| 00:00:01 |
| 42 | BITMAP INDEX FAST FULL SCAN | DOC_T科室档案_系 | | | | |
---------------------------------------------------------------------------------------------------------------

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

2 - access("A"."执行科室I"="G"."系统序号"(+))
3 - access("A"."申请科室I"="E"."系统序号")
10 - access("XB"."系统序号"(+)="B"."性别I")
13 - filter("A"."病人来源"=U'\4F4F\9662' AND "A"."已删除B"=0 AND "A"."病人科室
I" IS NOT NULL)
14 - access("A"."申请时间">=TO_DATE(' 2018-12-04 00:00:00', 'syyyy-mm-dd hh24:
mi:ss') AND
"A"."申请时间"<=TO_DATE(' 2018-12-04 23:59:59', 'syyyy-mm-dd hh24:
mi:ss'))
17 - access("A"."系统序号"="Y"."辅检明细I")
18 - filter("A"."作废B"=0 AND "A"."状态N"<=4)
19 - access("A"."单据序号I"="A"."系统序号")
20 - filter("Y"."辅检明细I" IS NOT NULL AND "Y"."医嘱类别"=U'\68C0\67E5' AND (
"Y"."医嘱状态N"=2 OR
"Y"."医嘱状态N"=3))
24 - access("系统序号"="A"."病人序号I")
26 - access("系统序号"="A"."病人序号I")
30 - access("A"."检查类别I"="C"."系统序号")
32 - access("X"."系统序号"(+)="A"."婴儿序号I")
34 - access("A"."申请医师R"="D"."系统序号")
36 - access("CW"."系统序号"(+)="B"."床位I")
37 - access("C"."系统序号"="A"."健康序号I")

已选择71行。

已用时间: 00: 00: 00.16
SQL>

---------------------------------------------------------------------------------------------------------------------

explain plan for Select
A.系统序号
,A.同步序号
,A.隶属机构i
,A.健康序号i,C.健康ID
,B.住院编号 As 就诊编号
,B.拼音简码
,B.姓名||case when A.婴儿序号i>0 then '['||X.姓名||']' end as 姓名
,Case When A.婴儿序号I>0 Then Null Else getage(B.年龄岁,B.年龄月,B.年龄天,0) End As 年龄
,B.性别i,XB.名称 As 性别
,B.床位i,CW.名称 As 床位
,A.病人来源
,A.病人序号I
,A.急诊b,A.急诊
,A.申请编号
,A.症状及体征
,A.诊断描述
,A.检查类别i,A.检查类别
,A.申请医师R,A.申请医师
,A.申请科室i,A.申请科室
,A.执行科室i,A.执行科室
,A.申请时间
,DECODE(MX.状态n,4,2,3,2,MX.状态n) 状态n,MX.状态
,MX.费用金额 As 费用总额
,A.系统序号||to_char(rownum) AS 唯一标识
From INS_VD检查申请列表 A
Inner Join (
Select 系统序号,姓名,性别i,年龄岁,年龄月,年龄天,当前床位i As 床位i,拼音简码,住院编号 From INQ_D住院档案
Union All
Select 系统序号,姓名,性别i,年龄岁,年龄月,年龄天,当前床位i As 床位i,拼音简码,住院编号 From INQ_D住院档案Z
) B On A.病人序号i=B.系统序号
Inner Join Crm_D健康档案 C On C.系统序号=A.健康序号i
Inner Join (Select A.单据序号i, A.状态N,Decode(A.状态N,0,'申请',1,'记账','已执行') As 状态,sum(A.费用金额) As 费用金额 From INS_D检查申请明细记录 A
Inner Join INQ_D住院医嘱临时列表 Y On A.系统序号=Y.辅检明细I
WHere A.状态n <=4 And A.作废B=0 And Y.医嘱类别='检查' And (Y.医嘱状态N=2 Or Y.医嘱状态N=3)
Group By A.单据序号i,A.状态N) MX On A.系统序号=MX.单据序号i
Left Join INQ_D住院新生儿列表 X On X.系统序号=A.婴儿序号i
Left Join Doc_B性别 XB On XB.系统序号=B.性别i
Left Join Doc_T病床档案 CW On CW.系统序号=B.床位i
Where A.病人来源='住院' AND A.申请时间 BETWEEN to_date('2018-12-04 00:00:00','yyyy-MM-dd HH24:mi:ss') AND 
to_date('2018-12-04 23:59:59','yyyy-MM-dd HH24:mi:ss');

调整后执行计划
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3650771370

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 339 | 22 (5)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | HASH JOIN OUTER | | 1 | 339 | 22 (5)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 326 | 21 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 313 | 20 (5)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 313 | 20 (5)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 299 | 19 (6)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 285 | 18 (6)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 274 | 17 (6)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 259 | 16 (7)| 00:00:01 |
|* 10 | HASH JOIN OUTER | | 1 | 249 | 15 (7)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 243 | 14 (8)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 122 | 10 (10)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | INS_D检查申请列表| 1 | 89 | 4 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_78564_申请时 | 1 | | 3 (0)| 00:00:01 |
| 15 | VIEW PUSHED PREDICATE | | 1 | 33 | 6 (17)| 00:00:01 |
| 16 | SORT GROUP BY | | 1 | 31 | 6 (17)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 31 | 5 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 31 | 5 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID| INS_D检查申请明细| 1 | 20 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX_78568_单据序 | 1 | | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_78476_辅检明 | 1 | | 2 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | INQ_D住院医嘱临时| 1 | 11 | 3 (0)| 00:00:01 |
| 23 | VIEW | | 1 | 121 | 4 (0)| 00:00:01 |
| 24 | UNION ALL PUSHED PREDICATE | | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | INQ_D住院档案 | 1 | 46 | 2 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_INQ_D住院档案 | 1 | | 1 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | INQ_D住院档案Z | 1 | 46 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_INQ_D住院档案Z| 1 | | 1 (0)| 00:00:01 |
| 29 | BITMAP CONVERSION TO ROWIDS | | 2 | 12 | 1 (0)| 00:00:01 |
| 30 | BITMAP INDEX FAST FULL SCAN | DOC_B性别_名称_系| | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | DOC_B检查类别 | 1 | 10 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_DOC_B检查类别 | 1 | | 0 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | INQ_D住院新生儿列| 1 | 15 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_INQ_D住院新生 | 1 | | 0 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | DOC_T员工档案 | 1 | 11 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_T员工档案 | 1 | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | DOC_T病床档案 | 1 | 14 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_T病床档案 | 1 | | 0 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | PK_D健康档案 | 1 | | 0 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | CRM_D健康档案 | 1 | 14 | 1 (0)| 00:00:01 |
| 41 | BITMAP CONVERSION TO ROWIDS | | 97 | 1261 | 1 (0)| 00:00:01 |
| 42 | BITMAP INDEX FAST FULL SCAN | DOC_T科室档案_系 | | | | |
| 43 | BITMAP CONVERSION TO ROWIDS | | 97 | 1261 | 1 (0)| 00:00:01 |
| 44 | BITMAP INDEX FAST FULL SCAN | DOC_T科室档案_系 | | | | |
--------------------------------------------------------------------------------
--------------------------------

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

2 - access("A"."执行科室I"="G"."系统序号"(+))
3 - access("A"."申请科室I"="E"."系统序号")
10 - access("XB"."系统序号"(+)="B"."性别I")
13 - filter("A"."病人来源"=U'\4F4F\9662' AND "A"."已删除B"=0 AND "A"."病人科室
I" IS NOT NULL)
14 - access("A"."申请时间">=TO_DATE(' 2018-12-04 00:00:00', 'syyyy-mm-dd hh24:
mi:ss') AND
"A"."申请时间"<=TO_DATE(' 2018-12-04 23:59:59', 'syyyy-mm-dd hh24:
mi:ss'))
19 - filter("A"."作废B"=0 AND "A"."状态N"<=4)
20 - access("A"."单据序号I"="A"."系统序号")
21 - access("A"."系统序号"="Y"."辅检明细I")
filter("Y"."辅检明细I" IS NOT NULL)
22 - filter("Y"."医嘱类别"=U'\68C0\67E5' AND ("Y"."医嘱状态N"=2 OR "Y"."医嘱状
态N"=3))
26 - access("系统序号"="A"."病人序号I")
28 - access("系统序号"="A"."病人序号I")
32 - access("A"."检查类别I"="C"."系统序号")
34 - access("X"."系统序号"(+)="A"."婴儿序号I")
36 - access("A"."申请医师R"="D"."系统序号")
38 - access("CW"."系统序号"(+)="B"."床位I")
39 - access("C"."系统序号"="A"."健康序号I")