0

I have a same session running in production and UAT.All it does is seslects the data ( around 6k in both environments).Expression transformation (to hard code few columns) and then inserting into a table ( which does not have partitions).

The problem I am facing is PROD session is taking more than 30 minutes where as UAT is done within 5 minutes.

I have backtracked the timining to many days and its following the same pattern.When compared the session properties between the two.There is no difference at all.

When checked the session log its the reading of rows which is taking time(same count and query in UAT also)Could you please let me know how to proceed with this:

PROD:
Severity    Timestamp   Node    Thread  Message Code    Message
INFO    4/26/2016 11:07:18 AM   node02_WPPWM02A0004 WRITER_1_*_1    WRT_8167    Start loading table [FACT_] at: Tue Apr 26 01:37:17 2016
INFO    4/26/2016 11:26:48 AM   node02_WPPWM02A0004 READER_1_1_1    BLKR_16019  Read [6102] rows, read [0] error rows for source table [STG_] instance name [STG]

UAT:
Severity    Timestamp   Node    Thread  Message Code    Message
INFO    4/26/2016 11:40:53 AM   node02_WUPWM02A0004 WRITER_1_*_1    WRT_8167    Start loading table [FACT] at: Tue Apr 26 01:10:53 2016
INFO    4/26/2016 11:43:10 AM   node02_WUPWM02A0004 READER_1_1_1    BLKR_16019  Read [6209] rows, read [0] error rows for source table [STG] instance name [STG]
Maciejg
  • 3,088
  • 1
  • 17
  • 30
karthik adiga
  • 133
  • 2
  • 12

2 Answers2

1

Follow the below steps 1) Open the session log and search for 'Busy'

2) Find the Busy statistics which has a very high Busy Percentage

3) if it is with reader , just run the query in production and UA and try to check the retrieval time. If its high in production then there is a need to tune the query or create indexes or create partitions at table level and informatica level etc., (depend on your project limitations)

4) if it is writer try to increase few informatica options like 'Maximum memory allocated for auto memory attributes' and 'Maximum percentage of total memory allowed..." depending on your server configuration

5) Also try to use informatica partitions while loading into target (Provided the target is partitioned on a particular column)

6) Also some times there is a possibility that cache creation takes time due to huge tables being used as lookups( Refer busy percentage of lookup also). In that case also target waits for the rows to come to the writer thread as they are still transforming

we need to tune the lookup by overriding the default query with tuned version of query

Also search for the following keywords "Timeout based Commit point" - generally occurs when a writer thread waits for long time

"No more lookup cache " - generally occurs whenever there is a huge data and index cache to be build and no space available on the disk as multiple jobs will be running in production utilizing the same cache folder

Thanks and Regards

Raj

Rajendra Kalepu
  • 99
  • 7
0

Perhaps, you should check the Query's Explain plan in UAT and PROD. Working on the plan in PROD can help. Similar thing happen with me earlier. We checked the SQL plan and found that it is different in prod when compared to UAT. Had to work with the DBA's to change the plan.

Ajay
  • 1
  • 2