2 Replies Latest reply: Jan 4, 2013 9:11 AM by sb92075 RSS

    how to analysis long time running statement

    846181
      Hi expert,

      please check following statement, it ran for a long time recent days, will you please instruct how to do analysis to find out the root cause for its long running. in it, function hiroc_get_delta_amount and large table rmv_policy_premium have caused this long runing time, but I still want to get more fault information from system side.

      select
      pp.policy_premium_pk,
      pp.policy_fk,
      pp.policy_term_fk,
      pp.risk_fk,
      pp.coverage_fk,
      pp.transaction_log_fk,
      pp.coverage_component_code,
      hiroc_rpt_user.hiroc_get_delta_amount(pp.policy_fk, pp.policy_term_fk, pp.risk_fk, pp.coverage_fk, pp.transaction_log_fk, pp.coverage_component_code),
      pp.rate_period_from_date
      from proddw_mart.rmv_policy_premium pp
      where pp.rate_period_type_code = 'TERM_COVG'
      and pp.coverage_component_code <> 'NETPREM'
      -- and pp.premium_amount <> 0
      -- *** Following line is included for faster performance
      and hiroc_rpt_user.hiroc_get_delta_amount(pp.policy_fk, pp.policy_term_fk, pp.risk_fk, pp.coverage_fk, pp.transaction_log_fk, pp.coverage_component_code) != 0
      group by pp.policy_premium_pk,
      pp.policy_premium_pk,
      pp.policy_fk,
      pp.policy_term_fk,
      pp.risk_fk,
      pp.coverage_fk,
      pp.transaction_log_fk,
      pp.coverage_component_code,
      pp.rate_period_from_date

      Many Thanks,
        • 1. Re: how to analysis long time running statement
          sb92075
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: how to analysis long time running statement
            BluShadow
            843178 wrote:
            -- *** Following line is included for faster performance
            and hiroc_rpt_user.hiroc_get_delta_amount(pp.policy_fk, pp.policy_term_fk, pp.risk_fk, pp.coverage_fk, pp.transaction_log_fk, pp.coverage_component_code) != 0
            That's an intersting piece of code.

            Apparently, by adding a user defined function into a query, that's going to improve performance according to the comment.
            In truth, it's going to add context switching and slow the query down.

            As well as posting the information required (as detailed by the FAQ posts), you will also need to ensure you provide detials of what that function does. If the functionality can be done in SQL rather than a PL/SQL function, then that will certainly improve things.