Custom query on HR tables runs in 15 minutes in 9.2.0.5 and after upgrading to 11.2.0.2, runs for ev

   Hi,

We have a query which completes in 15 minutes in 9.2.0.5.  After upgrading to 11.2.0.2, it runs very long and doesn't seem to complete at all.

Any pointers? All other custom programs and standard queries are working fine.

We have enabled 11.2.0 optimizer on the DB. The query is given below...

Request your help.

SELECT position_id,
  person_id,
  full_name,
  title,
  ft_pt,
  job_code,
  hire_date,
  current_rate,
  dept_code,
  deptname,
  store_number,
  hours,
  current_headcount,
  rate_schedule,
  mgmt_hourly,
  assignment_status
 FROM (SELECT  paaf.position_id,
   papf.person_id,
   papf.full_name,
   ppd.segment3 title,
   decode(substr(ppd.segment6,-1), '0','FT', '5', 'PT','9','PT', NULL) FT_PT,