SQL & PL/SQL

The following code has not changed since well before we did our Oracle upgrade to 9.2. Before the upgrade, this code would execute in approximately 10 minutes. Now it executed in approximately 1.5 - 2 DAYS. I have been trying to look at the explain plans to figure out what may cause it to be like this.

SELECT
LTRIM(TO_CHAR(xfre_vrsn_id,'000000')) version_id,
xfre_item_upc item_upc,
xfre_rm_code rm_code,
xfre_loc_code loc_code,
TO_CHAR(vrsn_model_begin_date,'DD-MON-YYYY') run_start_date,
am_month run_month,
LTRIM(TO_CHAR(xfre_rm_in_shred_usage, 'S00000000000.00')) rm_in_shred_usage,
LTRIM(TO_CHAR(xfre_rm_in_cutting_usage, 'S00000000000.00')) rm_in_cutting_usage,
LTRIM(TO_CHAR(xfre_rm_in_process_usage, 'S00000000000.00')) rm_in_process_usage,
LTRIM(TO_CHAR(xfre_other_rm_usage, 'S00000000000.00')) other_rm_usage,
LTRIM(TO_CHAR(xfre_trim_to_process_usage, '00000000000.00')) trim_to_process_usage,
LTRIM(TO_CHAR(xfre_trim_to_shred_usage, '00000000000.00')) trim_to_shred_usage,
LTRIM(TO_CHAR(xfre_reject_usage, '00000000000.00')) reject_usage,
LTRIM(TO_CHAR(xfre_other_byprod_usage, '00000000000.00')) other_byprod_usage,
LTRIM(TO_CHAR(xfre_total_gross_usage, '00000000000.00')) total_gross_usage,
RPAD('&4',7) disco_source,
substr(to_char(vt_pr.model_pr_dt,'MMDDYYYY'),1,8) model_pr_dt
FROM version,
location,
pp_model_periods p,
acctg_month,
sp_xt_fg_rm_extract,
(SELECT pmp_period, min(fgde_start_Date) model_pr_dt
FROM fg_dmd_estimate, pp_model_periods
WHERE pmp_vrsn_id = &1
AND pmp_vrsn_id = fgde_vrsn_id
AND pmp_period > 0
AND fgde_start_date BETWEEN pmp_mod_start_date AND pmp_mod_end_date
AND fgde_date_increment = pmp_date_increment
GROUP BY pmp_period ) vt_pr
WHERE vrsn_id = &1
AND loc_vrsn_id = &1
AND xfre_vrsn_id = &1
AND pmp_vrsn_id = &1
AND xfre_loc_code = loc_code
AND loc_type IN ( 'SU','US','ST','TO' )
AND xfre_period = p.pmp_period
AND pmp_mod_start_date BETWEEN am_start_date AND am_end_date
AND p.pmp_period > 0 --ignore the 0th period if it exists
AND p.pmp_period <= DECODE('&4', 'MONTHLY', 12,
'ANNUAL' , 12,
'FRF' , 9,
'SRF' , 6,
'TRF' , 3 )
AND am_month >= DECODE('&4', 'MONTHLY', '01',
'ANNUAL' , '01',
'FRF' , '04',
'SRF' , '07',
'TRF' , '10' )
AND am_year like (decode('&4','MONTHLY','%',TO_CHAR(TO_DATE('&5', 'MM/DD/YYYY'), 'YYYY'))) --if not monthly only get data in current year
AND p.pmp_period = vt_pr.pmp_period
ORDER BY 4, p.pmp_period, 2, 3 --plant, model period, item_upc(or prm), rmcode

In a similar query, it appeared to be the inline view section of code:

(SELECT pmp_period, min(fgde_start_Date) model_pr_dt
FROM fg_dmd_estimate, pp_model_periods
WHERE pmp_vrsn_id = &1
AND pmp_vrsn_id = fgde_vrsn_id
AND pmp_period > 0
AND fgde_start_date BETWEEN pmp_mod_start_date AND pmp_mod_end_date
AND fgde_date_increment = pmp_date_increment
GROUP BY pmp_period ) vt_pr

That was causing a huge drop in performance. This section of code is very simple, and runs quickly when executed on its own. I can probably rewrite the code to get rid of this, but that does not answer that question of WHY there is such a HUGE difference in execution between the version of Oracle, if that is the cause. The reason am leaning toward that being the cause is that when this code ran in production before the upgrade, we had no problem, but now we do.

I have tried to run the query with a hint for force rule based optimization, and so far have not seen any change over the current "CHOOSE". We recently analyzed on the database, so it should not be an issue of inefficiency caused by lack up updated database statistics. Any help you can give is very much appreciated.