Direct path write wait event causes database hang, directhang
My colleague responded that the database seemed to have been suspended for a while more than 10 minutes and asked me to check whether there were any problems with the database.
The first response is to check whether there are any pending events in the current database, and the result is a direct path write wait event.
So I caught the ASH REPORT OF THE PROBLEM time period of 20 minutes and found that direct path write is the first place
Read the SQL statements and the result is these INSERT operations.
After viewing the table structure, the TICKETIMAGE column is of the BLOB type. It is assumed that the direct path write wait event occurs only when the database writes BLOB data to the table without being cached.
Due to the poor I/O performance of the local disk, I decided to disable the oracle feature and find the oracle-related parameters.
Alter system set "_ direct_path_insert_features" = 1;
After a period of observation, we found that the improvement was significant.
Appendix: SQL statement used to view implicit Oracle Parameters
SELECT x. ksppinm NAME, y. ksppstvl VALUE, x. ksppdesc describ
From sys. x $ ksppi x, SYS. x $ ksppcv y
WHERE x. inst_id = USERENV ('instance ')
AND y. inst_id = USERENV ('instance ')
AND x. indx = y. indx
AND x. ksppinm LIKE '% direct %'
/