Tanel
I was testing latchprfx.sql on 11R2 but seems not working ? any idea

Regards

Hi Tanel,

I am confused about a trivial issue. I was reading the following excerpt from an article:

A Concepual Knowledge on Oracle Blocks Read Algorithm is as under :

1. User Issues a Query.

2. Query is Parsed and Optimal Execution path is generated and stored in the Library Cache.

3. Based on the Execution Path, required Index or Table block is searched in the the Cache.

4. If the block is not found in the cache, a disk read request is made, and the block is read into the Cache.

5. The block, from the Cache is then read into a private memory area (UGA) of the User.

6. Once the Block is read into the private memory of the User, the required row is fetched.

http://viveklsharma.wordpress.com/2010/03/04/consistent-gets-myth/

I was a bit shocked by steps number 5 & 6. My own perception was that the Oracle server process will read only selected information from the buffer cache into the UGA.

What is your take on this?

Many thanks.

Hi Tanel,

We faced the issue of library cache latch contentions due to which CPU was 0% idle continuously. There was one index rebuild session which was holding the library cache lock. Others were waiting with “library cache: Mutex X”. Some times I was able to see other session coming and going with “Library cache lock”. But index rebuild was there.

I was not able to check which session was holding lock. Whenever I used to run the query to check the holder of library cache lock, it was displaying “library cache: Mutex X” and never used come back.

So I decided to kill the index rebuild. But it’s Journal table did not clean up after killing the index rebuild and still I was able to see that index rebuild session which was holding the Library cache lock. Manual clean up of the journal table was also stuck.

Here my question is what can we do in this case? This database was critical. I tried for around 25 mins and then decided to go for failover of the database to standby.

Thanks,
Ashish