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