Oracle Performance and Engineering

First one was this.

A big query takes 'forever' (about 385 secs. on DB CPU and a total of 685 secs. elapsed). It looks like -

/* Formatted on 2006/03/31 14:32 (Formatter Plus v4.8.5) */
SELECT t1.cessation_date, t1.commence_date, t1.confirm_date, t1.alias,
t1.birth_date, t1.emp_card_one, t1.employee_id, t1.employee_name,
t1.employment_type_code, t1.family_name, t1.gender, t1.given_name,
t1.language_spoke, t1.language_written, t1.mail_id,
t1.marital_stat_eff_date, t1.marital_stat_code, t1.nationality_code,
t1.native_name, t1.original_commence_date, t1.permit_expiry_date,
t1.permit_issue_date, t1.permit_type, t1.national_id, t1.race_code,
t1.religion_code, t1.religion_born, t1.religion_convert_date,
t1.security_id, t1.service_reference_date, t1.status_code,
t1.title_code
FROM hrm_employee t1, hrm_curr_career_v t0
WHERE ( ( ( ( (t0.job_level_code IN
('JL1-S0',
'JL1-SF',
'JL1-SG',
'JL1-SH',
'JL1-SI',
'JL1-SJ',
'JL1-SK',
'JL1-SL',
'JL1-SM',
'JL1-SN',
'JL1-SO',
'JL1-SP',
'JL1-SQ',
'JL1-SR',
'JL5-S0',
'JL8-S9',
'JL9-SC',
'JLA-S0',
'JL8EHR26',
'JL8EHR29'
)
)
AND (t0.leave_scheme_code = 'LSA')
)
AND (t0.employee_id = t1.employee_id)
)
AND t1.employee_id <> 'SG02514373'
AND t1.employee_id IN (
SELECT t1.employee_id
FROM sec_gpprofile t2,
sa_userprofile t1,
sec_profilegroupmap t0
WHERE (( (t2.object_id = 'Time.Attendance Adj')
AND ( (t0.GROUP_ID = t2.GROUP_ID)
AND (t1.user_id = t0.profile_id)
)
)
))
)
AND (t0.employee_id IN (
SELECT e.employee_id
FROM hrm_curr_career_v c, hrm_employee e
WHERE c.employee_id = e.employee_id
AND e.employee_id LIKE 'SG%'
AND (e.cessation_date IS NULL
OR e.cessation_date >= SYSDATE
)
AND ( c.employee_id = 'SG02514373'
OR c.supervisor_3 = ('SG02514373')
OR c.employee_id IN (
SELECT cc1.employee_id
FROM hrm_cost_centre cc1, hrm_curr_career_v c
WHERE c.employee_id = ('SG02514373')
AND c.remarks LIKE
'%/' || cc1.cost_centre_code || '%'
AND cc1.employee_id LIKE 'SG%'
AND cc1.eff_date =
(SELECT MAX (cc2.eff_date)
FROM hrm_cost_centre cc2
WHERE cc2.employee_id =
cc1.employee_id
AND cc2.eff_date <= SYSDATE))
)
UNION
(SELECT e.employee_id
FROM hrm_curr_career_v c,
hrm_employee e,
ihr_covering_officer r2
WHERE c.employee_id = e.employee_id
AND e.employee_id LIKE 'SG%'
AND ( e.cessation_date IS NULL
OR e.cessation_date >= SYSDATE
)
AND r2.covering_officer_id = ('SG02514373')
AND r2.start_date <= SYSDATE
AND r2.end_date >= SYSDATE
AND ( c.supervisor_3 = r2.officer_id
OR c.employee_id IN (
SELECT cc1.employee_id
FROM hrm_cost_centre cc1,
hrm_curr_career_v c2
WHERE c2.employee_id = r2.officer_id
AND c2.remarks LIKE
'%/' || cc1.cost_centre_code || '%'
AND cc1.employee_id LIKE 'SG%'
AND cc1.eff_date =
(SELECT MAX (cc2.eff_date)
FROM hrm_cost_centre cc2
WHERE cc2.employee_id =
cc1.employee_id
AND cc2.eff_date <= SYSDATE))
)))
)
);

They have the execution plan attached.

(I also commented on this - as oracle_doc ;) ).

But here's how I would analyze this, if I were at a completely unknown site with this query.

(1) The part in the given plan that is most expensive is -

Rows Execution Plan
------- ---------------------------------------------------
40928544 NESTED LOOPS
13428 INDEX (RANGE SCAN) OF 'HRM_CAREER_PK' (UNIQUE)
40928544 TABLE ACCESS (BY INDEX ROWID) OF 'HRM_CAREER'
3048 INDEX (RANGE SCAN) OF 'HRM_CAREER_PK' (UNIQUE)

(2) I see no 'HRM_CAREER' table in the given SQL. Thus, the table HRM_CAREER is part of the view hrm_curr_career_v.

(3) I would open SQL*plus and execute two queries -- one each for each UNION part -- with "set autot on". Typically, it's just one part that's sluggish.

(4) Since I see a VIEW OF 'VW_NSO_1' in the explain plan - I will also try
"alter session set "_unnest_subquery"=false", or alternately UNNEST_SUBQUERY hint.

WHY?

What is "VW_NSO_1" View?
------------------------

"VW_NSO_1" is a dynamic view which is created at query run time. It is utilized for
nested subquery optimization.

It is used to transform an IN subquery to a join, and to transform a NOT IN
subquery to an anti-join.

Thus, a guess would be -- without looking into the view or data-distribution mentioned in this query -- that Oracle transforms the entire unbounded IN-list to a join, and that could be a pain in this case.

(5) Check the data distribution and indexing of supervisor_3 and eff_date columns from hrm_curr_career_v view

(6) Possibly use ANALYTIC FUNCTION - MAX(eff_date) to replace this part of the query -

cc1.eff_date =
(SELECT MAX (cc2.eff_date)
FROM hrm_cost_centre cc2
WHERE cc2.employee_id =
cc1.employee_id
AND cc2.eff_date <= SYSDATE)

(7) And of course, bind this statement and execute it. Without binding, and especially if histograms were calculated, Oracle could arrive at a completely different plan.

The second post I took a look at is this. There was a statspack analysis, about 75% of the load could be attributed to a single SQL (look at the buffer_gets) that looks like a "space check" monitoring query - and definitely not recursive.

This guy also wrote - "the server also hosts a number of automated programs that
access the database. "

I would start with looking into these "automated programs" too. It executed about 779 times over a period of 68 mins. 68 secs. -- very close to once in 5 secs.