Home » Applications » Oracle Fusion Apps & E-Business Suite » How to tune tthis Query. Please help on this.
How to tune tthis Query. Please help on this. [message #335591] Tue, 22 July 2008 19:16 Go to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member
Hi Friends

I am facing problem with the query it is taking very long time to populate the date please friends tune this query.

SELECT SUBSTR (hou.NAME, 1, 6) division_number
,ppf.national_identifier ssn
,ppf.last_name last_name
,ppf.first_name first_name
,TO_CHAR (ppf.date_of_birth, 'MM-DD-YYYY') date_of_birth
,TO_CHAR (ppf.start_date, 'MM-DD-YYYY') hire_date
,TO_CHAR (ppos.actual_termination_date, 'MM-DD-YYYY') termination_date
,ppf.marital_status marital_status
,paf.ass_attribute1 highly_compensated
,DECODE (ptp.period_type,'Bi-Week', 26,'Week', '52') pay_periods
,NULL street_address
,NULL apt_number
,NULL city
,NULL state
,NULL zip
,NVL(SUM(DECODE(d.element_name,'NQDC Salary',DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value), 0)
,'NQDC Bonus', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number (prrv.result_value), 0)
,'KLC NQDC Regular 2007', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number (prrv.result_value), 0)
)
),0
) nqdc_ee_value
,NVL(SUM(DECODE(d.element_name,'NQDC Salary ER', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
,'NQDC Bonus ER', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number (prrv.result_value),0)
,'KLC NQDC Regular 2007 ER', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number (prrv.result_value),0)
)
),0
) nqdc_er_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value), 0)
,'KLC 401k', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) s401k_ee_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k ER', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number (prrv.result_value),0)
,'KLC 401k ER', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) s401k_er_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number (prrv.result_value),0)
)
),0
) loan1_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 2', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan2_value
,NVL(SUM(DECODE(d.element_name,'MassMutual 401k Loan', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) mm_loan1_value
,NVL(SUM(DECODE(d.element_name,'MassMutual 401k Loan 2', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) mm_loan2_value
,NVL(SUM(DECODE(d.element_name,'Catchup 401k', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
,'KLC 401k Catchup', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) catchup_401k_value
,NVL(SUM(DECODE(d.element_name,'Catchup 401k ER', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) catchup_401k_er_value
,ppf.person_id
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 3', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan3_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 4', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan4_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 5', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan5_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 6', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan6_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 7', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan7_value
,NVL(SUM(DECODE(d.element_name,'Savings 401k Loan 8', DECODE(feed.balance_type_id, d.element_information10, apps.fnd_number.canonical_to_number(prrv.result_value),0)
)
),0
) loan8_value
FROM apps.pay_run_results prr
,apps.pay_run_result_values prrv
,apps.pay_element_types_f d
,apps.pay_input_values_f e
,apps.pay_element_classifications g
,apps.pay_assignment_actions paa
,apps.pay_balance_feeds_f feed
,apps.per_all_people_f ppf
,apps.per_all_assignments_f paf
,apps.pay_payroll_actions ppa
,apps.hr_all_organization_units hou
,per_time_periods ptp
,per_periods_of_service ppos
WHERE d.classification_id = g.classification_id
AND d.element_type_id = prr.element_type_id
AND d.element_type_id = e.element_type_id
AND prr.assignment_action_id = paa.assignment_action_id
AND ppf.person_id = ppos.person_id
AND paf.period_of_service_id = ppos.period_of_service_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND ppa.effective_date BETWEEN l_start_date AND l_end_date
AND ppa.action_type IN ('Q', 'R', 'V', 'B', 'I')
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_status = 'C'
AND ppa.payroll_id = p_payroll_id
/*IN (SELECT assignment_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ppa.effective_date BETWEEN l_start_date AND l_end_date
AND ppa.action_type IN ('Q', 'R', 'V', 'B', 'I')
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_status = 'C'
AND ppa.payroll_id = p_payroll_id
)*/
AND prrv.run_result_id = prr.run_result_id
AND prr.status IN ('P', 'PA')
AND result_value IS NOT NULL
AND e.input_value_id = prrv.input_value_id
AND NVL (prrv.result_value, '0') <> '0'
AND element_name IN ('NQDC Salary',
'NQDC Salary ER',
'NQDC Bonus',
'NQDC Bonus ER',
'NQDC Severance',
'NQDC Severance ER',
'Savings 401k',
'Savings 401k ER',
'Savings 401k Loan',
'Savings 401k Loan 2',
'MassMutual 401k Loan',
'MassMutual 401k Loan 2',
'Catchup 401k',
'Catchup 401k ER',
'KLC NQDC Regular 2007',
'KLC NQDC Regular 2007 ER',
'KLC 401k',
'KLC 401k ER',
'KLC 401k Catchup',
'Savings 401k Loan 3',
'Savings 401k Loan 4',
'Savings 401k Loan 5',
'Savings 401k Loan 6',
'Savings 401k Loan 7',
'Savings 401k Loan 8'
)
AND ppa.effective_date BETWEEN feed.effective_start_date AND feed.effective_end_date
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND feed.balance_type_id IN (NVL (d.element_information10, '0'), NVL (d.element_information12, '0'), NVL (d.element_information13, '0'))
AND feed.input_value_id = prrv.input_value_id
AND ppf.person_id = paf.person_id
AND paf.assignment_id = paa.assignment_id
AND hou.organization_id = paf.organization_id
AND ppa.time_period_id = ptp.time_period_id
GROUP BY ppf.national_identifier
,ppf.last_name
,ppf.first_name
,ppf.date_of_birth
,ppf.start_date
,ppf.marital_status
,paf.ass_attribute1
,hou.name
,ptp.period_type
,ppos.actual_termination_date
,ppf.person_id;

Here i am passing l_start_date and l_end_date as 01-jan-2008 and 31-dec-2008 and paroll_id is 62 it is taking more than 5 hours to populate the data. Please provide some inputs to tune the query

Thanks
srinivas

Report message to a moderator

Re: How to tune tthis Query. Please help on this. [message #335626 is a reply to message #335591] Wed, 23 July 2008 00:42 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member
First you would need to take the explain plan and trace of the query and see if any full table scans are happening or not. Moreover if any index is used or can be forced to use in the query.

Which version of Oracle is being used? 5 hours in PRODUCTION environment or DEV environment?

Report message to a moderator

Re: How to tune tthis Query. Please help on this. [message #335766 is a reply to message #335591] Wed, 23 July 2008 12:03 Go to previous message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member
thnaks for your replay..

Report message to a moderator

Previous Topic: match a po to an invoice without receiving
Next Topic: Problem in printing log messages in concurrent program log file
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jun 17 03:49:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.