In Oracle, a latch wait happens when one session needs to use a latch (a type of lock) to access shared data but has to wait because another session is currently using that latch. Imagine you have a library book that others want to borrow. If someone else has the book checked out, you have to wait until they return it before you can borrow it.

For example, let's say two sessions, Session A and Session B, are trying to access a shared database buffer that is protected by a latch:

1. Session A wants to read data from the buffer and tries to acquire the latch.

2. If the latch is free, Session A quickly gets it and reads the data.

3. Meanwhile, Session B also needs to read data from the same buffer and tries to acquire the latch.

4. Since the latch is already held by Session A, Session B has to wait (Latch Wait event) until Session A releases the latch.

5. Once Session A is done, it releases the latch, and Session B can then acquire it and read the data.

This waiting for the latch can cause delays in processing, especially in systems with high concurrency where many sessions are trying to access the same resources simultaneously.

Latch Wait events can occur due to various reasons

such as high concurrency for a particular data structure, a poorly tuned database, or excessive parallelism. Monitoring and tuning the database parameters, optimising SQL queries, and reducing the level of parallelism are some of the ways to address Latch Wait events.

It is important to note that not all Latch Waits are problematic, and some level of contention is expected in a multi-user database system. However, excessive or persistent Latch Waits can cause performance issues and should be investigated and addressed.

Oracle latch free wait events happen when a session wants a latch but can't get it because another session is using it. This wait event occurs when the session has to wait for the latch to become available before it can continue.

For example, imagine you have two sessions in your database. Session A wants to update a row in a table, so it tries to get a latch on that table. However, Session B is already using the latch because it's reading from the same table. So, Session A has to wait until Session B releases the latch before it can proceed with its update.

This type of wait event can happen due to issues like multiple sessions contending for the same resources, poorly written SQL queries that create excessive latch contention, or too much parallel activity in the system. To resolve these issues, you might need to adjust database settings, optimize your SQL queries, or reduce the level of parallel processing in the system.

The library cache in Oracle is like a big storage space in the system's memory (SGA) where it keeps important stuff like shared SQL statements, PL/SQL code, and object definitions. This helps speed up things because the database doesn't have to redo the same work over and over.

Now, imagine the library cache latch as a key to a room. Only one person can have that key at a time. When someone needs to use something from the library cache, they first get this key (the latch). While they have the key, they can read or change things in the library cache. Once they're done, they give back the key so someone else can use it.

For example, let's say there are two users trying to use the same SQL query stored in the library cache. User A wants to run the query, so they get the latch key, use the query, and then return the key. Meanwhile, User B waits for the key because only one person can use the query at a time. Once User A is done, User B gets the key and runs the query. This way, the library cache latch controls who can use what in the library cache at any given time, preventing conflicts.

Contentions for the shared pool and library cache latches are mainly due to intense hard parsing. A hard parse applies to new cursors and cursors that are aged out and must be re-executed. Excessive hard parsing is common among applications that primarily use SQL statements with literal values. A hard parse is a very expensive operation, and a child library cache latch must be held for the duration of the parse.

This event occurs when another session is modifying the contents which is related to dictionary cache (or row cache). This event is often associated with high parse activity but may occur for other reasons, such as manipulation of rollback segments.