环境描述:Oracle 11.2.0.4 单实例
操作系统:Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
以下是数据库AWR中的TOP等待事件:
Total Wait Time (sec)Wait Avg(ms)% DB timeWait ClassDB CPU 1546.9 42.7
log file sync 810,467 1440.1 2 39.7 Commit
db file sequential read 72,267 452.5 6 12.5 User I/O
direct path write temp 31,717 220.4 7 6.1 User I/O
CSS initialization 18 18 997 .5 Other
Disk file operations I/O 2,068 14.3 7 .4 User I/O
SQL*Net message to client 2,125,801 7.6 0 .2 Network
direct path read 716 5.9 8 .2 User I/O
control file sequential read 4,450 5.9 1 .2 System I/O
read by other session 236 5.3 23 .1 User I/O
CSS initialization 代表有进程在向CSS进行注册。但数据库是单实例,且数据库也没有使用ASM,那么为什么会出现CSS initialization?
再进一步观察发现该等待事件虽然等待的次数不多,但是每次等待的事件却很长,对系统性能肯定会有影响,所以必须进行处理。
接下来分析为什么会出现该等待事件。首先检查了数据库的alert日志,发现日志里面没有出现相关的错误;再到数据库里检查最近一段时间内出现的该等待事件:
SQL> select sql_id, count(*), sum(time_waited)
from dba_hist_active_sess_history
where sample_time>to_date('201507080000','yyyymmddhh24mi')
and sample_time<to_date('201507081420','yyyymmddhh24mi')
and event='CSS initialization'
group by sql_id;
SQL_ID COUNT(*) SUM(TIME_WAITED)
------------- ---------- ----------------
a6w8xy8jw0dpa 5 2026455
4ztfd8f5kk8jf 10 5463613
9jp5bc1p6dnfs 7 4367045
能观察到sql_id号,这样问题处理就容易多了,再继续把对应的sql找到:
SQL> select * from dba_hist_sqltext
where sql_id='a6w8xy8jw0dpa';
DBID SQL_ID SQL_TEXT COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
901678011 a6w8xy8jw0dpa select round(sum(FREE_MB)/1024,2) as d_asm_free from v$asm_diskgroup 3
SQL> select * from dba_hist_sqltext
where sql_id='4ztfd8f5kk8jf';
DBID SQL_ID SQL_TEXT COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
901678011 4ztfd8f5kk8jf select round( 100-100*sum(FREE_MB)/sum(TOTAL_MB),2) as d_asm_usePer from v$asm 3
SQL> select * from dba_hist_sqltext
where sql_id='9jp5bc1p6dnfs';
DBID SQL_ID SQL_TEXT COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------