Re: Oracle slowed down

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 Apr 2006 18:12:27 -0700
Message-ID: <1145495547.406317.186260@t31g2000cwb.googlegroups.com>

Daniel is correct. The problem is likely being caused by one or more bad SQL statements. Update the statistics then spot check the v$ views for the SQL statements with the hightest logical and physical IO numbers. Verify that these statements are tuned adequately.

Next look for almost identical SQL statements where the difference in the statements is the value of constants. Finding this would point to a failure to use bind variables properly in the applications.

Think about and inquire on any application changes made at or just before the problem started to occur. Any new program, pl/sql package, web applications, or process change should be looked at.

During a problem period take a look at the latching going on in the shared pool. Take a look at the load counts. If you have lots of objects being reloaded then you might need more space in the shared pool rather than in the buffer cache.

I take it you checked the alert log and user trace directory to make sure no database or process problem were occurring.

HTH -- Mark D Powell -- Received on Wed Apr 19 2006 - 20:12:27 CDT