News Stay informed about the latest enterprise technology news and product updates.

Load up on latches

Performance tuning tip #1 from "30 tips in 30 minutes, brought to you by the IOUG.

The following is performance tuning tip #1 from "30 tips in 30 minutes," brought to you by the IOUG. Return to the main page for more tips on this topic.

A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. A process acquires a latch when working with a structure in the SGA. It continues to hold the latch for the period of time it works with the structure. The latch is dropped when the process is finished with the structure. Each latch protects a different set of data, identified by the name of the latch. If a required latch is busy, the process requesting it spins, tries again and if still not available, spins again. The loop is repeated up to a maximum number of times determined by the initialization parameter _SPIN_COUNT. If after this entire loop, the latch is still not available, the process must yield the CPU and go to sleep. Initially is sleeps for one centisecond. This time is doubled in every subsequent sleep. This causes a slowdown to occur and results in additional CPU usage, until a latch is available. The CPU usage is a consequence of the "spinning" of the process. "Spinning" means that the process continues to look for the availability of the latch after certain intervals of time, during which it sleeps.

To increase the number of latches available to your database, increase the DB_BLOCK_LRU_LATCHES to a value of two times the number of CPUs on the server machine. The default value of this parameter of this parameter is half the number of CPUs on your machine. Increasing this value make more latches available and reduce latch contention.

Get more tips in minutes! Return to the main page.

About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.

IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.

Dig Deeper on Oracle database design and architecture

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Thank you this post answered my question very clearly helping me to resolve an issue in a very timely manner