Hi,
I'm trying to tune this query below:
select ppf.employee_number
,ppf.full_name
,ppf.national_identifier
,ppf.ATTRIBUTE1 nssf_no
,ppf.ATTRIBUTE2 nhif_no
,ppf.ATTRIBUTE3 pin_no
,paf.assignment_id
,LOCATION_CODE location_code
,ftt.territory_short_name country
,hou.name org
,pap.name position
,pg.name grade
,ftt.territory_short_name
,ptp.period_name
,pay.payroll_name
,to_char(ptp.start_date,'Month-YYYY') Pay_Month
,ppf.person_id
,xppv.country_payroll_short_name Payroll_curr
from per_assignments_f paf
,per_people_f ppf
,hr_locations hl
,pay_payrolls_f pay
,per_time_periods ptp
,pay_payroll_actions ppa
,pay_run_results prr
,pay_element_types_f petf
,pay_element_classifications pec
,pay_run_result_values prrv
,pay_input_values_f piv
,per_grades pg
,hr_organization_units hou
,per_positions pap
,fnd_territories_tl ftt
,xxbri_payroll_payments_v xppv
where paf.person_id=ppf.person_id
and paf.location_id=hl.location_id
and paf.payroll_id = pay.payroll_id
and paf.payroll_id = ptp.payroll_id
and paf.payroll_id = ppa.payroll_id
and ptp.time_period_id = ppa.time_period_id
and petf.element_type_id = prr.element_type_id
and petf.classification_id = pec.classification_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = petf.element_type_id
and piv.name = 'Pay Value'
and ppa.action_type IN ('Q', 'R')
and pec.classification_name like '%Deductions%'
and prrv.result_value !='0'
and trunc(ppa.effective_date) between
paf.effective_start_date and
paf.effective_end_date
and trunc(ppa.effective_date) between
ppf.effective_start_date and
ppf.effective_end_date
and trunc(ppa.effective_date) between
pay.effective_start_date and
pay.effective_end_date
and trunc(ppa.effective_date) between ptp.start_date
and ptp.end_date
and trunc(ppa.effective_date) between
petf.effective_start_date and
petf.effective_end_date
and ptp.TIME_PERIOD_ID IN (select time_period_id
from per_time_periods
where payroll_id = paf.payroll_id
and PERIOD_NAME= '9 2016 Calendar Month' )
and paf.person_id = 496
and paf.grade_id = pg.grade_id(+)
and paf.organization_id = hou.organization_id
and paf.position_id = pap.position_id(+)
and hl.country=ftt.territory_code(+)
and ftt.LANGUAGE='US'
and paf.payroll_id=xppv.payroll_id
group by ppf.employee_number
,ppf.full_name
,ppf.national_identifier
,ppf.ATTRIBUTE1
,ppf.ATTRIBUTE2
,ppf.ATTRIBUTE3
,paf.assignment_id
,hl.location_code
,hou.name
,pap.name
,pg.name
,ftt.territory_short_name
,ptp.period_name
,pay.payroll_name
,to_char(ptp.start_date,'Month-YYYY')
,ppf.person_id
,xppv.country_payroll_short_name
This query takes over 10 minutes to run, sometimes timing out.
I've attached the full SQL Tuning advisor report, but just for comparison, I'm pasting the old plan and the new recommended plan:
1- Original
-----------
Plan hash value: 3914474287
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 791 | 270 (2)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 791 | 270 (2)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 791 | 269 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 656 | 266 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 536 | 264 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 459 | 262 (1)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 411 | 261 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 374 | 260 (1)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 364 | 258 (1)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 348 | 30 (7)| 00:00:01 |
| 10 | MERGE JOIN | | 1 | 307 | 28 (8)| 00:00:01 |
| 11 | SORT JOIN | | 1 | 267 | 14 (8)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 267 | 13 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 267 | 13 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 251 | 11 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 190 | 9 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 162 | 8 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 127 | 7 (0)| 00:00:01 |
| 18 | NESTED LOOPS OUTER | | 1 | 106 | 6 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 98 | 5 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 56 | 4 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID BATCHED| PER_ALL_ASSIGNMENTS_F | 1 | 49 | 3 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | PER_ASSIGNMENTS_F_N12 | 1 | | 2 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 7 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 42 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | PER_GRADES | 1 | 8 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PER_GRADES_PK | 1 | | 0 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL | 1 | 21 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | HR_LOCATIONS_PK | 1 | | 0 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | FND_TERRITORIES_TL | 1 | 35 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | FND_TERRITORIES_TL_U1 | 1 | | 0 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL_TL | 1 | 28 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | HR_LOCATIONS_ALL_TL_PK | 1 | | 0 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_ALL_PAYROLLS_F | 1 | 61 | 2 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | PAY_PAYROLLS_F_PK | 1 | | 1 (0)| 00:00:01 |
| 37 | INLIST ITERATOR | | | | | |
|* 38 | INDEX RANGE SCAN | PAY_PAYROLL_ACTIONS_N51 | 1 | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | PAY_PAYROLL_ACTIONS | 1 | 16 | 2 (0)| 00:00:01 |
|* 40 | FILTER | | | | | |
|* 41 | SORT JOIN | | 1147 | 45880 | 14 (8)| 00:00:01 |
| 42 | VIEW | index$_join$_033 | 1147 | 45880 | 13 (0)| 00:00:01 |
|* 43 | HASH JOIN | | | | | |
| 44 | INDEX FAST FULL SCAN | PAY_ELEMENT_TYPES_F_FK1 | 1147 | 45880 | 5 (0)| 00:00:01 |
|* 45 | INDEX FAST FULL SCAN | PAY_ELEMENT_TYPES_F_PK | 1147 | 45880 | 11 (0)| 00:00:01 |
| 46 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_INPUT_VALUES_F | 1 | 41 | 2 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | PAY_INPUT_VALUES_F_UK2 | 1 | | 1 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_RUN_RESULT_VALUES | 773 | 12368 | 228 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | PAY_RUN_RESULT_VALUES_PK | 836 | | 6 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | PAY_RUN_RESULTS | 1 | 10 | 2 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | PAY_RUN_RESULTS_PK | 1 | | 1 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID | PAY_ELEMENT_CLASSIFICATIONS | 1 | 37 | 1 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PAY_ELEMENT_CLASSIFICATION_PK | 1 | | 0 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PER_TIME_PERIODS | 1 | 48 | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | PER_TIME_PERIODS_PK | 1 | | 0 (0)| 00:00:01 |
|* 56 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_ALL_PAYROLLS_F | 1 | 77 | 2 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | PAY_PAYROLLS_F_PK | 1 | | 1 (0)| 00:00:01 |
|* 58 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 120 | 2 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 1 (0)| 00:00:01 |
| 60 | VIEW PUSHED PREDICATE | PER_POSITIONS | 1 | 135 | 3 (0)| 00:00:01 |
| 61 | NESTED LOOPS | | 1 | 59 | 3 (0)| 00:00:01 |
| 62 | TABLE ACCESS BY INDEX ROWID | HR_ALL_POSITIONS_F_TL | 1 | 51 | 2 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | HR_ALL_POSITIONS_F_TL_PK | 1 | | 1 (0)| 00:00:01 |
|* 64 | TABLE ACCESS BY INDEX ROWID | PER_ALL_POSITIONS | 1 | 8 | 1 (0)| 00:00:01 |
|* 65 | INDEX UNIQUE SCAN | PER_POSITIONS_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------------
This is the new recommended plan:
3- Using SQL Profile
--------------------
Plan hash value: 2081844926
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 704 | 342 (2)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 704 | 342 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 704 | 341 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 704 | 341 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 694 | 339 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 678 | 111 (3)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 637 | 109 (3)| 00:00:01 |
| 7 | MERGE JOIN | | 1 | 600 | 108 (3)| 00:00:01 |
| 8 | SORT JOIN | | 1 | 560 | 94 (3)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 560 | 93 (2)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 560 | 93 (2)| 00:00:01 |
|* 11 | HASH JOIN OUTER | | 1 | 518 | 92 (2)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 470 | 69 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 470 | 69 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 463 | 68 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 428 | 67 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 2 | 824 | 64 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 2 | 768 | 62 (0)| 00:00:01 |
| 18 | NESTED LOOPS OUTER | | 2 | 726 | 60 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 2 | 710 | 59 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 2 | 588 | 57 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 2 | 434 | 53 (0)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN | | 2 | 338 | 5 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | PER_ALL_ASSIGNMENTS_F | 1 | 49 | 3 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | PER_ASSIGNMENTS_F_N12 | 1 | | 2 (0)| 00:00:01 |
| 25 | BUFFER SORT | | 2 | 240 | 2 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID BATCHED| PER_ALL_PEOPLE_F | 2 | 240 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_TIME_PERIODS | 1 | 48 | 24 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PER_TIME_PERIODS_N51 | 840 | | 3 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_ALL_PAYROLLS_F | 1 | 77 | 2 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | PAY_PAYROLLS_F_PK | 1 | | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_ALL_PAYROLLS_F | 1 | 61 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | PAY_PAYROLLS_F_PK | 1 | | 1 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | PER_GRADES | 1 | 8 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PER_GRADES_PK | 1 | | 0 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL | 1 | 21 | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | HR_LOCATIONS_PK | 1 | | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL_TL | 1 | 28 | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | HR_LOCATIONS_ALL_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 40 | INLIST ITERATOR | | | | | |
|* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_PAYROLL_ACTIONS | 1 | 16 | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | PAY_PAYROLL_ACTIONS_N51 | 1 | | 1 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | FND_TERRITORIES_TL | 1 | 35 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | FND_TERRITORIES_TL_U1 | 1 | | 0 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00:00:01 |
|* 46 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 7 | 1 (0)| 00:00:01 |
| 47 | VIEW | PER_POSITIONS | 1408 | 67584 | 22 (0)| 00:00:01 |
|* 48 | HASH JOIN | | 1408 | 83072 | 22 (0)| 00:00:01 |
|* 49 | VIEW | index$_join$_022 | 1408 | 11264 | 9 (0)| 00:00:01 |
|* 50 | HASH JOIN | | | | | |
| 51 | INDEX FAST FULL SCAN | PER_POSITIONS_FK1 | 1408 | 11264 | 6 (0)| 00:00:01 |
| 52 | INDEX FAST FULL SCAN | PER_POSITIONS_PK | 1408 | 11264 | 5 (0)| 00:00:01 |
|* 53 | TABLE ACCESS FULL | HR_ALL_POSITIONS_F_TL | 1408 | 71808 | 13 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 55 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 42 | 1 (0)| 00:00:01 |
|* 56 | FILTER | | | | | |
|* 57 | SORT JOIN | | 1147 | 45880 | 14 (8)| 00:00:01 |
| 58 | VIEW | index$_join$_033 | 1147 | 45880 | 13 (0)| 00:00:01 |
|* 59 | HASH JOIN | | | | | |
| 60 | INDEX FAST FULL SCAN | PAY_ELEMENT_TYPES_F_FK1 | 1147 | 45880 | 5 (0)| 00:00:01 |
|* 61 | INDEX FAST FULL SCAN | PAY_ELEMENT_TYPES_F_PK | 1147 | 45880 | 11 (0)| 00:00:01 |
|* 62 | TABLE ACCESS BY INDEX ROWID | PAY_ELEMENT_CLASSIFICATIONS | 1 | 37 | 1 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | PAY_ELEMENT_CLASSIFICATION_PK | 1 | | 0 (0)| 00:00:01 |
| 64 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_INPUT_VALUES_F | 1 | 41 | 2 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | PAY_INPUT_VALUES_F_UK2 | 1 | | 1 (0)| 00:00:01 |
|* 66 | TABLE ACCESS BY INDEX ROWID BATCHED | PAY_RUN_RESULT_VALUES | 773 | 12368 | 228 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | PAY_RUN_RESULT_VALUES_PK | 836 | | 6 (0)| 00:00:01 |
|* 68 | INDEX UNIQUE SCAN | PAY_RUN_RESULTS_PK | 1 | | 1 (0)| 00:00:01 |
|* 69 | TABLE ACCESS BY INDEX ROWID | PAY_RUN_RESULTS | 1 | 10 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
This new plan has more cpu cost that the original.
So, why is it recommending this?
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 68.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'staName65849',
task_owner => 'APPS', replace => TRUE);
Dula
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.