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

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:

Krupesh Desai October 09, 2014 09:39