2 Replies Latest reply on May 30, 2019 1:52 PM by Rajan Rath

    Performance Improvement

    Vini singh New Member

      Hi,

       

      i have workflow running for long with below statistics:

       

      Severity: INFO

      Timestamp: 06/04/2019 12:49:05 PM

      Node: Production_node02

      Thread: MANAGER

      Process ID: 40204

      Message Code: PETL_24031

      Message:

      ***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****

      Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_CH_TRIL_CANDDT_MATCH] has completed.

      Total Run Time = [2550.187111] secs

      Total Idle Time = [2411.536171] secs

      Busy Percentage = [5.436893]

      Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_CH_TRIL_CANDDT_MATCH] has completed.

      Total Run Time = [2550.249617] secs

      Total Idle Time = [6.375576] secs

      Busy Percentage = [99.750002]

      Thread work time breakdown:

      UN_CH_TRIL_SRC_SYS_CLI: 11.191047 percent

      SRT_CH_TRIL_SRC_SYS_CLI: 88.808953 percent

      Thread [READER_1_2_1] created for [the read stage] of partition point [SQ_CH_tril_src_sys_cli_t] has completed.

      Total Run Time = [10113.850859] secs

      Total Idle Time = [9858.082245] secs

      Busy Percentage = [2.528894]

      Thread [TRANSF_1_2_1] created for [the transformation stage] of partition point [SQ_CH_tril_src_sys_cli_t] has completed.

      Total Run Time = [10485.263569] secs

      Total Idle Time = [175.469990] secs

      Busy Percentage = [98.326509]

      Thread work time breakdown:

      UN_CH_TRIL_SRC_SYS_CLI: 17.993921 percent

      SRT_CH_TRIL_SRC_SYS_CLI: 1.175279 percent

      EXP_CH_TRIL_SRC_SYS_CLI: 0.060790 percent

      LKP_CH_CDW_HSHLD_T: 26.119554 percent

      LKP_CH_CDW_CNSMR_PARTY_T: 27.375887 percent

      LKP_CH_CDW_CIF_PARTY_T: 27.274569 percent

      Thread [WRITER_1_*_1] created for [the write stage] of partition point [T_TRIL_CANDDT_MATCH] has completed.

      Total Run Time = [10477.559841] secs

      Total Idle Time = [10271.297579] secs

      Busy Percentage = [1.968610]

       

       

      It looks like the Sorter transformations is taking the longest. How to i optimize that?

       

      Thanks!

        • 1. Re: Performance Improvement
          Nico Heinze Guru

          From this excerpt of the session log I dare to suppose that the SRT_CH_TRIL_SRC_SYS_CLI needs more than four passes to sort all its input data.

          Please look for messages by this Sorter where it states how much memory it would need for e.g. a 3-pass sort and set its buffer size accordingly. Usually those estimations in the session log are pretty accurate, and a 3-pass sort will usually reduce its total runtime by 80-95% if it ran a 5- or even 6-pass sort.

           

          However, that's not the only important thing. Please take a look at SQ_CH_TRIL_CANDDT_MATCH; this SQ took app. 40 minutes total runtime and was lazy for app. 39 minutes of this time. That's not a good relation. Please check (using the guidelines in the Performance Tuning guide for PowerCenter, part of the PowerCenter documentation set) whether you have a source bottleneck here; in this case you should definitely work with the DB team and the network team to find out whether it's a problem with the source DB and/or a problem with the network route which those data records had to take before being processed by the PowerCenter server.

           

          Even worse is the total runtime for SQ_CH_tril_src_sys_cli_t (almost 3 hours!) and the transformations following this SQ; they take up a really heavy amount of time (again, almost 3 hours!), and the heaviest transformations in this regard are three LKP transformations. You might want to check whether you can increase their buffer memory sizes such that you can increase mapping performance notably here.

           

          Again, the Performance Tuning guide will be your friend here.

           

          Regards,

          Nico

          • 2. Re: Performance Improvement
            Rajan Rath Guru

            Were you able to figure out the issue here? Else you might need to reach out to the Informatica Support team to capture the required information for thorough analysis for a root cause.