Results 1 to 9 of 9

Thread: Query Slow

  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: Query Slow

    Hi All,

    Please advice on tuning the following query the query is taking 30 minutes to run.It seems like the row_number() is causing the query slow.

    Query
    --------
    SELECT * FROM (SELECT ta.ROWID rid,
    ta.lunit_soure,
    tg.lgroup_source,
    ta.loc_source,
    tg.ctannel,
    tg.sales,
    row_number () over (PARTITION BY ta.lunit, ta.dgrourp, ta.loc ORDER BY

    tg.re_lvl desc) nbr
    FROM a_hisj ta
    re_history tg
    lunit du
    Dgroup dg
    WHERE du.lunit=ta.lunit
    And dg.dgroup= ta.dgroup
    And tg.dgroup_target=dg.udc_dmdgroup_val
    AND tg.cntry_cd IN (SELECT cntry_cd FROM job_cfng where script name=’ script’)
    AND sc_df_assign.build-dgroup
    (tg.dgroup_target,
    tg.ctannel,
    tg.sales,
    tg.cntry_cd
    ) =ta.dgroup
    AND (tg.reassignment_lvl=’A’
    OR (tg.re_lvl=’p’ AND du.udc_lunit_attribute=tg.history_portition)
    ) Where nbr=1;

    Got the function code could you please advice on rewriting the query.



    Function build_dgroup(p_d mdgroup varchar2 p_channel varchar2 p_sales_org



    varchp_cntry_cd varchar2) return varchar2 IS v_d group varchar2



    BEGIN

    CASE p_entry_cd WHEN 'DE'

    THEN

    v_dmdgroup:=

    p_dmdgroup !!'-'!!

    p_entry_cd;

    WHEN 'GB'

    THEN

    v_dmdgroup:= p_dmdgroup !!

    p_channel !!'-'!!

    p_entry_cd

    ELSE Raise application error

    END CASE;

    RETURN

    v_dmdgroup;


    Thanks,
    Ragu

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since we don't have your tables or data, we can't run, test or improve posted code.

    COPY & PASTE must be broken for you, since posted SQL contains invalid syntax.

    post DDL for all tables & indexes along with EXPLAIN PLAN for slow SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    In addition to what anacedent has written: please format your SQL so that it's readable.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Jun 2013
    Posts
    4
    Hi Anacedent,

    Thanks for your reply. We don't have access to DDL tables also for explain plan i have written the explain plan with me and the indexes is available for the columnsta.lunit_soure,
    tg.lgroup_source,
    ta.loc_source,
    tg.ctannel,
    tg.sales,

    Actually the query is taking 45 minutes could you advice whether it is possible to rewrite the query.

    Thanks,
    Ragu

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Put your case statement directly into the sql replacing your function call (also removing the v_dmdgroup variable). Function calls are very expensive if called lots of times.

    Alan

  6. #6
    Join Date
    Jun 2013
    Posts
    4
    Hi Alan,

    Thanks for update. We tried with CASE statement still the query is taking 45 minutes to run. Please advice.

    select rid,lunit_source,lgroup_source,loc_source,channel, sales

    from (select ta.rowid rid,

    ta.lunit_source,

    tg.lgroup_source,

    ta.loc_source,

    tg.channel,

    tg.sales,

    row_number() over (partition by ta.lunit,ta.dgrourp,ta.loc order by tg.re_lvl desc) nbr

    from a_hisj ta,

    re_history tg,

    lunit du,

    dgroup dg

    where du.lunit = ta.lunit

    and dg.dgroup = ta.dgroup

    and tg.dgroup_target = dg.udc_dmdgroup_val

    and tg.cntry_cd in (select cntry_cd

    from job_cfng

    where script_name = ' script'

    )

    /* and sc_df_assign.build-dgroup(tg.dgroup_target,tg.channel,tg.sales,tg.cnt ry_cd) = ta.dgroup */

    and ta.dgroup = case tg.cntry_cd when 'DE'

    then tg.dgroup_target || '-' || tg.cntry_cd

    when 'GB'

    then tg.dgroup_target || tg.channel || '-' || tg.cntry_cd

    end

    and (tg.reassignment_lvl = 'A'


    Thanks,
    Ragu.
    Last edited by ragupathymca; 06-14-13 at 12:02.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    enabling SQL_TRACE before running the SQL.
    The content of the trace file will show when actual time is being spent.
    Unless & until you know what is taking a long time, you won't know what to focus on to be changed & improved.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2013
    Posts
    4
    Hi Alan,

    We are using TOAD can i set the SQL_TRACE at the session level and run the query?

    Thanks,
    Ragu

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alter session set sql_trace=true;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •