This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jun 26, 2013 4:37 AM by 826008 RSS

Query Tuning

826008 Newbie
Currently Being Moderated
Hi Experts,

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;


Thanks,
Vijay
  • 1. Re: Query Tuning
    Tubby Guru
    Currently Being Moderated
    823005 wrote:
    Hi Experts,

    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;


    Thanks,
    Vijay
    I can almost guarantee you that the "row_number" has nothing to do with your performance problem.

    I'd wager a small fortune your problem lies within
    AND sc_df_assign.build-dgroup
    (tg.dgroup_target,
    tg.ctannel,
    tg.sales,
    tg.cntry_cd
    )
    Calling custom PLSQL code in SQL statements is extremely expensive. Try to move the logic from that routine in to the SQL statement itself and you'll save a ton of resources.

    Also, please read the FAQ since you are new here.
    https://wikis.oracle.com/display/Forums/Forums+FAQ

    So you can learn how to do useful things like format your code so it's readable.

    Cheers,
  • 2. Re: Query Tuning
    sb92075 Guru
    Currently Being Moderated
    823005 wrote:
    Hi Experts,

    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;


    Thanks,
    Vijay
    post SQL is INVALID syntax & since it can't/won't run there is nothing to tune.


    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 3. Re: Query Tuning
    826008 Newbie
    Currently Being Moderated
    Hi Tubby,

    Thanks for update. Could you please advice how to custom PLSQL code in SQL statements for this query also let me know what is the problem with the below section.

    AND sc_df_assign.build-dgroup
    (tg.dgroup_target,
    tg.ctannel,
    tg.sales,
    tg.cntry_cd
    )


    Thanks,
    vijay
  • 4. Re: Query Tuning
    Tubby Guru
    Currently Being Moderated
    823005 wrote:
    Hi Tubby,

    Thanks for update. Could you please advice how to custom PLSQL code in SQL statements for this query also let me know what is the problem with the below section.
    Obviously not, you didn't post the code for what the function is doing.

    Even then, I don't know your business, your requirements,etc....

    What I suggested (and continue to suggest) is for YOU to look at the logic in your function and attempt to incorporate that into the SQL statement directly, perhaps you need to make a view and join to that, who knows ... we haven't seen the function being called.

    As to the problem with that approach
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:60122715103602

    And in addition, you are calling a PLSQL function and utilizing the results of that function to join your entities, that smells of a bad design (at the data model level, IF this function call is really "needed").

    Cheers,
  • 5. Re: Query Tuning
    826008 Newbie
    Currently Being Moderated

    Hi Tubby,

     

    Thanks for update. 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,

    vijay

  • 6. Re: Query Tuning
    rahulras Explorer
    Currently Being Moderated

    Looking at the code in the function, I don't think you need to call a function in the SQL. You can simply have a CASE statement in place of the function call.

    Also, as you can imagine CASE will not raise error, but you can read back the data you generated and raise error if you find error condition in the data populated.

    Also, I don't know the nature of actual data you are using. If there are small number of distinct inputs and outputs for the function sc_df_assign.build-dgroup (e.g. even if you are processing 1 million records, for the function sc_df_assign.build-dgroup, there are only 200 distinct values are being passed and 200 distinct outputs are possible), in that case you can use result caching (if you are on 11g).

  • 7. Re: Query Tuning
    826008 Newbie
    Currently Being Moderated

    Hi Rahul,

     

    Thanks for your reply. Could you please advice how to place CASE statement in place of the function call also how to use result caching.

     

    Thanks,

    vijay.

  • 8. Re: Query Tuning
    Etbin Guru
    Currently Being Moderated

    As pointed out already: invalid syntax & probable typing errors combined with https://forums.oracle.com/thread/2549068 this is a wild guess (trying to get used to post in renewed forum)

    just trying to get rid of the function

     

    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.cntry_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'

                or  (tg.re_lvl = 'p'

               and   du.udc_lunit_attribute = tg.history_portition

                    )

                   )

           )

    where nbr = 1

     

    hoping predicates might substantially reduce the number of rows from history

     

    with

    history as

    (select dgroup_target,lgroup_source,reassignment_lvl,re_lvl,history_portition,channel,sales,

            case cntry_cd when 'DE'

                          then dgroup_target || '-' || cntry_cd

                          when 'GB'

                          then dgroup_target || channel || '-' || cntry_cd

            end dgroup

       from re_history

      where cntry_cd in (select cntry_cd

                           from job_cfng

                          where script_name = ' script'

                        )

        and (reassignment_lvl = 'A'

         or  re_lvl = 'p'

            )

    )

    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,

                   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 ta.dgroup = tg.dgroup

               and (tg.reassignment_lvl = 'A'

                or  (tg.re_lvl = 'p'

               and   du.udc_lunit_attribute = tg.history_portition

                    )

                   )

           )

    where nbr = 1

     

    Regards

     

    Etbin

  • 9. Re: Query Tuning
    826008 Newbie
    Currently Being Moderated

    Hi Etbin,

     

    Thanks for rewriting the Query. Tried running the following query still it takes 45 minutes to run please advice on this query.

     

    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.cntry_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'

                or  (tg.re_lvl = 'p'

               and   du.udc_lunit_attribute = tg.history_portition

                    )

                   )

           )

    where nbr = 1


    Thanks,

    Vijay

  • 10. Re: Query Tuning
    BrendanP Journeyer
    Currently Being Moderated

    Function calls are more of a problem when they include SQL within the function. You might try using aggregation with the KEEP option instead of row_number and nbr = 1, dropping the outer-level query. Employees example using analytic Row_Number with subquery:

    SELECT employee_id
      FROM (
    SELECT employee_id,
           Row_Number() OVER (PARTITION BY department_id ORDER BY salary DESC) rn
      FROM employees
    )
     WHERE rn = 1
     ORDER BY 1
    

    Equivalent using aggregation with KEEP:

    SELECT Max (employee_id) KEEP (DENSE_RANK LAST ORDER BY salary, ROWID)
      FROM employees
     GROUP BY department_id
     ORDER BY 1
    

    However, there's far too little information to know what is causing the performance issue, and this may not make much difference.

  • 11. Re: Query Tuning
    Etbin Guru
    Currently Being Moderated

    As others have suggested take a look at how to post a SQL statement tuning request https://forums.oracle.com/thread/865295 by Randolf Geist.

    My post was just a wild guess aimed at restore interest in your post as it seemed few others would be willing to look at because of syntax errors and some naming inconsistencies probably due to typing errors.

    Now you seem to have a working query, but just getting rid of the function didn't help much.

    How about the other (another wild guess) query?

    It's more than eight months I'm on Exadata where things are somehow different (trying out scarse index approach) so others would be more competent to suggest how to improve your query, but you must provide them something to look at.

     

    Regards

     

    Etbin

  • 12. Re: Query Tuning
    826008 Newbie
    Currently Being Moderated

    Hi Etbin/Bredan,

     

    Thanks for your reply and suggestions.

     

    Etbin,

     

    The following queries are running separately but when i run in a single query it is not running throwing errors.

     

    (select dgroup_target,lgroup_source,reassignment_lvl,re_lvl,history_portition,channel,sales,

            case cntry_cd when 'DE'

                          then dgroup_target || '-' || cntry_cd

                          when 'GB'

                          then dgroup_target || channel || '-' || cntry_cd

            end dgroup

       from re_history

      where cntry_cd in (select cntry_cd

                           from job_cfng

                          where script_name = ' script'

                        )

        and (reassignment_lvl = 'A'

         or  re_lvl = 'p'

            )

    )

    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,

                   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 ta.dgroup = tg.dgroup

               and (tg.reassignment_lvl = 'A'

                or  (tg.re_lvl = 'p'

               and   du.udc_lunit_attribute = tg.history_portition

                    )

                   )

           )

    where nbr = 1

     


    Thanks,

    vijay.

  • 13. Re: Query Tuning
    Etbin Guru
    Currently Being Moderated

    The following queries are running separately but when i run in a single query it is not running throwing errors.


    you left out with history as

    take a look at subquery_factoring_clause in the SELECT statement http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF01702 to familiarize.

     

    Regards


    Etbin

  • 14. Re: Query Tuning
    826008 Newbie
    Currently Being Moderated

    Hi Etbin,


    We are on 10g Oracle DB we will try the query with history as and i will update the you.


    Thanks,

    Vijay.

1 2 Previous Next

Incoming Links

Legend

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