DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: sql tuning

  1. #1
    Join Date
    Jan 2007
    Posts
    2

    sql tuning

    Hi my query
    SELECT *
    FROM dis_pay_det_view;
    taking 45 minutes.

    The explain plain is as attached.

    My View is as dis_pay_det_view as follows:
    CREATE or REPLACE view dis_pay_det_view
    (
    Pay_ref ,
    Pay_Type ,
    Pay_Name ,
    Pay_Bud_Cen ,
    Pay_Value_Aug ,
    Pay_Value_Sep ,
    Pay_Value_Oct ,
    Pay_Value_Nov ,
    Pay_Value_Dec ,
    Pay_Value_Jan ,
    Pay_Value_Feb ,
    Pay_Value_Mar ,
    Pay_Value_Apr ,
    Pay_Value_May ,
    Pay_Value_Jun ,
    Pay_Value_Jul
    )
    AS
    SELECT distinct
    si.si_pay_num ,
    substr(si.si_analysis_code1,1,3) ,
    -- get_staff_cost_type(si.si_analysis_code1) , --
    e.e_name ,
    decode(si.si_analysis_code3,
    null,si.si_analysis_code1,si.si_analysis_code3) ,
    mis_payroll_pack.period_value (
    'AUG-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'SEP-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'OCT-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'NOV-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'DEC-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'JAN-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'FEB-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'MAR-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'APR-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'MAY-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'JUN-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
    mis_payroll_pack.period_value (
    'JUL-06/07' ,
    decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
    si.si_pay_num,substr(si.si_analysis_code1,1,2) )
    FROM mis_salary_items si ,
    mis_employee e
    WHERE si.si_control_ind = 'D'
    AND si.si_pay_num = e.e_emp_ref
    AND si.si_account_period like '%06/07%'
    /
    CREATE or REPLACE Package mis_payroll_pack AS
    FUNCTION period_value
    ( account_period IN CHAR,
    budget_centre IN CHAR,
    staff_ref IN CHAR,
    pay_type IN CHAR
    )
    RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES ( period_value, WNDS, WNPS );
    FUNCTION period_value_nom
    ( account_period IN CHAR,
    budget_centre IN CHAR,
    nominal IN CHAR,
    staff_ref IN CHAR
    )
    RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES ( period_value_nom, WNDS, WNPS );
    END mis_payroll_pack;
    /
    rem Define Function Body.
    CREATE or REPLACE Package body mis_payroll_pack AS
    FUNCTION period_value
    ( account_period IN CHAR,
    budget_centre IN CHAR,
    staff_ref IN CHAR,
    pay_type IN CHAR
    )
    RETURN NUMBER
    IS
    return_period_value NUMBER := 0 ;

    BEGIN
    SELECT nvl(sum(si.si_value), 0)
    INTO return_period_value
    FROM mis_salary_items si
    WHERE si.si_account_period = account_period
    AND decode(si.si_analysis_code3,
    null , si.si_analysis_code1 , si.si_analysis_code3)
    = budget_centre
    AND si.si_pay_num = staff_ref
    AND substr(si.si_analysis_code1,1,2) = pay_type
    AND si.si_month_available = 'Y'
    AND si.si_control_ind = 'D';


    RETURN ( return_period_value );

    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN RETURN(0);
    END;

    /*

    */

    FUNCTION period_value_nom
    ( account_period IN CHAR,
    budget_centre IN CHAR,
    nominal IN CHAR,
    staff_ref IN CHAR
    )
    RETURN NUMBER

    IS
    return_period_value_nom NUMBER := 0 ;
    BEGIN
    SELECT nvl(sum(si.si_value), 0)
    INTO return_period_value_nom
    FROM mis_salary_items si
    WHERE si.si_account_period = account_period
    AND decode(si.si_analysis_code3,
    null , si.si_analysis_code1 , si.si_analysis_code3)
    = budget_centre
    AND si.si_pay_num = staff_ref
    AND si.si_analysis_code1 = nominal
    AND si.si_month_available = 'Y'
    AND si.si_control_ind = 'D';
    RETURN ( return_period_value_nom );
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN RETURN(0);
    END;
    END mis_payroll_pack;

    /

    Please find the attached explain plan file .
    Please advice me to optimize my sql.

    Thanks
    Hsac
    Attached Images Attached Images

  2. #2
    Join Date
    Aug 2002
    Posts
    56
    When was the base tables last analyzed, what was the percentage used, total num of rows for base tables, what version of db, etc.
    When in doubt ...go to the basics!!

Posting Permissions

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


Click Here to Expand Forum to Full Width