1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

need ur help --tuninging tis query

Discussion in 'SQL PL/SQL' started by vijayspecial, Jul 10, 2013.

  1. vijayspecial

    vijayspecial Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    can anyone suugest some steps to tune the below query????








    start_time := dbms_utility.get_time;
    EXECUTE IMMEDIATE 'SET TRANSACTION USE ROLLBACK SEGMENT RBS_BIG';

    EXECUTE IMMEDIATE 'UPDATE NSS_PIVOT '
    ||'SET WTD ='||v_days_str||',SAME_DAY_LAST_WEEK='||v_processing_day
    ||',WTD_LAST_WEEK='||v_days_str
    ||' WHERE PERIOD_ID ='||v_period_id||' AND VERSION='||v_version_num||' AND (SATURDAY IS NOT NULL OR SUNDAY IS NOT NULL '
    ||' OR MONDAY IS NOT NULL OR TUESDAY IS NOT NULL OR WEDNESDAY IS NOT NULL'
    ||' OR THURSDAY IS NOT NULL OR FRIDAY IS NOT NULL)' ;

    EXECUTE IMMEDIATE 'MERGE INTO NSS_PIVOT MAIN_PVT '
    ||' USING (SELECT NVL('||v_days_str||',0) WTD_LAST_WEEK,'
    ||' '||v_processing_day||' V_DAY,'
    ||' VERSION,PERIOD_ID,OU_CODE,ELEMENT_ID,DATA_TYPE '
    ||' FROM NSS_PIVOT P WHERE PERIOD_ID ='||v_period_id||'-1'
    ||' AND VERSION ='||v_version_num
    ||' AND WTD IS NOT NULL) SUB_PVT '
    ||' ON(MAIN_PVT.OU_CODE=SUB_PVT.OU_CODE '
    ||' AND MAIN_PVT.ELEMENT_ID=SUB_PVT.ELEMENT_ID '
    ||' AND MAIN_PVT.DATA_TYPE=SUB_PVT.DATA_TYPE'
    ||' AND MAIN_PVT.PERIOD_ID=SUB_PVT.PERIOD_ID+1'
    ||' AND MAIN_PVT.VERSION=SUB_PVT.VERSION)'
    ||' WHEN MATCHED THEN'
    ||' UPDATE SET '
    ||' MAIN_PVT.WTD_LAST_WEEK = (NVL(MAIN_PVT.WTD,0))-(SUB_PVT.WTD_LAST_WEEK),'
    ||' MAIN_PVT.SAME_DAY_LAST_WEEK = (CASE WHEN MAIN_PVT.'||v_processing_day||' IS NOT NULL OR SUB_PVT.V_DAY IS NOT NULL'
    ||' THEN NVL(MAIN_PVT.'||v_processing_day||',0)-NVL(SUB_PVT.V_DAY,0)'
    ||' ELSE NULL END)';

    COMMIT;
    --Ver 1.7 Change end for comparision columns
    dbms_output.put_line('***************TIME FOR GLOBAL THREE COLUMN UPDATE>>>>'||((dbms_utility.get_time - start_time)/6000)|| ' minutes');

    -----------------------------------------------------------------------------------------------------------------
    --required daily budget
    --bulk update for budget run rate
    --this column has been renamed to Gross Actuals Vs Rewarded Target
    BEGIN

    EXECUTE IMMEDIATE 'UPDATE NSS_PIVOT '
    ||'SET REQ_DAILY_BUDGET =((((('||v_days_str||')-BUDGET)/BUDGET)+1)*100) '
    ||'WHERE VERSION ='||v_version_num
    ||' AND PERIOD_ID ='||v_period_id||' AND BUDGET > 0';

    COMMIT;

    exception
    WHEN zero_divide
    THEN
    dbms_output.put_line ('Budget(WTD Rewarded Target)- Divided By Zero exception');
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why? What is the reason for this tuning? You give no information other than the dynamic statements -- no timings, no tkprof reports, no AWR or ASH reports, nothing to indicate this block of code actually needs tuning. Query plans would also help here.

    Please post at least some of the requested information because we can 't help you without it.