Please, your help will be appreciated. I need to improve the performance of this query as it is very slow. When it finally return data in SQL developer, it takes forever to output to excel as csv.
I attach both the query and the explain plan as I am having problems fitting them here
I am having problems interpreting the explain plan.
Many thanks for your help.
select * from (
select
TRSF_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) FAM,
decode('2','1',org.team_area,null) AREA,
org.body_position body_pos,
org.full_name,
peo.surname||', '||peo.initials name,
peo.payroll_number,
decode(TRSF_SES_IND(peo.person_code),
null, TRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')),
TRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')), TRSF_SES_IND(peo.person_code),
TRSF_SES_IND(peo.person_code,to_date('01-AUG-2016','DD-MON-YYYY')) ||
' at revision date, ' || TRSF_SES_IND(peo.person_code) || ' now') SES,
app.pos_post_number post_number,
app.appointment_code,
sev.start_date salev_date,
org.team_area,
TRSF_APPT_GRADE(app.appointment_code,sev.start_date) grade_name,
TRSF_APPT_HOURS(app.appointment_code, sev.start_date) hours_per_week,
TRSF_APPT_SALARY(app.appointment_code, sev.start_date) salary,
TRSF_APPT_FTE_SALARY(app.appointment_code, sev.start_date) fte_salary,
decode(TRSF_APPT_SPINAL_IND(app.appointment_code, sev.start_date),
'N','NON-SPINAL',
'Y','Spinal') spinal_ind,
gra.psp_category||gra.psp_group staffcat
from
res_people peo,
res_periods_of_service pos,
res_appointments app,
trs_post_org_histories poh,
res_organisation_units org,
brsv_salary_events sev,
res_app_grade_histories agh,
res_grades gra
where peo.person_code = pos.per_person_code
and to_date('01-AUG-2016','DD-MON-YYYY') between pos.start_date and pos.end_date
and pos.period_of_ser_code = app.pes_period_of_ser_code
and app.pos_post_number = poh.post_number
and poh.start_date= (select max(poh2.start_date)
from trs_post_org_histories poh2
where poh2.post_number=poh.post_number
and poh2.start_date<=sev.start_date)
and poh.organisation_code = org.organisation_code
and agh.gra_grade_code = gra.grade_code
and sev.start_date between gra.start_date and gra.end_date
and app.appointment_code = agh.app_appointment_code
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.appointment_code = app.appointment_code
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 TRSF_APPT_SPINAL_IND(app.appointment_code,sev.start_date)='Y') or
('N'='Y' and TRSF_APPT_SPINAL_IND(app.appointment_code,sev.start_date)='N') )
and (org.team_area in ('A') or 'null' in ('A'))
order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 desc)
union all
(
select
TRSF_JOB_FAMILY_PATHWAY_LEVEL(app.appointment_code) FAM,
decode('2','1',org.team_area,null) AREA,
org.body_position org_body_pos,
org.full_name,
peo.surname||', '||peo.initials name,
peo.payroll_number,
decode(TRSF_SES_IND(peo.person_code),
null, TRSF_SES_IND(peo.person_code,pos.start_date),
TRSF_SES_IND(peo.person_code,pos.start_date), TRSF_SES_IND(peo.person_code),
TRSF_SES_IND(peo.person_code,pos.start_date) ||
' at start date, ' || TRSF_SES_IND(peo.person_code) || ' now') SES,
app.pos_post_number post_number,
app.appointment_code,
pos.start_date service_start_date,
org.team_area,
TRSF_APPT_GRADE(app.appointment_code, pos.start_date) grade_name,
TRSF_APPT_HOURS(app.appointment_code, pos.start_date) hours_per_week,
TRSF_APPT_SALARY(app.appointment_code, pos.start_date) salary,
TRSF_APPT_FTE_SALARY(app.appointment_code, pos.start_date) fte_salary,
decode(TRSF_APPT_SPINAL_IND(app.appointment_code, pos.start_date),
'N','NON-SPINAL',
'Y','Spinal') spinal_ind,
gra.psp_category||gra.psp_group staffcat
from
res_people peo,
res_periods_of_service pos,
res_appointments app,
trs_post_org_histories poh,
res_organisation_units org,
res_app_grade_histories agh,
res_grades gra
where peo.person_code = pos.per_person_code
and pos.start_date > to_date('01-AUG-2016', 'DD-MON-YYYY')
and pos.period_of_ser_code = app.pes_period_of_ser_code
and pos.start_date between app.start_date and app.end_date
and app.pos_post_number != 'Z1'
and app.pos_post_number = poh.post_number
and pos.start_date between poh.start_date and poh.end_date
and poh.organisation_code = org.organisation_code
and app.appointment_code = agh.app_appointment_code
and pos.start_date between agh.effective_start_date and agh.effective_end_date
and agh.gra_grade_code = gra.grade_code
and pos.start_date between gra.start_date and gra.end_date
)
order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 desc