Skip to Main Content

Oracle Database Discussions

"control file parallel write" wait event occurs too frequent on BLOB operations

user12024388Dec 30 2021

Hello!
I would like to transfer BLOB data from one table to another in my Oracle APEX application. Oracle Database version is 19.9.0.0.201020.
Here is my code:
1.jpgWhen I try to execute this code it takes too many time to get completed. I have found that some wait events occur when this code is executed.
Here is an information about wait events at moment about three minutes later from code execution start point.
2.jpgAnd here is an information about wait events at moment about ten minutes later from code execution start point.
3.jpgAs you could see there are a lots of "control file parallel write" events occur. And performance is really poor.
The table where I insert BLOB data is defined as:
CREATE TABLE FILES
(
ID_FILE NUMBER NOT NULL,
FILE_NAME VARCHAR2(128 CHAR),
CONTENT BLOB NOT NULL,
MIME_TYPE VARCHAR2(128 CHAR),
FILE_SIZE NUMBER
)
LOB (CONTENT) STORE AS BASICFILE (
TABLESPACE ASIO_NPI_LOB
ENABLE STORAGE IN ROW
CHUNK 32768
PCTVERSION 10
NOCACHE
NOLOGGING
STORAGE (
INITIAL 100M
NEXT 100M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE ASIO_NPI
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
And tablespace where the LOB segment placed is defined as:
CREATE TABLESPACE ASIO_NPI_LOB DATAFILE '/u02/oradata/CPNDB/asio_npi_lob01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 25G NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
So my question is quite simple: how to decrease "control file parallel write" events occur?
Best regards and hope your help.

This post has been answered by Jonathan Lewis on Dec 30 2021
Jump to Answer
Comments
Post Details
Added on Dec 30 2021
3 comments
300 views