9i to 10g causing sql to return VERY slowly
529447Dec 19 2007 — edited Dec 23 2007We upgraded from Oracle 9i to 10g on the weekend (some software our vendor supplies necessitated it) and since then we've seen some pretty poor response times which is causing business issues (funnily enough, we didn't see this as a problem in test - though the load is much higher now).
FYI, we're running Oracle 10.1.0.5 on Windows Server 2003 - We have approximately 4 databaes per server, general load is 2 x 2Gb databases and 2 x 1Gb databases per server.
We're trying to muddle through it and reading up on sql tuning in general to get a better understanding (no fulltime dba on staff) but I'd appreciate any pointers anyone can give me.
We've read that lowering the optimizer_index_cost_adj parameter from the default of 100 can give a significant boost to performance, and despite doing some reading on it, I'm still unsure as to the benefit of it. There were some other parameters mentioned: optimizer_index_caching , optimizer_index_cost_adj , optimizer_max_permutations and optimizer_search_limit
We've also read that running dbms_stats.gather_system_stats might help, but again, if anyone has some experience with this I'd appreciate it.
Finally, if anyone can give me some other words of wisdom or references to improve performance, that'd be great.
Thanks