This discussion is archived
2 Replies Latest reply: Jan 4, 2013 7:11 AM by sb92075 RSS

how to analysis long time running statement

846181 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 2. Re: how to analysis long time running statement
    BluShadow Guru Moderator
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points