Oracle Direct IO+ASM causes CSS initialization

A database upgrade to 12c (the application code has also been upgraded), there are a lot of CSS initialization waiting:

Sql> Select Event,sql_id,count (*) from v$session
Where event= ' CSS initialization ' GROUP by event,sql_id;



EVENT sql_id COUNT (*)
---------------------- ------------- ----------
CSS Initialization 1QKDW0DTYJYDF 5
CSS Initialization 2g9g4as2nasad 1
CSS Initialization 5r5mwas0m4mxq 1
CSS Initialization 712df0y5u236f 529
CSS Initialization Aasd9k80jrvax 342
CSS Initialization A4uvs112123q6 1
CSS Initialization C5ax6wp98nam4 1
CSS Initialization Fsadasd6asd12 4

A new feature that doubts whether 12c is causing.

CSS Initialization Description:
In a RAC (or single-instance) database environment where the current session needs to perform a direct IO operation, the CSSD process needs to be registered, when the foreground process takes place with CSS initialization waiting.
In 11g or 12c, the trigger principle of CSS initialization has not changed, the event is the expected behavior of a direct IO, any foreground process needs to do direct IO, must be a CSS registration, The direct IO operation can then be allowed.

We know that in the case of LOB objects, the first operation is direct IO, subsequent operations, to see whether the LOB object has cache, if there is cache, then direct OI will not be, and will not be CSS initialization.

If there is no cache, then each DML operation will be a CSS initialization. Then there will be a situation like this customer encountered, large concurrency, a large number of processes in the CSS initialization waiting, and cssd.bin process CPU utilization will become very high.

Therefore, we do not recommend adding LOB fields to the core business tables that are frequently manipulated. If you do need a LOB field, you need to use the cache attribute. Note that this is the cache for the LOB object, not the cache property of the table. I made a mistake, a slight difference caused the cache to be on the table, not the LOB object, so no matter how the test, can not be back to the customer scene.

The table I created is as follows:

CREATE TABLE Wrong_tab_securefile_cache (
ID number,
Clob_data CLOB
)
LOB (Clob_data) STORE as Securefile cache
Tablespace users;

The correct table is established in the following ways:

CREATE TABLE Tab_securefile_cache (
ID number,
Clob_data CLOB
)
LOB (Clob_data) STORE as Securefile (cache)
Tablespace users;

Just there is no difference between parentheses, that is, one is cache, one is (cache).

But if you use dbms_metadata for analysis, you can clearly see the difference between them:

Sql> Select Dbms_metadata.get_ddl (' TABLE ', ' wrong_tab_securefile_cache ', ' SYS ') from DUAL;

Dbms_metadata. GET_DDL (' TABLE ', ' wrong_tab_securefile_cache ', ' SYS ')
--------------------------------------------------------------------------------

CREATE TABLE "SYS". Wrong_tab_securefile_cache "
("ID" number,
"Clob_data" CLOB
) PCTFREE pctused Initrans 1 Maxtrans 255
Nocompress LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1
Buffer_pool default Flash_cache default Cell_flash_cache default)
Tablespace "USERS"
LOB ("Clob_data") STORE as Securefile (
Tablespace "USERS" ENABLE STORAGE in ROW CHUNK 8192
NoCache LOGGING nocompress keep_duplicates
STORAGE (INITIAL 106496 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0
Buffer_pool default Flash_cache default Cell_flash_cache default))
CACHE


SQL >
Sql> Select Dbms_metadata.get_ddl (' TABLE ', ' tab_securefile_cache ', ' SYS ') from DUAL;

Dbms_metadata. GET_DDL (' TABLE ', ' tab_securefile_cache ', ' SYS ')
--------------------------------------------------------------------------------

CREATE TABLE "SYS". Tab_securefile_cache "
("ID" number,
"Clob_data" CLOB
) PCTFREE pctused Initrans 1 Maxtrans 255
Nocompress LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1
Buffer_pool default Flash_cache default Cell_flash_cache default)
Tablespace "USERS"
LOB ("Clob_data") STORE as Securefile (
Tablespace "USERS" ENABLE STORAGE in ROW CHUNK 8192
CACHE nocompress Keep_duplicates
STORAGE (INITIAL 106496 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0
Buffer_pool default Flash_cache default Cell_flash_cache default))


Sql>

Line 21st and 41 can see the difference, the first cache property is added to the table, the second table cache property is added to the LOB.

So, if we add LOB objects to the cache, we don't suffer from CSS initialization so violently.

Finally, the customer is resolved by changing the LOB field to the Varchar2 field.