Oracle Performance Troubleshooting OPTIMIZER_FEATURES_ENABLE by yvtong

VIEWS: 39 PAGES: 2

									Senario: A SQL became very slow after the database is migrated from 9i to 11g.

Version: 11gR2

Compatibility : 10.2.0.2

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE.EMP_ID IN
(SELECT EMP_ID FROM SEC_EMPLOYEE_DATE
  WHERE SEC_EMPLOYEE_DATE.CLIENT_ID=1
  AND WBU_ID            =3
  AND TRUNC(SYSDATE) BETWEEN EMPT_START_DATE AND EMPT_END_DATE
  AND TRUNC(SYSDATE) BETWEEN WBUT_START_DATE AND WBUT_END_DATE)
AND EMP_ID IN
 (
  SELECT emp_id
  FROM sec_employee
  WHERE sec_employee.CLIENT_ID=1
  AND wbu_id        =3
  AND TRUNC(SYSDATE) BETWEEN start_date AND end_date
);




The explain plan on 9i ( running time < 20 seconds)




The explain plan on 11g (running time > 200 seconds)
Problem:

On 11g

Solution 1: Add Hint RULE OR NO_USE_NL

Solution 2: Adjust OPTIMIZER_FEATURES_ENABLE from 11.2.0.2 to 10.2.0.2.

Solution 3: Change internal Parameter

								
To top