SQL query tuning
Hi
I need to tune this query. It is taking long time to execute this query. can someone help.
select paaf.person_id
,papf.full_name
,papf.employee_number
,hta.attribute1 project_id
,pap.segment1 project_number
,pap.name project_name
,htb0.stop_time timecard_week
--,Sum(htb.measure) HOURS
FROM
hxc.hxc_time_attributes hta
,hxc.hxc_time_attribute_usages htau
,hxc.hxc_time_building_blocks htb -- detail
,hxc.hxc_time_building_blocks htb1 -- day
,hxc.hxc_time_building_blocks htb0 -- header
,per_all_people_f papf
,hr.per_all_assignments_f paaf
,pa.pa_projects_all pap
WHERE
paaf.person_id = papf.person_id
AND htb0.resource_id = paaf.person_id
AND htb1.parent_building_block_id = htb0.time_building_block_id
AND htb1.PARENT_BUILDING_BLOCK_OVN = htb0.OBJECT_VERSION_NUMBER
AND htb.PARENT_BUILDING_BLOCK_ID = htb1.TIME_BUILDING_BLOCK_ID
AND htb.PARENT_BUILDING_BLOCK_OVN = htb1.OBJECT_VERSION_NUMBER
AND htb.time_building_block_id = htau.time_building_block_id
AND htb.object_version_number = htau.time_building_block_ovn
AND htau.time_attribute_id = hta.time_attribute_id
AND hta.ATTRIBUTE_CATEGORY = 'PROJECTS'
AND htb.date_to = hr_general.end_of_time
AND htb.SCOPE = 'DETAIL'
AND htb.resource_type = 'PERSON'
AND htb1.date_to = hr_general.end_of_time
AND htb1.SCOPE = 'DAY'
AND htb1.resource_type = 'PERSON'
AND htb0.approval_status in ('SUBMITTED','APPROVED')
AND htb0.date_to = hr_general.end_of_time
AND htb0.SCOPE = 'TIMECARD'
AND htb0.resource_type = 'PERSON'
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
AND paaf.assignment_type = 'E' AND paaf.primary_flag = 'Y'
AND trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
AND (paaf.person_id = 146800)
AND hta.attribute1 = pap.project_id
AND pap.segment1 <> nvl('','39907')
order by htb0.stop_time desc
Explain Plan
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 97
SORT ORDER BY 1 273 97
NESTED LOOPS 1 273 73
NESTED LOOPS 1 237 72
NESTED LOOPS 1 216 70
NESTED LOOPS 1 201 62
NESTED LOOPS 1 161 54
NESTED LOOPS 1 121 46
NESTED LOOPS 1 70 16
TABLE ACCESS BY INDEX ROWID HR.PER_ALL_ASSIGNMENTS_F 3 75 10
INDEX RANGE SCAN HR.PER_ASSIGNMENTS_F_N12 10 2
TABLE ACCESS BY INDEX ROWID HR.PER_ALL_PEOPLE_F 1 45 2
INDEX RANGE SCAN HR.PER_PEOPLE_F_PK 1 1
TABLE ACCESS BY INDEX ROWID HXC.HXC_TIME_BUILDING_BLOCKS 1 51 30
INDEX RANGE SCAN HXC.BIO_HXC_TIME_BUILDING_BLKS_N1 44 14
TABLE ACCESS BY INDEX ROWID HXC.HXC_TIME_BUILDING_BLOCKS 1 40 8
INDEX RANGE SCAN HXC.HXC_TIME_BUILDING_BLOCKS_FK3 1 2
TABLE ACCESS BY INDEX ROWID HXC.HXC_TIME_BUILDING_BLOCKS 1 40 8
INDEX RANGE SCAN HXC.HXC_TIME_BUILDING_BLOCKS_FK3 1 2
TABLE ACCESS BY INDEX ROWID HXC.HXC_TIME_ATTRIBUTE_USAGES 2 30 8
INDEX RANGE SCAN HXC.HXC_TIME_ATTRIBUTE_USAGES_FK2 1 2
TABLE ACCESS BY INDEX ROWID HXC.HXC_TIME_ATTRIBUTES 1 21 2
INDEX UNIQUE SCAN HXC.HXC_TIME_ATTRIBUTES_PK 1 1
TABLE ACCESS BY INDEX ROWID PA.PA_PROJECTS_ALL 1 36 1
INDEX UNIQUE SCAN PA.PA_PROJECTS_U1 1