单实例非ASM中 CSS initialization等待事件解决

环境描述: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 Class
DB 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

---------- ------------- -------------------------------------------------------------------------------- ------------

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/3554076a293361a43fe2c12ee408a8b2.html