一用户进入某界面慢得要死，查看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))”，如果用到该字段的索引，肯定能够提升查询的性能，最后调整后的执行计划如下：
分分钟查询结果出来，问题解决。