This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Oct 17, 2013 7:21 AM by Chris Hunt RSS

Please help me to tune this procedure

976208 Explorer
Currently Being Moderated

Hi Experts,

 

 

Please help me to tune this procedure it's taking 30 minute to complete.

 

 

 

 

CREATE OR REPLACE PROCEDURE del_merge_proc

IS

ownername VARCHAR2(30);

mymainquery CLOB;

v_tat_value VARCHAR2(100);

prior_process_exist NUMBER;

prior_process_dt VARCHAR2(100);

 

 

BEGIN

--  DELETES THE RECORDS WHICH ARE OLDER THAN TWO WEEKS.

 

 

DELETE  FROM maint_data

WHERE datatime_dt <=(WITH dates AS (select DECODE(

    TRIM(TO_CHAR(SYSDATE,'DAY')),

    'SATURDAY',1,

    'SUNDAY',2,

    'MONDAY',3,

    'TUESDAY',4,

    'WEDNESDAY',5,

    'THURSDAY',6,

    'FRIDAY',7) daynumber 

FROM dual )

SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7);

COMMIT;

 

 

/* KEEPING THE DATE IN THE STAMPING TABLE WHEN THE PROCEDURE RAN

AND THE NEXT RUN IT SHOULD TAKE THE RECORDS BETWEEN PREVIOUS RUN AND SYSDATE */

 

 

SELECT COUNT(*) INTO prior_process_exist from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';

IF prior_process_exist = 0 THEN

    prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');

    INSERT INTO stamping_tbl(stamp_name,stamp_date)

    VALUES('PROCESS_DONE',prior_process_dt);

ELSE

    SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';

    UPDATE stamping_tbl

    SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')

    WHERE stamp_name='PROCESS_DONE';

END IF;

 

 

FOR schemain IN (SELECT schema_name FROM schema_list  ORDER BY schema_name)

LOOP

SELECT tat_value INTO v_tat_value FROM tat_info_tbl WHERE tat_name='MASTERTAT';

BEGIN

ownername := schemain.schema_name||'.';

 

 

mymainquery := 'MERGE INTO maint_data target

USING (

WITH unitvalues AS (

SELECT MNG_NO, BIDVAL_NO,

    SUM(unitval) unitval,

    SUM(unitrate) unitrate,

    SUM(unitmargin)  unitmargin

FROM '||ownername||'rate_values R

GROUP BY MNG_NO,BIDVAL_NO)

SELECT DISTINCT

    PHD.MNG_NO MNG_NO,

    RL.BIDVAL_NO BIDVAL_NO,

    PHD.ccd_code ccd_code,

    CASE WHEN UPPER(PHD.ccd_code)= UPPER('''||v_tat_value||''')

    THEN 1

    ELSE 2

    END ccd_rate,

    RL.making_name making_name,

    PMR.making_unit making_unit

FROM '||ownername||'ps_hpold_desg PHD

    LEFT OUTER JOIN '||ownername||'ps_manage_rts PMR

    ON PHD.MNG_NO = PMR.MNG_NO

    LEFT OUTER JOIN '||ownername||'rate_values RL

    ON (RL.BIDVAL_NO = PMR.BIDVAL_NO OR PMR.BIDVAL_NO IS NULL) AND PHD.MNG_NO = RL.MNG_NO

    INNER JOIN unitvalues ON unitvalues.MNG_NO=PHD.MNG_NO AND unitvalues.BIDVAL_NO = RL.BIDVAL_NO

    LEFT OUTER JOIN '||ownername||'GEDIS_SALESPERSON GLS ON PHD.LEAD_SALESPERSON = GLS.SALESPERSON_ID

    WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE(

    TRIM(TO_CHAR(SYSDATE,''DAY'')),

    ''SATURDAY'',1,

    ''SUNDAY'',2,

    ''MONDAY'',3,

    ''TUESDAY'',4,

    ''WEDNESDAY'',5,

    ''THURSDAY'',6,

    ''FRIDAY'',7) daynumber 

FROM dual )

SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7)

    AND ('||prior_process_exist||' = 0)

    OR ('||prior_process_exist||' <> 0 AND PHD.UPDATED_TIME BETWEEN TO_DATE('''||prior_process_dt||''',''DD-MON-YYYY HH24:MI:SS'') AND SYSDATE))source

ON(source.MNG_NO = target.tg_MNG_NO

AND source.BIDVAL_NO=target.tg_BIDVAL_NO)

WHEN MATCHED THEN

UPDATE SET

target.tg_ccd_code.ccd_code,

target.tg_ccd_rate =ccd_rate, 

target.tg_making_name = source.making_name,

target.tg_making_unit = source.making_unit

WHEN NOT MATCHED THEN

