Re: db_block_lru_latches and servers with multiple instances

Unfortunately there *IS* (or maybe was) a bug (1967363) that caused contention for the root block of indexes. Since the root block always hashes to the same latch it's pretty easy to get contention on that one latch. It's also pretty easy to get with badly fragmented DMTs when checking for tablespace utilization.

I've seen these two things cripple a server with the default value of db_block_lru_latches. (CPU_COUNT/2) We had a nested loop probe of an index where a hash join would have been more appropriate. (Stale statistics, easy to fix) The repeated probe of the index would have one of the latches "checked out" almost constantly. Adding on two or three sessions checking for free tablespace also competing for another latch or two, and pretty soon perfectly good queries hit the wall. While it was easy to get rid of the NL in favor of the hash, trying to convince the administrators that having two or more tablespace monitoring scripts running constantly was a problem. The best we could do is change the db_block_lru_latches to 2*CPU_COUNT to try and minimize the contention to just the tablespace monitoring scripts. Now we just tell everybody to run 2*CPU_COUNT and forget about it. In fact in 9i db_block_lru_latches becomes hidden and defaults to 2*CPU_COUNT.

My vote: set it 2*CPU_COUNT on all instances and write it off to best practices. It doesn't cost you anything and it gives you protection against poorly written queries or bugs. It also gives you an extra little bit of scalability when the server's CPUs get overloaded. What happens when somebody grabs a latch then gets context switched for a "long" time before it can make it through the critical section and release the latch? Wouldn't you rather have 4x the number of latches?

S-

PS: Jared, I agree 100% that knob twisting rarely makes all worries go away. In fact I don't even consider this tuning, just best practice like changing the default db_block_size or instance name.

On Mon, 20 Oct 2003 Jared.Still_at_radisys.com wrote:

> Interesting question.
>
> Some initial thoughts on that are that latches don't actually consume
> much CPU. In a poorly written app (or in the extremely rare event
> of a database bug, but probability of that is so low as to not be
> worth discussing) you may encounter latch contention that will cause
> your application to run very slowly, but not actually use too much
> cpu in the process.
>
> Rather than worry about latch contention on multiple databases, you
> might want to watch for excessive LIO's ( which do consume cpu), and
> consider the worst case load for each database in terms of physical
> IO, CPU and memory (it seems that saying RAM has become politically
> incorrect, as you are really dealing with virtual memory. but you really
> do
> want your databases to fit in RAM), and determine if your server is up
> to the task.
>
> Don't forget to consider the IO generated by backups.
>
> Notice I didn't say anything about db_block_lru_latches. You can rarely
> tune problems away by twisting the knobs.
>
> Now watch someone more knowledgable chime in and make me
> wish I hadn't replied.
>
> Jared
>
>
>
>
>
> <rgaffuri_at_cox.net>
> Sent by: ml-errors_at_fatcity.com
> 10/20/2003 07:39 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: db_block_lru_latches and servers with multiple instances
>
>
> We have some servers with 6-8 instances. These are typically staging
> instances and maybe 1 low transaction production instance.
>
> We keep multiple instances on one server strictly for cost. Licensing
> additional servers would be prohibitively expensive so we bought higher
> end servers and stacked them with multiple instances.
>
> My concern here is with latch contention. I have read that its often best
> to have db_block_lru_latches set to 2 *CPU. We have 4 CPUs on each server
> and are an NAS hard disk array from network appliances.
>
> Do I have to take into consideration the additional instances on the
> server when considering latch contention?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve Rospo
  INET: srospo_at_watchmark.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).