Home » RDBMS Server » Performance Tuning » Help Tuning slow query (Oracle 12c)
| Help Tuning slow query [message #662051] |
Sat, 15 April 2017 09:16  |
 |
James_s
Messages: 7
Registered: April 2017
|
Junior Member |
|
|
Please, I need your help to tune this query as it is very slow. My task is to improve the performance. At the moment, it is taking about 30 minutes to run. I need help interpreting the explain plan. Many thanks in advence
--------
SELECT brsf_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) AS FAM
,DECODE('2', '1', org.team_area
,NULL) AS AREA
,org.body_position AS body_pos
,org.full_name
,peo.surname || ', ' || peo.initials AS NAME
,peo.payroll_number
,DECODE(BRSF_SES_IND(peo.person_code),
NULL, BRSF_SES_IND(peo.person_code,TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))
,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')), BRSF_SES_IND(peo.person_code)
,BRSF_SES_IND(peo.person_code, TO_DATE('01-AUG-2016', 'DD-MON-YYYY')) ||
' at revision date, ' ||
BRSF_SES_IND(peo.person_code) || ' now') AS SES
,app.pos_post_number AS post_number
,app.appointment_code
,sev.start_date AS salev_date
,org.team_area
,BRSF_APPT_GRADE(app.appointment_code, sev.start_date) AS grade_name
,BRSF_APPT_HOURS(app.appointment_code, sev.start_date) AS hours_per_week
,BRSF_APPT_SALARY(app.appointment_code, sev.start_date) AS salary
,BRSF_APPT_FTE_SALARY(app.appointment_code, sev.start_date) AS fte_salary
,DECODE(BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date)
,'N', 'NON-SPINAL'
,'Y', 'Spinal') AS spinal_ind
,gra.psp_category || gra.psp_group AS staffcat
FROM hes_people peo
,hes_periods_of_service POS
,hes_appointments app
,brs_post_org_histories poh
,hes_organisation_units org
,brsv_salary_events sev
,hes_app_grade_histories agh
,hes_grades gra
WHERE peo.person_code = pos.per_person_code
AND pos.period_of_ser_code = app.pes_period_of_ser_code
AND app.pos_post_number = poh.post_number
AND app.appointment_code = agh.app_appointment_code
AND sev.appointment_code = app.appointment_code
AND poh.organisation_code = org.organisation_code
AND poh.start_date = (SELECT MAX(poh2.start_date)
FROM brs_post_org_histories poh2
WHERE poh2.post_number = poh.post_number
AND poh2.start_date <= sev.start_date)
AND agh.gra_grade_code = gra.grade_code
AND TO_DATE('01-AUG-2016', 'DD-MON-YYYY') BETWEEN pos.start_date
AND pos.end_date
AND sev.start_date BETWEEN gra.start_date
AND gra.end_date
AND sev.start_date BETWEEN agh.effective_start_date
AND agh.effective_end_date
AND sev.start_date BETWEEN app.start_date
AND app.end_date
AND app.end_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')
AND app.pos_post_number != 'Z1'
AND (
sev.start_date >= TO_DATE('01-AUG-2016', 'DD-MON-YYYY')
OR
sev.start_date = (SELECT MAX(sev2.start_date)
FROM brsv_salary_events sev2
WHERE sev2.appointment_code = sev.appointment_code
AND sev2.start_date < TO_DATE('01-AUG-2016', 'DD-MON-YYYY'))
)
AND (
(
'Y' = 'Y'
AND
BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'Y'
)
OR
(
'N' = 'Y'
AND
BRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date) = 'N'
)
)
AND (
org.team_area IN ('A')
OR 'null' IN ('A')
)
ORDER BY 1 ASC
,2 ASC
,3 ASC
,4 ASC
,5 ASC
,6 ASC
,7 ASC
,8 ASC
,9 ASC
,10 DESC;
Explain plan
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 185 | 14207 |
| 1 | SORT ORDER BY | | 1 | 185 | 14207 |
| 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 1 | 185 | 14203 |
| 4 | NESTED LOOPS | | 1 | 185 | 14203 |
| 5 | HASH JOIN | | 1 | 166 | 14202 |
| 6 | NESTED LOOPS | | 1 | 166 | 14202 |
| 7 | STATISTICS COLLECTOR | | | | |
| 8 | HASH JOIN | | 1 | 145 | 14199 |
| 9 | NESTED LOOPS | | 1 | 145 | 14199 |
| 10 | STATISTICS COLLECTOR | | | | |
| 11 | NESTED LOOPS | | 1 | 128 | 14197 |
| 12 | NESTED LOOPS | | 1 | 87 | 14196 |
| 13 | NESTED LOOPS | | 1 | 70 | 14193 |
| 14 | HASH JOIN | | 1 | 48 | 14190 |
| 15 | TABLE ACCESS FULL | HES_APPOINTMENTS | 8056 | 204K| 603 |
| 16 | VIEW | BRSV_SALARY_EVENTS | 424K| 9119K| 13581 |
| 17 | SORT UNIQUE | | 424K| 10M| 13581 |
| 18 | UNION-ALL | | | | |
| 19 | HASH JOIN | | 5502 | 231K| 896 |
| 20 | NESTED LOOPS | | 5502 | 231K| 896 |
| 21 | NESTED LOOPS | | | | |
| 22 | STATISTICS COLLECTOR | | | | |
| 23 | HASH JOIN | | 5502 | 188K| 335 |
| 24 | NESTED LOOPS | | 5502 | 188K| 335 |
| 25 | STATISTICS COLLECTOR | | | | |
| 26 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | 7 |
| 27 | TABLE ACCESS BY INDEX ROWID BATCHED| HES_APP_GRADE_INCR_HISTORIES | 4 | 92 | 326 |
| 28 | BITMAP CONVERSION TO ROWIDS | | | | |
| 29 | BITMAP AND | | | | |
| 30 | BITMAP CONVERSION FROM ROWIDS | | | | |
| 31 | SORT ORDER BY | | | | |
| 32 | INDEX RANGE SCAN | BRSI_AGIH_PRIME | 361 | | 9 |
| 33 | BITMAP CONVERSION FROM ROWIDS | | | | |
| 34 | INDEX RANGE SCAN | BRSI_AGIH_GRADECODE | 361 | | 71 |
| 35 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| 326 |
| 36 | INDEX UNIQUE SCAN | HES_AGH_PK | | | |
| 37 | TABLE ACCESS BY INDEX ROWID | HES_APP_GRADE_HISTORIES | 1 | 8 | 560 |
| 38 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
| 39 | HASH JOIN | | 28942 | 2148K| 920 |
| 40 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
| 41 | HASH JOIN | | 28942 | 1921K| 358 |
| 42 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 7009 | 157K| 18 |
| 43 | HASH JOIN | | 87637 | 3851K| 335 |
| 44 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | 7 |
| 45 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 91342 | 2051K| 326 |
| 46 | HASH JOIN | | 121K| 2248K| 747 |
| 47 | NESTED LOOPS | | 121K| 2248K| 747 |
| 48 | STATISTICS COLLECTOR | | | | |
| 49 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
| 50 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 11 | 184 |
| 51 | INDEX FAST FULL SCAN | BRSI_AGHW_PRIME | 121K| 1301K| 184 |
| 52 | HASH JOIN | | 91342 | 1694K| 691 |
| 53 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
| 54 | INDEX FAST FULL SCAN | BRSI_AGIH_GHC_START | 91342 | 981K| 128 |
| 55 | HASH JOIN | | 127K| 2372K| 731 |
| 56 | NESTED LOOPS | | 127K| 2372K| 731 |
| 57 | STATISTICS COLLECTOR | | | | |
| 58 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 812K| 560 |
| 59 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 11 | 168 |
| 60 | INDEX FAST FULL SCAN | BRSI_SALHISTS_PRIME | 127K| 1373K| 168 |
| 61 | HASH JOIN | | 49691 | 1698K| 566 |
| 62 | NESTED LOOPS | | 49691 | 1698K| 566 |
| 63 | NESTED LOOPS | | | | |
| 64 | STATISTICS COLLECTOR | | | | |
| 65 | HASH JOIN | | 587 | 8218 | 4 |
| 66 | INDEX FULL SCAN | BRSI_PSHH_UI | 83 | 747 | 1 |
| 67 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | 3 |
| 68 | INDEX RANGE SCAN | BRSI_AGH_GRADECODE | | | |
| 69 | TABLE ACCESS BY INDEX ROWID | HES_APP_GRADE_HISTORIES | 85 | 1785 | 560 |
| 70 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 104K| 2133K| 560 |
| 71 | TABLE ACCESS BY INDEX ROWID | HES_PERIODS_OF_SERVICE | 1 | 22 | 2 |
| 72 | INDEX UNIQUE SCAN | HES_PES_PRIME | 1 | | 1 |
| 73 | TABLE ACCESS BY INDEX ROWID BATCHED | BRS_POST_ORG_HISTORIES | 1 | 17 | 3 |
| 74 | INDEX RANGE SCAN | BRSI_POH_POST_START | 1 | | 2 |
| 75 | SORT AGGREGATE | | 1 | 13 | |
| 76 | FIRST ROW | | 1 | 13 | 3 |
| 77 | INDEX RANGE SCAN (MIN/MAX) | BRSI_POH_POST_START | 1 | 13 | 3 |
| 78 | TABLE ACCESS BY INDEX ROWID | HES_ORGANISATION_UNITS | 1 | 41 | 1 |
| 79 | INDEX UNIQUE SCAN | HES_ORG_PRIME | 1 | | 0 |
| 80 | TABLE ACCESS BY INDEX ROWID | HES_PEOPLE | 1 | 17 | 2 |
| 81 | INDEX UNIQUE SCAN | HES_PER_PRIME | 1 | | 1 |
| 82 | TABLE ACCESS FULL | HES_PEOPLE | 1 | 17 | 2 |
| 83 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 1 | 21 | 3 |
| 84 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 1 | | 2 |
| 85 | TABLE ACCESS FULL | HES_APP_GRADE_HISTORIES | 1 | 21 | 3 |
| 86 | INDEX UNIQUE SCAN | HES_GRA_PRIME | 1 | | 0 |
| 87 | TABLE ACCESS BY INDEX ROWID | HES_GRADES | 1 | 19 | 1 |
| 88 | SORT AGGREGATE | | 1 | 22 | |
| 89 | VIEW | BRSV_SALARY_EVENTS | 8557 | 183K| 736 |
| 90 | SORT UNIQUE | | 8557 | 292K| 736 |
| 91 | UNION-ALL | | | | |
| 92 | NESTED LOOPS | | 3 | 57 | 8 |
| 93 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
| 94 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
| 95 | INDEX RANGE SCAN | BRSI_AGHW_PRIME | 1 | 11 | 2 |
| 96 | NESTED LOOPS | | 5 | 95 | 8 |
| 97 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
| 98 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
| 99 | INDEX RANGE SCAN | BRSI_AGIH_GHC_START | 2 | 22 | 2 |
| 100 | NESTED LOOPS | | 3 | 57 | 8 |
| 101 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
| 102 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
| 103 | INDEX RANGE SCAN | BRSI_SALHISTS_PRIME | 1 | 11 | 2 |
| 104 | HASH JOIN | | 8545 | 292K| 10 |
| 105 | HASH JOIN | | 99 | 1386 | 4 |
| 106 | INDEX SKIP SCAN | BRSI_PSHH_UI | 83 | 747 | 1 |
| 107 | TABLE ACCESS FULL | HES_GRADES | 330 | 1650 | 3 |
| 108 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 104K| 2148K| 4 |
| 109 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
| 110 | HASH JOIN | | 1 | 30 | 696 |
| 111 | TABLE ACCESS BY INDEX ROWID BATCHED | HES_APP_GRADE_HISTORIES | 2 | 16 | 4 |
| 112 | INDEX RANGE SCAN | BRSI_AGH_APP_START | 2 | | 3 |
| 113 | VIEW | VW_JF_SET$8DB8F874 | 33940 | 729K| 692 |
| 114 | SORT UNIQUE | | 33940 | 2076K| 692 |
| 115 | UNION-ALL | | | | |
| 116 | HASH JOIN | | 5501 | 188K| 335 |
| 117 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 17316 | 7 |
| 118 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| 327 |
| 119 | HASH JOIN | | 28439 | 1888K| 357 |
| 120 | HASH JOIN | | 4913 | 215K| 26 |
| 121 | TABLE ACCESS FULL | HES_SPINAL_INCREMENT_MAPS | 1443 | 31746 | 7 |
| 122 | INDEX FAST FULL SCAN | BRSI_PRH_SPINE_POINT_START | 6888 | 154K| 18 |
| 123 | TABLE ACCESS FULL | HES_APP_GRADE_INCR_HISTORIES | 88382 | 1985K| 327 |
---------------------------------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
How to use {code} tags and make your code easier to read
-
Attachment: qry1.sql
(Size: 4.26KB, Downloaded 2489 times)
[Updated on: Sat, 15 April 2017 11:07] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Help Tuning slow query [message #662055 is a reply to message #662051] |
Sat, 15 April 2017 11:23  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You are getting an adaptive plan, so there is no reason to assume that the plan shown is the plan that will be used.
You need to drop your plan_table and re-create it as the correct version (or just drop it, and use the supplied plan_table public synonym) and then explain your statement with the format=>'adaptive' argument to see both plans that are being considered. Then you need to run the statement with the gather_plan_Statistics hint, and extract the actual plan used from the library cache.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 20:57:50 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
