郑州病历系统登录卡慢问题,初步看是体温单表( 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博客 ” ,链接:http://blog.itpub.net/29578568/viewspace-2151497/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29578568/viewspace-2151497/