郑州病历系统登录卡慢问题，初步看是体温单表（t_vital_signs）查询没走索引影响的，800多万的数据中查几千条，从AWR报告和跟病历同事了解的情况看，查询的频次还不低。
数据库卡顿等问题排查顺序：
* 先了解情况，卡顿具体时间，持续时间，是第一次出现还是之前就有出现，只影响本系统还是其他系统也受影响。
1、看监听日志大小是否过大，导致数据库响应过慢并挂起的问题
2、看alert告警日志
3、看实时进程数是否超过150
4、先执行SQL语句初步查看性能低语句，然后生成AWR/ADDM/ASH 报告，详细查看具体情况，定位问题SQL
5、导出效率低的相关表及数据，导入到本地测试库中，进行测试。避免对生产环境造成影响
6、优化效率低的SQL语句
原语句为：
优化该sql语句如下：
说明： MEASURE_TIME字段为timestamp时间锉格式，有隐式转换不能使用to_char转换，不走索引，新建索引并优化语句。
create index INDEX_T_VITAL_SIGNS_TIME5 on T_VITAL_SIGNS (MEASURE_TIME);
select /*+INDEX(t_vital_signs INDEX_T_VITAL_SIGNS_TIME5)*/ * from t_vital_signs
where MEASURE_TIME between to_timestamp ('2018-3-1','yyyy-mm-dd') and to_timestamp ('2018-3-2','yyyy-mm-dd');
优化前：优化前：虽然字段建有索引但还是全表扫描未走索引。
执行计划
----------------------------------------------------------
Plan hash value: 3742512663
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82344 | 7156K| 18109 (3)| 00:03:38 |
|* 1 | TABLE ACCESS FULL| T_VITAL_SIGNS | 82344 | 7156K| 18109 (3)| 00:03:38
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("MEASURE_TIME"),'yyyy-MM-dd')='2018
-02-27')
统计信息
----------------------------------------------------------
29 recursive calls
0 db block gets
65184 consistent gets
64746 physical reads
0 redo size
232528 bytes sent via SQL*Net to client
5056 bytes received via SQL*Net from client
429 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6409 rows processed
优化后：已经走索引，并且取和读的效率明显提升。
执行计划
----------------------------------------------------------
Plan hash value: 1413738788
--------------------------------------------------------------------------------
-------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1673 | 145K
| 436 (0)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_VITAL_SIGNS | 1673 | 145K
| 436 (0)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | INDEX_T_VITAL_SIGNS_TIME5 | 1673 || 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MEASURE_TIME">=TIMESTAMP' 2018-02-27 00:00:00.000000000' AND
"MEASURE_TIME"<=TIMESTAMP' 2018-02-27 23:59:59.000000000')
统计信息
----------------------------------------------------------
29 recursive calls
0 db block gets
1862 consistent gets
220 physical reads
0 redo size
401477 bytes sent via SQL*Net to client
5056 bytes received via SQL*Net from client
429 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6409 rows processed
问题搞定
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29578568/viewspace-2151497/ ，如需转载，请注明出处，否则将追究法律责任。
