Luis,

After I analyzed the next slow block (let's call it "GMS2 INSERT"),

I shifted my focus to much bigger issue (in my opinion).

It is very similar to my original query I posted above (let's call it "IDS INSERT").

The pattern is the same.

Just small variations (Source_Code, etc...)

Look at the query.

The output is:

DISTINCT

CLIENT_SHARED.SOURCE_CODE

,CLIENT_SHARED.CUSTOMER_NUMBER. . . .

Without DISTINCT

it returns around 25,000 records.

With DISTINCT it is 127 rows !

I digged in and found out that JOIN between

S_CLIENT_SHARED

and

S_CLIENT_ACCOUNT_ASSOCIATION_SHARED

is not correct.

It is only on 2 columns:

SOURCE_CODE

CUSTOMER_NUMBER

It should be:

SOURCE_CODE

CUSTOMER_NUMBER

BUS_PROC_DT

I already tested.

Instead on 36 min, now it runs 1.5 min

and the output is identical to original query.

The problem is SQL is generated dynamically from

[FATCA_TDS.T_ETL_SP_SS_TRANSFORMER_DML] table

based on very convoluted looping mechanism

in SP_SS_TRANSFORMER stor proc.

It is very risky to make the change.

So far I found this pattern in 79 records of

[FATCA_TDS.T_ETL_SP_SS_TRANSFORMER_DML] table.

This would be my last option.

I wonder though..

This test I'm doing is in PAT environment.

Why in PROD this original "GMS2 INSERT" piece of code runs only 2 minutes?

In PAT - it runs 36 minutes!

PAT and PROD data volumes are almost identical.

We copied PROD to PAT

two weeks ago.

Does it have anything to do with Memory?

PAT server: 16 GB RAM

PROD server: 32 GB RAM

CPU is same.

Disk parameters are close .