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

2019年9月20日 14:56:44Linux大全评论629 views阅读模式

环境描述：Oracle 11.2.0.4 单实例

操作系统：Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition

以下是数据库AWR中的TOP等待事件：

Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait 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

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

901678011 9jp5bc1p6dnfs select round((sum(TOTAL_MB)-sum(FREE_MB))/1024,2) as d_asm_use from v$asm_disk 3
发现相关的sql都是对v$asm_diskgroup视图进行查询，因为查询针对系统视图的，怀疑可能是系统自带的job执行的，接下来进行确认

SQL> select u.user_id,u.username

from dba_users u,

v$sql s

where s.parsing_user_id=u.user_id

and s.sql_id='a6w8xy8jw0dpa';

USER_ID USERNAME

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

90 CQITJK

0 SYS

SQL> select u.user_id,u.username

from dba_users u,

v$sql s