Performance challenge.

We are running a version of Matrix One's Document Management system. My team inherited the DBA work from another group in our organization. This group had developed a nice process to update statistics on an hourly basis based on a 2% threshold change to data in the base tables. The scripts used dbms_stats to recalculate the stats. Recently, the scripts have had some issues and have not run for several hours - and sure enough user complaints began to pour into the support teams.  

I am baffled that only a 100GB allocated database can possibly require stats to be recomputed on an hourly basis to provide adequate response time. While I am not an expert, this application appears to generate its own dynamic MQL queries where we can not add hints to ensure performance. We are running Oracle 9.2.0.6 on Solaris hardware.  

So, after all of that, what is my real question? Does analyzing a table invalidate all parsed statements in the v$sqlarea - in effect getting rid of a poor explain plan that may be causing other queries to run slowly? We appear to have seen evidence of this before where an application can provide various bind variables into a Sql statement. Depending on who first ran the query with a wide range of values, Oracle would generate a different access path and sit on that path until the sql statement had been either aged out of the shared_pool or was forced out by an alter system flush shared pool command. (It also appears you can not flush any active statements from the shared_pool.)    

Anyone have experience with this or have some good documentation I could read?  

Thanks!

Bill Johnson

--
http://www.freelists.org/webpage/oracle-l