PLP_GLRevnGroupAccount_A_Stage_Full Informatica ETL Mapping Slow

In one of the project, we have seen that out of the box mapping PLP_GLRevnGroupAccount_A_Stage_Full in Informaitca was taking really long time. In full ETL, this mapping took almost 30 hours.

After reviewing the Session log, it was found that most of the time was taken in the Reader thread. Since the issue was with the source SQL, the same SQL was run from the SQLPlus and it run in 1 hour only.

After reviewing the informatica configuration, it was found that Informatica server was forced to use some pre-sql to change the Database session settings.

alter session set optimizer_index_cost_adj=1;
alter session set optimizer_index_caching=10;

These session pre-sql change the explain plan for the optimizer and optimizer was not performing well with the Source Query. But it perform well for other mapping.

It was advised to remove these pre-sql at the global level but use at the mapping level if they perform well with these pre-sqls.