INSERT

(

target.tg_MNG_NO,

target.tg_BIDVAL_NO,

target.tg_ccd_code,

target.tg_ccd_rate,

target.making_name,

target.making_unit,

)

VALUES

(

source.MNG_NO,

source.tg_BIDVAL_NO,

source.ccd_code,

source.ccd_rate,

source.making_name,

source.making_unit

)';

 

 

EXECUTE IMMEDIATE mymainquery;

COMMIT;

 

 

MERGE INTO maint_data t

USING

(

SELECT * FROM(

WITH dates AS (select DECODE(

    TRIM(TO_CHAR(SYSDATE,'DAY')),

    'SATURDAY',1,

    'SUNDAY',2,

    'MONDAY',3,

    'TUESDAY',4,

    'WEDNESDAY',5,

    'THURSDAY',6,

    'FRIDAY',7) daynumber 

FROM dual )

SELECT (sysdate-(ROWNUM-1)) weekday_name,

CASE WHEN (daynumber)-(ROWNUM-1)<=0 THEN 8

ELSE (daynumber)-(ROWNUM-1)

END weekday_number

FROM dates CONNECT BY ROWNUM <=dates.daynumber+7)

)s

ON(TRUNC(s.weekday_name)=TRUNC(t.datatime_dt))

WHEN MATCHED THEN

UPDATE SET t.QO_SNAPSHOT_ID=s.weekday_number;

EXCEPTION

   WHEN OTHERS

   THEN

      ROLLBACK;

END;

 

 

