Regarding tunning of Query .

[vagdevi]

Hi ,

SELECT ppa.project_type project_type,
'=T("'||ppa.segment1||'")' project_number,
'=T("'||pt.task_number||'")' task_number,
ppa.description project_description,
peia.expenditure_item_date expenditure_item_date,
pcdl.pa_period_name pa_period,
haou.name project_org,
gcc.SEGMENT1 || '.' ||
gcc.SEGMENT3 || '.' ||
gcc.SEGMENT2 || '.' ||
gcc.SEGMENT4 || '.' ||
gcc.segment5 account,
peia.system_linkage_function system_linkage_function,
peia.transaction_source transaction_source,
DECODE(peia.system_linkage_function, 'PJ', pec.expenditure_comment, NULL) description,
DECODE(peia.system_linkage_function, 'PJ', pea.expenditure_group, NULL) pa_expend_batch,
DECODE(peia.system_linkage_function, 'VI', xpi.invoice_num) invoice_num,
DECODE(peia.system_linkage_function, 'VI', xpi.invoice_date) invoice_date,
DECODE(peia.system_linkage_function, 'VI', xpo.po_number) po_number,
DECODE(peia.system_linkage_function, 'VI', NVL(xpo.vendor_name, xpi.vendor_name)) vendor_name,
NVL(peia.burden_cost, 0) burden_cost,
ppa.project_status_code project_status_code,
peia.expenditure_type expenditure_type,
papf.full_name project_manager,
(SELECT ppc_reg.class_code
FROM pa_project_classes ppc_reg
WHERE ppc_reg.project_id = ppa.project_id
AND ppc_reg.class_category = 'Regions') class_code_reg,
(SELECT ppc_dpt.class_code
FROM pa_project_classes ppc_dpt
WHERE ppc_dpt.project_id = ppa.project_id
AND ppc_dpt.class_category = 'Department') class_code_dpt,
ppa.attribute2 attribute2,
(SELECT ppc_afe.class_code
FROM pa_project_classes ppc_afe
WHERE ppc_afe.project_id = ppa.project_id
AND ppc_afe.class_category = 'AFE Year') class_code_afe
,peia.expenditure_item_id expenditure_item_id ----DELETE
FROM pa_projects_all ppa,
pa_project_players ppp,
pa_tasks pt,
pa_expenditure_items_all peia,
pa_cost_distribution_lines_all pcdl,
hr_all_organization_units haou,
gl_code_combinations gcc,
pa_periods pp,
pa_expenditure_comments pec,
xxklc_per_all_people_active papf,
pa_expenditures_all pea,
xxklc_pa_to_invoice_v xpi,
xxklc_pa_to_po_v xpo
WHERE UPPER (NVL(ppa.pm_product_code, 'X')) =
NVL (UPPER (p_name), UPPER (NVL(ppa.pm_product_code, 'X')))
AND ppa.template_flag <> 'Y'
AND ppa.segment1 = NVL (:p_pa_num, ppa.segment1)
AND ppa.project_id = ppp.project_id(+)
AND ppa.project_id = pt.project_id
AND pt.task_id = peia.task_id(+)
AND peia.expenditure_item_id = pcdl.expenditure_item_id(+)
AND pcdl.transfer_status_code in('A','V','R','G')
AND ppa.carrying_out_organization_id = haou.organization_id
AND pcdl.dr_code_combination_id = gcc.code_combination_id(+)
AND pt.task_number = NVL(:p_task, pt.task_number)
AND haou.organization_id = NVL(:p_org, haou.organization_id)
AND pcdl.pa_period_name = pp.period_name(+)
AND pcdl.pa_date BETWEEN NVL(:p_pa_period_low, pcdl.pa_date) AND NVL(:p_pa_period_high, pcdl.pa_date)
AND peia.expenditure_item_id = pec.expenditure_item_id(+)
AND ppp.person_id = papf.person_id(+)
AND ppp.project_role_type(+) = 'PROJECT MANAGER'
AND ppa.project_status_code BETWEEN NVL(:p_status_low, ppa.project_status_code) AND NVL(:p_status_high, ppa.project_status_code)
AND peia.system_linkage_function = NVL(:p_system, peia.system_linkage_function)
AND peia.expenditure_type = NVL(:p_expenditure_type, peia.expenditure_type)
AND ((p_region IS NULL) OR
(SELECT ppc_reg.class_code
FROM pa_project_classes ppc_reg
WHERE ppc_reg.project_id = ppa.project_id
AND ppc_reg.class_category = 'Regions' and ppc_reg.class_code = NVL(:p_region, ppc_reg.class_code)) IS NOT NULL)
AND NVL(papf.full_name, 1) = NVL(p_pm, NVL(papf.full_name, 1))
AND ((:p_department IS NULL) OR
(SELECT ppc_dpt.class_code
FROM pa_project_classes ppc_dpt
WHERE ppc_dpt.project_id = ppa.project_id
AND ppc_dpt.class_category = 'Department' and ppc_dpt.class_code = NVL(p_department, ppc_dpt.class_code)) IS NOT NULL)
AND peia.expenditure_id = pea.expenditure_id
AND peia.expenditure_item_id = xpi.expenditure_item_id(+)
AND peia.expenditure_item_id = xpo.expenditure_item_id(+)
AND pcdl.line_num =
(SELECT MAX(b.line_num)
FROM pa_cost_distribution_lines_all b
WHERE pcdl.expenditure_item_id = b.expenditure_item_id
)
ORDER BY ppa.segment1, pt.task_number, peia.expenditure_item_id, peia.expenditure_item_date;

The above query has 10 input paramerters,when i am passing Project_number(P_PA_NUM) as input and passing all others parameters as null its taking 5secs to execute.But when i am passing all other parameters and giving project number as null it is taking 30 mn to execute.I need to tune the query in 2 scenario that is passing all parameters except project number.

pls.. Advice .

devi