CSS initialization in non-ASM of a single instance is waiting for event resolution

CSS initialization in non-ASM of a single instance is waiting for event resolution

Environment Description: Oracle 11.2.0.4 Single Instance

Operating System: Microsoft (R) Windows (R) Server 2003 Enterprise x64 Edition

The TOP wait events in the database AWR are as follows:

Total Wait Time (sec)	MS (Wait Avg)	% 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 indicates that a process is registering with CSS. But the database is a single instance and the database does not use ASM. Why does CSS initialization occur?

Further observation shows that although the waiting event does not wait for a long time, each wait event is very long and will definitely affect the system performance, so it must be processed.

Next, we will analyze why this wait event occurs. First, check the alert log of the database and find that there are no related errors in the log. Then, check the wait event in the database for the last time:

SQL> select SQL _id, count (*), sum (time_waited)

From dba_hist_active_sess_history

Where sample_time> to_date ('20140901', 'yyyymmddhh24mi ')

And sample_time <to_date ('20140901', 'yyyymmddhh24mi ')

And event = 'css initialization'

Group by SQL _id;

SQL _ID COUNT (*) SUM (TIME_WAITED)

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

A6w8xy8da-0dpa 5 2026455

4ztfd8f5kk8jf 10 5463613

9jp5bc1p6dnfs 7 4367045

We can observe the SQL _id number, which makes it much easier to handle the problem. Then we can continue to find the corresponding SQL:

SQL> select * from dba_hist_sqltext

Where SQL _id = 'a6w8xy8jw0dpa ';

DBID SQL _ID SQL _TEXT COMMAND_TYPE

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

901678011 a6w8xy81_0dpa select round (sum (FREE_MB)/) as d_asm_free from v $ asm_diskgroup 3

SQL> select * from dba_hist_sqltext

Where SQL _id = '4ztfd8f5k8jf ';

DBID SQL _ID SQL _TEXT COMMAND_TYPE

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

901678011 4ztfd8f5k8jf 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)/, 2) as d_asm_use from v $ asm_disk 3
It is found that the related SQL statements are used to query the v $ asm_diskgroup view, because the query is for the System View and may be executed by the system's built-in job. Then confirm

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 = 'a6w8xy81_0dpa ';

USER_ID USERNAME

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

90 CQITJK

0 SYS

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 = '4ztfd8f5k8jf ';

USER_ID USERNAME

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

90 CQITJK

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 = '9jp5bc1p6dnfs ';

USER_ID USERNAME

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

90 CQITJK
The executor of the first statement contains SYS. This is because I used the SYS user to execute this statement to troubleshoot the problem and found that the problematic SQL statement was executed by the user.

At this point, the problem is basically clear. Because the application queries v $ asm_diskgroup, a CSS initialization wait event is generated. The database is a single instance and does not use ASM. Therefore, the user's query is meaningless. You only need to remove this SQL statement from the developer.

In addition, why does the application query v $ asm_diskgroup for a single instance or non-ASM database? In fact, we can guess that this is a unified development system. To ensure universality, it involves queries on some RAC and ASM views. This query causes problems. Therefore, developers should fully understand the database features.

How to copy data files in ASM to the Operating System

Restoration After all Oracle 11g rac asm disks are lost

Oracle 11g from entry to proficient in PDF + CD source code

Installing Oracle 11g R2 using RHEL6 ASM

Oracle 10g manual creation of the ASM Database

Oracle 10g R2 create ASM instance Step By Step