Re: Eroding Oracle Performance

Jpmakar wrote:
>
> I am experiencing a strange problem in one of my 8.04 instances. Gradually,
> over the course of a day, performance goes in the tank. I mean we have a
> benchmark query (select count(*) from foo) that runs in less than a second at
> 8:00AM and runs in 10 seconds by noon, and by 4:00PM is up to 20 seconds.
> Same number of rows, only slightly more user sessions. It doesn't seem to be

Humm.. Sounds like a problem we encountered a while ago. Do you use a lot of adhoc sqls? If so, then it has something to do with your shared sql area. Every time you send an SQL statement to Oracle, it searches in the shared SQL area to try and locate an identical one so it doesn't have to reparse it. If you have a LOT of adhoc SQL statements, this can create a HUGE amount of different SQL statements in the shared sql area.

By using ALTER SYSTEM FLUSH SHARED SPOOL should correct such a situation.

Hop this helps.

Thanks.

-- 
Stephane Lajeunesse.
Oracle DBA