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

Thread: sql tuning?

Hybrid View

  1. #1
    Join Date
    Jan 2007
    Posts
    2

    sql tuning?

    Hi

    Please advice me how to tune the following.
    When execute select * from myview it took 45 minutes.

    My view is:
    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%'
    /



    Code for mis_payroll_pack as follows:


    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
    rem
    rem Define Function Body.
    rem ---------------------
    rem
    rem

    CREATE or REPLACE Package body mis_payroll_pack AS

    /*
    FUNCTION period_value

    This Function will return a value which represents the amount
    of Gross pay for a member of staff for a given accounting period.

    The Function is driven by the Budget Centre the staff member belongs
    to si_analysis_code3. There are occasions when there is no Budget
    centre, value is Null. This occurs when the payments are against a
    Balance Sheet code. Where this happens, a DECODE is used to identify
    the Balance Sheet code and use that as a Psyudo Budget Centre!!!
    */

    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 help
    thanks
    hsac

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Can we have some explain plans? Hardware and Software details?
    Assistance is Futile...

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