Oracle latch: library cache causes database suspension failure

This ordinary Thursday, like the past, goes to the company, opens a computer, and receives emails. In a few minutes, I received a text message with a mobile phone alarm. I checked it and put it there. I was able to receive hundreds of alarm messages in a day, so I lost my head. After a few minutes, I received an alarm for the same database and gave a look at it. However, I was very vigilant. I received the alarm for the third time. I knew that the database had a problem and quickly connected to the Intranet.

In the Sun 5.1 system, DB 11.2.0.2. the login process is a hard process. It takes more than 30 seconds to log on to the system. If you do not need to check it, you will know that the CPU is definitely 100%.

Oracle @ h25k06dc $ vmstat 5 5

Kthr memory page disk faults cpu

R B w swap free re mf pi pofr de sr m5 m6 m7 m1 in sycs us sy id

0 0 0120080864 63113000 122 721 266 175 0 0 5 24 0 5 174 1590 7847 9 1 90

308 0 0117751600 66781304 194 616 9 9 0 0 3 0 1 3 2444 81080 97 3 0

305 0 0117752440 66782280 160 556 0 6 0 0 5 0 4 2430 84445 97 3 0

310 0 0117751872 66780480 165 718 0 2 0 0 3 0 3 2399 74438 97 3 0

307 0 0117752296 66782264 77 344 0 3 2 0 0 3 0 3 2319 82768 97 3 0

The numbers in the first column of 300 + are very conspicuous, and usually dozens of numbers are very tight. However, if you have seen more than 600, there will be no big fuss. Follow the steps to see the waiting event:

Select event, count (*) fromv $ session group by event;

The Command did not respond after several minutes, but it could not wait. A new window was opened to prepare for a hanganalyze, and the cup did not respond.

After waiting for a while, I still did not respond. This kind of thing happened too much and it was not so panic. During this period, I received several phone calls from the Bureau, hung up the phone, and continued to struggle, you can only use the last one to kill the process.

This is intended to be done with a command:

Ps-ef | grepLOCAL = NO | grep-v grep | awk '{print $2}' | xargs kill-9

During the warranty period, ps first:

Ps-ef | grepLOCAL = NO | grep-v grep | awk '{print $2 }'

Returns hundreds of records, a little more. Generally, if the data can be normally connected and operated, it is best to first locate the specific session and kill the corresponding process of the session, obviously, this morning's character is poor and cannot be identified.

Here, there are too many processes with LOCAL = NO, so it is impossible to kill them all. One kill part may be mitigated.

Each time a random batch of kill is killed, and the first batch of kill is finally reflected.

Oracle @ h25k06dc $ vmstat 5 5

Kthr memory page disk faults cpu

R B w swap free re mf pi pofr de sr m5 m6 m7 m1 in sycs us sy id

0 0 0 120078656 63119016 122 722 266 176 00 5 24 5 176 1597 7899 9 1 90

0 0 0 118483144 67508104 61 1159 0 0 0 0 220 0 22 5349 28461 55 4 42

0 0 0 118487648 67513760 38 726 0 0 0 0 0 0 0 0 4435 24430 10103 52 4 44

0 0 0 118488136 67514264 36 482 0 0 0 0 0 7 0 7 3451 22461 10015 51 3 47

0 0 0 118489392 67515480 58 630 0 0 0 0 1 0 0 14087 29228 49 4 47

After a look at the time, the processing took about 20 minutes, a little long, and the response speed needs to be improved. A snapshot was created quickly to analyze the cause of the fault:

Here the event is obvious, latch: library cache.

When we compile packages, stored procedures, functions, and views, Oracle first obtains a library cache lock on the handle of these objects, then obtain the pin on the heap of these objects, so that other processes do not modify the definition of these objects during compilation, or delete the objects.

When a session performs hard parsing on SQL statements, this session must obtain librarycache lock. AWR is also obvious here, and hard Parsing is too high.

The v $ active_session_history view captures the latch: library cache session and SQL. Three of the SQL statements do not use the bound variables, resulting in high hard parsing.

The SQL statement is submitted to the O & M team and continues to focus on the database. Maybe the CPU usage of this database may be 100% again some day.

I have previously written an article on library cache lock separately. For details, refer to the solution to Oracle Library Cache Lock.