Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance challenge.

RE: Performance challenge.

From: Powell, Mark D <>
Date: Tue, 17 Apr 2007 14:25:50 -0400
Message-ID: <>

The statement >> (It also appears you can not flush any active statements from the shared_pool.) << is true. If the SQL is actively being executed it does not flush.  

Are you generating histograms? The behavior you describe where it appears to matter what values were used in an SQL statement as to how following submissions of the statement perform makes me think that bind variable peeking could be in play. Try eliminating the histograms and see what happens. You can always put them back.  

And yes when you regenerate the statistics you flush existing, not in use, SQL that uses the object out of the shared pool.

HTH -- Mark D Powell --
Phone (313) 592-5148  

[] On Behalf Of Johnson, William L (TEIS)

	Sent: Tuesday, April 17, 2007 1:04 PM
	Subject: 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 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?          


        Bill Johnson

Received on Tue Apr 17 2007 - 13:25:50 CDT

Original text of this message