Re: Performance degradation

You probably want to find out exactly what Oracle is waiting on. See the performance tuning guide about statspack, or use OEM to look at the top sessions or top sql, click on particular slow sessions and look at top waits.

There are also potential memory leaks, some "fixed in the next release," so you might try reducing some SGA parameters if you've tried to maximize SGA size and then assign the rest of memory to PGA, which parameters "depends." Resource advisors in OEM can be helpful, and can be misleading. They make pretty pictures, though.

Also, how have you determined it is on the Oracle side? Excessive waits for sql*net? Are you using webcache? 9iAS? Is your app jamming stuff into the indices you are scanning?

It is possible that if you explain plan on the UPPER query you will see it being forced into a full table scan. If you are inserting into the table after the app has been up, you might see waits on the rollback, as it thrashes trying to keep many reads consistent.

So many possibilities! And the usual cause is poor app design and programming. But if it's all fixed with adding one index - Kewl!

jg

--
@home.com is bogus.
$100K is high income?  Can't hardly afford an average house on that in
socal:
http://www.signonsandiego.com/uniontrib/20050413/news_1b13taxgap.html