We're here to help!

CTAS(Create Table As Select) and APPLY Out Of Memory!

Follow
Problem Description

CTAS (Create Table As Select) is a very inefficient operation when it comes to replication. The reason is that the information is not in the redo logs to MINE and so all the data has to be retrieved from the table and put in a special plog. This is why you will see comparatively bigger plog created during such CTAS operations.

This process also uses large amounts of memory on the APPLY side and this is why you are seeing that APPLY is running out of memory.

Solution

There are 2 workarounds:

A: Increase the memory of the APPLY process:

  • Edit the file *-APPLY.ddc on the target side after the line
    ########## Any manual settings should go below this line ##########
  • Add the following:
    set MEMORY_LIMIT_APPLY_MB 4096
  • Restart the APPLY process.

or

B: Make your SQL more efficient for Replication:

  • Change your SQL to create an empty copy of the table:
    CREATE TABLE TEST_TABLE_01 AS
    SELECT * FROM SOME_TABLE
    WHERE 1=0;
  • Then load the table with the following SQL:

    INSERT INTO TEST_TABLE_01 SELECT * FROM SOME_TABLE;

Krupesh Desai October 09, 2014 09:39

Have more questions? Submit a request

Comments