Re: Sun E4500, 4GB Mem, 2 processors, Oracle running like a dog?

Hi Chris,

If your contention for the child library cache latches is unevenly distributed then additional processors are likely to make the latch contention problem worse, not better. Check V$LATCH_CHILDREN for this. Check the Answers section of the Ixora web site for a recent explanation of this.

Also, it sounds like your shared_pool might be too large. I suggest that you get the shared_pool_free_lists.sql script from the Ixora web site and run it during a period of bad performance. If you find long shared pool free lists then you are likely to get some relief by reducing the shared pool size. Reducing the number of child library cache latches to 1 using _kgl_latch_count can also help, but is to be regarded as a last resort. Otherwise just upgrade to 8.1.6 where the shared pool free list management has been GREATLY improved.

If it is not a shared pool problem, then increasing the number of child library cache latches to 67 using _kgl_latch_count will mitigate the effect of the failure to use bind variables. Otherwise, just upgrade to 8.1.6 and use cursor_sharing to make all your literal SQL shareable.

You can also reduce load on the library cache latch using the session cursor cache. Even for applications that use literal SQL, a modest value like 10 for the session_cached_cursors parameter can make a noticeable difference.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/
@   
@   Going to OpenWorld?
@   Catch the Ixora performance tuning seminar too!
@   See http://www.ixora.com.au/seminars/ for details.

On Thu, 31 Aug 2000 14:34:08 +0100, "Chris M" <chris_milner_at_hotmail.com> wrote:

>Hi,
>
>I'm the DBA for a website and periodically, under heavy load, the server
>falls on it's knees. using the top utility, it hits 7,8 and 9. If I flush
>the shared_pool then the load goes down again...so I believe the shared_pool
>is fragmented. BUT the load still seems quite high, around 1-2 for most of
>the day.
>
>I think we need more processors, to manage 4GB of memory, and I've heard the
>standard of 1 processor per 512M of memory. I'm being told by my boss that I
>can never have enough memory, but I'm sure that's not the case. There's no
>swapping going on and the hit rates are high (99) in the database. The
>processor does hit 0 idle time though during heavy load.
>
>Has anyone got any experience of this situation? We're going to try and put
>2 more processors in the machine as a test. The only database activity is
>'select' and a small minority of inserts (maybe 100 an hour).
>
>Utlbstat/estat only really shows the 'latch free' statistic as a problem
>when the server is on it's knees...actually library latch contention. We can
>get around this by changing our code to use bind variables, but I still
>think we need more processors?
>
>Thanks
>Chris
>
>

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/