Re: Problems that can ocuur while upgrading from 9i 1o 10g

"Jenny" <jjohnietk_at_gmail.com> schreef in bericht news:5e0012e6-37a2-47a0-9203-4d2a60896216_at_q26g2000prq.googlegroups.com...
> Our company is planning to uprage from oracle 9i to 10g.
> I want to know what all problem i will face during upgradation..

Adding to all other useful comments:
Don't forget to recalculate all appropriate schema statistics after upgrading, even sys and system. We found we were left with some old statistics, because 10g calculates stats only when (it thinks) it is necessairy (stale indexes etc). After recalculating statistics, performance went up. Our problem MAY have been caused by using a bucket size of 1 in 9i (we set it to 'auto' in 10g). Also calculate system statistics. (GATHER_SYSTEM_STATS).
One sql statement is not allowed in 10 where it was in 9: select level from <table> (without connect by) . I don't know why anyone would do so, but we had a third party app that did.. There is an init parameter to allow this, but I think it would be better to remove the statement from your app (if possible).

You may encounter performance problems. We found high parse times for some statements (higher than in 9i), and a lot more cpu usage (mainly due to not using bind variables; we tried to set cursor_sharing_mode to similar, but that gave a lot of errors in our queries), and we had to tune some of the worst statements. It looks like 10g is evaluating a lot more execution paths before deciding what's best, specially when using (complex) views within a complex query. In our case, we had to hint the queries to avoid long parse runs. (6 sec parse, 31 msec execution time when not hinted, 33 msec total when hinted), But again: this was caused mainly by bad programming in a third party app.

Shakespeare Received on Fri Sep 19 2008 - 04:11:39 CDT