Re: Shared Pool Tuning

Why do you say the shared pool is filling up and slowing down batch updates? Are you seeing a lot of hard parsing? Are you seeing lots of reloads?

How big is your shared pool? What version of Oracle? How many different SQL statements do your batch jobs use?

You haven't given us any information which we can use to help you. When the shared pool "fills up", all that means is that additional SQL which gets hard parsed must move some other SQL out of the shared pool. This is not a problem unless the SQL moved out is needed again. If you keep having to reload, THAT will slow down your batch updates. If you flush the shared pool, then all SQL will have to be reparsed. That will slow down your batch updates.

The version of Oracle is relevant, as 4031 errors are more common in older versions. Those errors can be reduced temporarily if you flush the shared pool. But you haven't said that you're receiving these errors.

The shared pool will always "fill up" (unless it is quite large, and your number of different SQL statements is quite small). That is not a problem. The problem occurs if you have to reload statements.

--Terry

• Original Message ----- Our shared pool is filling up and slowing down batch updates. Is there a way to correct this problem besides frequent commits, or enlarging the shared pool?

Ron=20
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2004 - 15:39:52 CDT