Thanks.

  • 1. Re: Please help me to tune this procedure
    Anar Godjaev Expert
    Currently Being Moderated

    Hi,

     

    First of all it takes to look at explain plan of it to make query rapidly works. I highly recommend you read about explain Plan. It is so difficult to accelerate speed of the query unless analyze explain plan.

     

    Thank you

  • 2. Re: Please help me to tune this procedure
    Hoek Guru
    Currently Being Moderated

    What are them COMMITS and that ROLLBACK in your WHEN OTHERS meant for?

    Looks like your transaction could break easily that way. I would remove all of them for starters.

    Also, why are you using dynamic SQL?

    Can you (or your DBA) trace (with wait events, level 10046) the execution of your procedure and post the tkprof output along with your database version?

    That way we know exactly where/what we're waiting for and how much time is spent per statement.

     

    Step-by-step instructions:

    Oracle related stuff: Basic SQL statement performance diagnosis - HOW TO, step by step instructions

     

    Message was edited by: Hoek added link

  • 3. Re: Please help me to tune this procedure
    Mike Kutz Expert
    Currently Being Moderated

    commenting on your code in general:

     

    BEGIN

    --  DELETES THE RECORDS WHICH ARE OLDER THAN TWO WEEKS.

     

    This should be nothing more than:

    delete from maint_data where datatime_dt <= sysdate -7

    Oh... datetime_dt better be a DATE data type.  if not, stop working on this procedure and fix it.

     

    SELECT COUNT(*) INTO prior_process_exist from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';

    IF prior_process_exist = 0 THEN

        prior_process_dt := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');

        INSERT INTO stamping_tbl(stamp_name,stamp_date)

        VALUES('PROCESS_DONE',prior_process_dt);

    ELSE

        SELECT stamp_date INTO prior_process_dt from stamping_tbl WHERE stamp_name = 'PROCESS_DONE';

        UPDATE stamping_tbl

        SET stamp_date=TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')

        WHERE stamp_name='PROCESS_DONE';

    END IF;

     

     

     

    1 - you should be able to do this as a MERGE

    2 - the column stamping_tbl.stamp_date better be a date column.  if not, fix this first.

     

    FOR schemain IN (SELECT schema_name FROM schema_list  ORDER BY schema_name)

    LOOP

    SELECT tat_value INTO v_tat_value FROM tat_info_tbl WHERE tat_name='MASTERTAT';

    BEGIN

    ownername := schemain.schema_name||'.';

     

     

    long term -- get EVERYTHING into a single schema.  use VPD to limit who can see what when they log in.

    short term -- use DBMS_ASSERT.schema_name( ) to make sure Bobby Tables doesn't screw things up.

     

    OVERALL

    Make sure the columns that are suppose to hold date/time values are actually DATE data type, not a varchar2 data type.

    You may want to think about splitting up the three segments into three separate procedures so that you can debug/enhance them  separately.

    (they are in a Package... right?  If not, fix that before you start 'tuning')

    As others have said, get rid of the WHEN OTHERS clause(s).

     

    Just my $0.02 worth.

     

    MK

  • 4. Re: Please help me to tune this procedure
    976208 Explorer
    Currently Being Moderated

    Thanks for your suggestions.

     

    Can you please provide me the example for


    long term -- get EVERYTHING into a single schema.  use VPD to limit who can see what when they log in.


    As others have said, get rid of the WHEN OTHERS clause(s).Instead of what exception type should I use?.

    Please confirm.


    In merge statement I am using the below filter.


    WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE(

        TRIM(TO_CHAR(SYSDATE,''DAY'')),

        ''SATURDAY'',1,

        ''SUNDAY'',2,

        ''MONDAY'',3,

        ''TUESDAY'',4,

        ''WEDNESDAY'',5,

        ''THURSDAY'',6,

        ''FRIDAY'',7) daynumber

    FROM dual )

    SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7)

     

    Is it better to use directly the query (or) storing the value of MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60)))  into variable

    and using that variable in filter condition?.

    please confirm.

     

    Thanks.

  • 5. Re: Please help me to tune this procedure
    Etbin Guru
    Currently Being Moderated

    You could simplify the decode(... into to_char(sysdate + 1,'d')

     

    select sysdate + level input_date,

           TRIM(TO_CHAR(SYSDATE + level,'DAY')) the_day,

           DECODE(TRIM(TO_CHAR(SYSDATE + level,'DAY')),

                  'SATURDAY',1,

                  'SUNDAY',2,

                  'MONDAY',3,

                  'TUESDAY',4,

                  'WEDNESDAY',5,

                  'THURSDAY',6,

                  'FRIDAY',7

                 ) daynumber,

           to_char(sysdate + level + 1,'d') alternative

      from dual

     

    INPUT_DATETHE_DAYDAYNUMBERALTERNATIVE
    10/14/2013MONDAY33
    10/15/2013TUESDAY44
    10/16/2013WEDNESDAY55
    10/17/2013THURSDAY66
    10/18/2013FRIDAY77
    10/19/2013SATURDAY11
    10/20/2013SUNDAY22
    10/21/2013MONDAY33
    10/22/2013TUESDAY44
    10/23/2013WEDNESDAY55
    10/24/2013THURSDAY66
    10/25/2013FRIDAY77
    10/26/2013SATURDAY11
    10/27/2013SUNDAY22
    10/28/2013MONDAY33

     

    Regards

     

    Etbin

  • 6. Re: Please help me to tune this procedure
    976208 Explorer
    Currently Being Moderated

    Hi,

     

    Is there any alternative logic for this (TRUNC(s.weekday_name)=TRUNC(t.datatime_dt))

     

    I don't want to consider time from both columns it should compare only date.

     

    Please help me.

     

    Thanks.

  • 7. Re: Please help me to tune this procedure
    Etbin Guru
    Currently Being Moderated

    Is there any alternative logic for this (TRUNC(s.weekday_name)=TRUNC(t.datatime_dt))

    I don't want to consider time from both columns it should compare only date.

    s.weekday_name is a date ? (I find the column name rather confusing )

    If so, you could use s.weekday_name between trunc(t.datetime_dt) and trunc(t.datetime_dt) + 1 - 1/24/60/60

    for making ths optimizer to be able to use an index on s.weekday_name

     

    Regards

     

    Etbin

  • 9. Re: Please help me to tune this procedure
    Solomon Yakobson Guru
    Currently Being Moderated

    a) Missed connect by clause

    b) Could use format modifier FM instead of TRIM. TRIM(TO_CHAR(SYSDATE + level,'DAY')) is nothing but TO_CHAR(SYSDATE + level,'FMDAY')

    c) decode uses english day names which is NLS dependent - should add third parameter nls_date_language=english

    c) to_char(sysdate + level + 1,'d') alternative - is NLS dependent (assumes week starts sunday)

     

    It is much simpler to use NLS independent IW format:

     

    select sysdate + level input_date,

           to_char(sysdate + level,'FMDAY') the_day,

           trunc(sysdate + level) - trunc(sysdate + level + 2,'iw') + 3 daynumber

      from dual

      connect by level <= 14

    /


    INPUT_DAT THE_DAY    DAYNUMBER
    --------- --------- ----------
    14-OCT-13 MONDAY             3
    15-OCT-13 TUESDAY            4
    16-OCT-13 WEDNESDAY          5
    17-OCT-13 THURSDAY           6
    18-OCT-13 FRIDAY             7
    19-OCT-13 SATURDAY           1
    20-OCT-13 SUNDAY             2
    21-OCT-13 MONDAY             3
    22-OCT-13 TUESDAY            4
    23-OCT-13 WEDNESDAY          5
    24-OCT-13 THURSDAY           6

    INPUT_DAT THE_DAY    DAYNUMBER
    --------- --------- ----------
    25-OCT-13 FRIDAY             7
    26-OCT-13 SATURDAY           1
    27-OCT-13 SUNDAY             2

    14 rows selected.

    SQL>

     

    SY.

  • 10. Re: Please help me to tune this procedure
    Etbin Guru
    Currently Being Moderated

    Thanks for the comments. Let's say the missing connect by is due to weekend carelessness (sorry to the OP).

    For the rest I just wanted to suggest a simpler expression can be used instead of the decode(... so I was one way or another obliged to use the original expression in the comparison.

    Generally I also try to aviod NLS dependent expressions but not at all cost (two function calls instead of one) - if Oracle took the effort to include a 2 million nation's language in the NLS it seems just not appropriate not using it.


    Regards


    Etbin

  • 11. Re: Please help me to tune this procedure
    976208 Explorer
    Currently Being Moderated

    In merge statement I am using the below filter.


    WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE(

        TRIM(TO_CHAR(SYSDATE,''DAY'')),

        ''SATURDAY'',1,

        ''SUNDAY'',2,

        ''MONDAY'',3,

        ''TUESDAY'',4,

        ''WEDNESDAY'',5,

        ''THURSDAY'',6,

        ''FRIDAY'',7) daynumber

    FROM dual )

    SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7)

     

    Is it better to use directly the query (or) storing the value of MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60)))  into variable

    and using that variable in filter condition?.

    please confirm.

     

    Thanks in advance.

  • 12. Re: Please help me to tune this procedure
    Etbin Guru
    Currently Being Moderated

    I'm not sure I understand the question, but if you want to do it in SQL then leave it to the query.

     

    Regards

     

    Etbin

  • 13. Re: Please help me to tune this procedure
    Mike Kutz Expert
    Currently Being Moderated

    In SQL or use a Variable?

    Let's take a look at the code from a 'new programmer' point-of-view.

    I have no idea what the scalar SQL statement is trying to calculate.

    I don't know if it is being done correctly.

    I don't know if there is a better way to calculate it.

    I know it is to limit the data, but I don't understand its purpose.

     

    My lack of understanding means one thing:   you do not have enough comments in your code.

     

    Therefore:

    Use a variable with comments.

    In this particular case, the comments (ie what and why ) are more important than the how.

    You may even want to explain the algorithm that you are using to do the calculations.

     

    BTW - Use a BIND variable in your SQL statement

    eg

    my_cutoff_date := -- place calculation here;

    mysql := '.... WHERE PHD.UPDATED_TIME > :X1';

     

    EXECUTE IMMEDIATE mysql USING my_cutoff_date;

    -- no.  you can not use schema_name as a bind variable

     

     

     

    MK

  • 14. Re: Please help me to tune this procedure
    Chris Hunt Journeyer
    Currently Being Moderated

    WHERE PHD.UPDATED_TIME > (WITH dates AS (select DECODE(

        TRIM(TO_CHAR(SYSDATE,''DAY'')),

        ''SATURDAY'',1,

        ''SUNDAY'',2,

        ''MONDAY'',3,

        ''TUESDAY'',4,

        ''WEDNESDAY'',5,

        ''THURSDAY'',6,

        ''FRIDAY'',7) daynumber

    FROM dual )

    SELECT MIN(TRUNC(sysdate-(rownum-1))-(1/(24*60*60))) FROM dates CONNECT BY rownum <=dates.daynumber+7)

     

    This horrible pile of SQL appears to be intended to return the date/time at 23:59:59 a week before the previous Friday. I.e. If run at any time between Sat 12-Oct-2013 and Fri 18-Oct-2013, it'll return 04-Oct-2013 23:59:59. If run on Sat 19-Oct-2013, it'll return 11-Oct-2013 23:59:59.

     

    Two points:

     

    1) If you ever have to write such a complex SQL expression, add a comment to explain what it does for the benefit of the unfortunate programmer who has to maintain this code and won't have a clue what it's supposed to be doing. That "unfortunate programmer" might be you a few months from now, when you've had time to forget everything about your "clever" SQL expression.

     

    2) You don't have to do it in this horrible fasion. You can get the same result from this expression:

    TRUNC(sysdate+3,'IW')-(9+1/(24*60*60))

     

     

    In answer to your question "is it better to put [the above] in a variable", I'd say yes. You're using it in multiple places in your procedure, so calculate it once and reuse it:

    -- v_Two_Weeks_Ago calculated to be 23:59:59 two fridays ago from today

    v_two_weeks_ago := TRUNC(sysdate+3,'IW')-(9+1/(24*60*60));

     

    DELETE  FROM maint_data

    WHERE datatime_dt <= v_Two_Weeks_Ago;

     

    [... etc ...]

1 2 Previous Next

Legend

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