一用户进入某界面慢得要死,查看SQL执行计划如下(具体SQL语句就不完全公布了,截断的如下):
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 9.48 97.10 4222 163337 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 9.48 97.10 4222 163337 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 31
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT ORDER BY (cr=163337 pr=4222 pw=0 time=97103852 us)
0 0 0 HASH GROUP BY (cr=163337 pr=4222 pw=0 time=97103834 us)
0 0 0 NESTED LOOPS (cr=163337 pr=4222 pw=0 time=97103700 us)
0 0 0 NESTED LOOPS (cr=163337 pr=4222 pw=0 time=97103697 us)
6 6 6 NESTED LOOPS (cr=163300 pr=4222 pw=0 time=100235764 us)
373 373 373 FILTER (cr=163298 pr=4222 pw=0 time=159317332 us)
373 373 373 HASH JOIN RIGHT OUTER (cr=163298 pr=4222 pw=0 time=92520264 us)
608 608 608 TABLE ACCESS FULL 病历文件列表 (cr=23 pr=0 pw=0 time=51 us)
373 373 373 TABLE ACCESS FULL 电子病历记录 (cr=163275 pr=4222 pw=0 time=92510188 us)
6 6 6 INDEX UNIQUE SCAN 病区科室对应_PK (cr=2 pr=0 pw=0 time=1713 us)(object id 19144)
0 0 0 TABLE ACCESS BY INDEX ROWID 病人变动记录 (cr=37 pr=0 pw=0 time=478 us)
23 23 23 INDEX RANGE SCAN 病人变动记录_IX_病人ID (cr=15 pr=0 pw=0 time=128 us)(object id 19148)
0 0 0 TABLE ACCESS BY INDEX ROWID 病人信息 (cr=0 pr=0 pw=0 time=0 us)
0 0 0 INDEX UNIQUE SCAN 病人信息_PK (cr=0 pr=0 pw=0 time=0 us)(object id 19355)
可以清楚的看到,2个表全表扫描,而且电子病历记录是一张大表,记录数据接近千万,这样查询要死人的,查看谓语条件,电子病历记录表有“完成时间>=Trunc(Sysdate-(:V001-1))”,如果用到该字段的索引,肯定能够提升查询的性能,最后调整后的执行计划如下:
分分钟查询结果出来,问题解决。
转载请注明出处,否则追究责任