Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what is the session method to locate and kill the final blocking in the Oracle RAC environment". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Experimental environment:

Oracle RAC 11.2.0.4 (2 nodes)

1. Simulated failure: the session is blocked by cascade

two。 Conventional method: comb to find out the final blocking session

3. Improvement: find the final blocking session immediately

In fact, I have written a related article before:

How to locate the root cause of locked blocking sessions in Oracle databases

1. Simulated failure: the session is blocked by cascade

Preparatory work:

Here, I open two sessions in each instance to simulate the business session of RAC in load balancing mode:

Example 1: session 1, session 2

Example 2: session 3, session 4

Do the following on the timeline of time 1-> time 2-> time 3-> time 4:

Time point 1:

The execution statement in session 1 (INS1-session1) of instance 1 is not committed or rolled back:

Select * from v$mystat where rownum = 1 * update emp set sal = 8000 where empno = 7788

Point in time 2:

Execute the statement in session 3 (INS2-session3) of example 2:

Select * from v$mystat where rownum = 1: delete from emp where empno = 7839: update emp set job = 'MANAGER' where empno = 7788: rollback

Point in time 3:

Execute the statement in session 4 (INS2-session4) of example 2:

Select * from v$mystat where rownum = 1 per update emp set sal = 15000 where empno = 7839 per rollback

Point in time 4:

Execute the statement in session 2 (INS1-session2) of example 1:

Select * from v$mystat where rownum = 1: update emp set job = 'CEO' where empno = 7839

At this point, you can see that the sessions operating at the next three points in time are all hang-hosted, obviously blocked. The phenomena of the four conversations are as follows:

So who on earth are they blocked by? The following will be analyzed in detail.

two。 Conventional method: comb to find out the final blocking session

We usually go to GV$SESSION to query the blocking_session to see if the blocking_session is blocked by other sessions until we find the source.

-- blockingset lines 180col program for a30col machine for a20select inst_id, SID, SERIAL#, event, machine, sql_id, blocking_session, blocking_instance from gv$session where blocking_session is not null

The results are as follows:

SYS@jyzhao1 >-- blockingSYS@jyzhao1 > set lines 180SYS@jyzhao1 > col program for a30SYS@jyzhao1 > col machine for a20SYS@jyzhao1 > select inst_id, 2 SID, 3 SERIAL#, 4 event, 5 machine, 6 sql_id, 7 blocking_session, 8 blocking_instance 9 from gv$session 10 where blocking_session is not null INST_ID SID SERIAL# EVENT MACHINE SQL_ID BLOCKING_SESSION BLOCKING_INSTANCE -1 146 6283 enq: TX-row lock contention jyrac1 052gy77vp276s 25 2 2 25 10250 enq: TX-row lock contention jyrac2 3t2npbvdcf2d2 150 1 2 145 32069 enq: TX-row lock contention jyrac2 0ct116qw46shq 25 2SYS@jyzhao1 >

You can see that the session of the sid=146 of instance 1 and the session of the sid=145 of instance 2 are blocked by the session of the sid=25 of instance 2, while the session of the sid=25 of instance 2 is blocked by the session of the sid=150 of instance 1. This example only simulates a few conversations and can be located quickly, but if it is a real fault, more than a few conversations are likely to be affected, although it can be found out slowly, but it will be dazzling after all. How could our arrogant DBA be willing to do such a thing all the time?

3. Improvement: find the final blocking session immediately

Previously, I have been using a script to find out the final blocking session in a single instance or in an environment where the business only runs on a certain node:

-- cascade blockingset lines 200 pages 100col tree for a30col event for a40select * from (select a.sid, a.serializations, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path (SID,'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.