Forum Stats

  • 3,783,670 Users
  • 2,254,818 Discussions
  • 7,880,514 Comments

Discussions

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

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:

When 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.

And here is an information about wait events at moment about ten minutes later from code execution start point.

As 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.

Tagged:

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,852 Gold Crown
    edited Dec 30, 2021 10:08AM Accepted Answer

    You're on 19.9: don't use basicfile, Oracle's advised option for the last few years has been securefile

    If you must stick with basicfile then don't use nologging - that's what triggers the controlfile updates because the control file records the last nologging operation done to a tablespace. (When this overhead was first recorded - some time in 9i or 10g, I think - there was an event that could be set to bypass this operation - this may still be relevant: the event used to be 10359 and could be set at the session level).


    Regards

    Jonathan Lewis


    UPDATE: I've just checked the oraus.msg file for 19.11 and the event number is still there with the following description:

    10359, 00000, "turn off updates to control file for direct writes"
    // *Cause:
    // *Action:  Control files won't get updated for direct writes for  LOBs
    //           when NOCACHE NOLOGGING is set. The only bad impact that it
    //           can have is that if you are using the recovery manager,
    //           it may affect a warning that says that the user should
    //           back everything up. Now the recovery manager won't know
    //           to tell you that the files that were updated with
    //           unrecoverable events should be backed up.
    


    UPDATE 2: The problem was first noted in Oracle 8.0.4; see MOS note:

    EXCESSIVE I/O CONTROLFILE PERFORMANCE PROBLEMS (Doc ID 1058851.6)

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,852 Gold Crown
    edited Dec 30, 2021 10:08AM Accepted Answer

    You're on 19.9: don't use basicfile, Oracle's advised option for the last few years has been securefile

    If you must stick with basicfile then don't use nologging - that's what triggers the controlfile updates because the control file records the last nologging operation done to a tablespace. (When this overhead was first recorded - some time in 9i or 10g, I think - there was an event that could be set to bypass this operation - this may still be relevant: the event used to be 10359 and could be set at the session level).


    Regards

    Jonathan Lewis


    UPDATE: I've just checked the oraus.msg file for 19.11 and the event number is still there with the following description:

    10359, 00000, "turn off updates to control file for direct writes"
    // *Cause:
    // *Action:  Control files won't get updated for direct writes for  LOBs
    //           when NOCACHE NOLOGGING is set. The only bad impact that it
    //           can have is that if you are using the recovery manager,
    //           it may affect a warning that says that the user should
    //           back everything up. Now the recovery manager won't know
    //           to tell you that the files that were updated with
    //           unrecoverable events should be backed up.
    


    UPDATE 2: The problem was first noted in Oracle 8.0.4; see MOS note:

    EXCESSIVE I/O CONTROLFILE PERFORMANCE PROBLEMS (Doc ID 1058851.6)

  • user12024388
    user12024388 Member Posts: 23 Bronze Badge

    Thank you very much, Jonathan!

    After ALTER SYSTEM SET event="10359 trace name context forever, level 1" COMMENT='Disables all updates of unrecoverable SCNs in control file' SCOPE=SPFILE; and restarting of the database instance, the application performance has increased in dozen times.

    I will switch to using securefile, of course.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,852 Gold Crown

    You're welcome.

    Thanks for letting us know it had the desired effect.


    Regards

    Jonathan Lewis