EBS -- Excessive redo/archive generation due to "Initialization SQL Statement - Custom"

A few days ago, I analyzed an excessive redo generation problem in an EBS 12.2 environment which was recently upgraded from 11i.

The problem was the redo generation, as more than 100Gbs of Redo was generated per day and this has started to become a critical problem since, the dataguard transport was saturating the network.

When we analyzed the generated redologs/archives using Log Miner, we saw the following;

/* No SQL_REDO for temporary tables */

This comment is normally seen when there is a redo generated for a temporary table, so there must be an index on it. Becauase, Oracle don't generate redo  for temporary tables unless there are indexes on them.

In order to find the table, we used the following query;

SELECT sql_redo, table_name, COUNT (*), operation_code
FROM v$logmnr_contents
GROUP BY sql_redo, table_name,operation_code
HAVING COUNT (*) > 10

--operation_code=1 -> insert
--operation_code=2 -> delete

--operation_code=3 -> update

--It was the delete operation causing excessive redo in our case...

So, at the end we concluded the table GL_BIS_SEGVAL_INT for the cause. (the count for GL_BIS_SEGVAL_INT in table v$logmnr_contents was much higher than the others)

The redologs and archives which were created during the problematic period was containing the redo for GL_BIS_SEGVAL_INT.

Using this info, we reached the document named: Table GL.GL_BIS_SEGVAL_INT Generates a Lot of Archive Redo (Doc ID 2161480.1)

The solution suggested in the document was -> setting "Initialization SQL Statement - Custom" to NULL at site level (remove value BEGIN GL_SECURITY_PKG.INIT; END;).

When we checked the  Initialization SQL Statement - Custom, we saw that we had the following set there;

BEGIN IF (fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL') = 0) THEN mo_global.init ('S'); ELSE GL_SECURITY_PKG.init (); mo_global.init ('M'); END IF;END;

This profile was set in order to make Discoverer work properly with MOAC (Multiple Organization Access Control) and Developers didn't want to set it null, as it would affect the Discoverer.

So, rather than setting the "Initialization SQL Statement - Custom" to null, we set it to the following
->

BEGIN IF (fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL') = 0) THEN mo_global.init ('S'); ELSE mo_global.init ('M'); END IF;END;

So, we only remove the GL_SECURITY_PKG.init () from the profile and with this setting; Discoverer could continue to work and the archive/redo generation dramatically decreased and the system went back to normal.

The cause seemed to be the  the GL_SECURITY_PKG.INIT, as it had a delete statement inside "delete from GL_BIS_SEGVAL_INT" and it had insert statement as well..

 sql_stmt := 'INSERT INTO GL_BIS_SEGVAL_INT( ' ||

                    'segment_column_name,' ||

                    'segment_value,      ' ||

                    'parent_segment) ' ||

                    'SELECT ''' || segment_column_name || ''',' ||

                    'flex_value, parent_flex_value_low ' ||

                    'FROM FND_FLEX_VALUES ' ||

                    'WHERE flex_value_set_id=' || value_set_id;

So , these statements were executed in every login (the profile was set in site level) and as the GL_BIS_SEGVAL_INT temporary table had index on it, we ended up with lots of redo generated..

Anways, we implemented the solution and now all is fine :) 

Interesting isn't it :) Sessions doing dml on Global tables do generate redo ( undo for table(just a small amount) + undo for indexes), and setting  "Initialization SQL Statement - Custom" profile to a standard initialization routine can create a big throuble...