We have upgraded our databases from 220.127.116.11 to 10.2.0.3. After the upgrade we have set Optimizer_Features_Enable=10.2.0.3. We have faced the performance slow down after this setup. As a workaround to enhance the performance Optimizer_Features_Enable (OFE) set back to 9.2.0, after this the performance is as expected.
Now we are planning to set this parameter to 10.2.0.3. There are few processes which are running fine (1 hr) with OFE 9.2.0, taking 5 hrs time with OFE 10.2.0.3.
Also if you are constantly deleting rows based on date you might consider partitioning the table by date. For example if you 1 year of data, you can either partition by month or quarter and then drop the oldest partitions.
But triggers can definitely be an issue, especially when the are trying to do too much.
We have upgraded our databases from 18.104.22.168 to 10.2.0.3. After the upgrade we have set Optimizer_Features_Enable=10.2.0.3. We have faced the performance slow down after this setup.
Oracle has introduced a series of changes in Ora10g you want to take into consideration.
Ora9i had an I/O based cost optimazer while Ora10g has by default a CPU based cost optimazer, this means Ora10g would try to use less CPU by doing more FTS. An alternative is to set _optimizer_cost_model parameter to "io" If you consider touching this hidden parameter please check before with Oracle support.
Ora9i gathered performance statistics might not be enough for the same database after upgrading to Ora10g. You may want to gather fresh stats on Ora10g using dbms_stats. The more histograms you get, the more chances Ora10g has of deciding for a efficient execution plan.
Finally, when you set optimizer_features_enable=10.2.0.3 be sure optimizer_mode IS NOT SET to "choose", it should be usually set to first_rows for a OLTP system while set to all_rows for a DSS system. Ora10g default is all_rows.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.