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

This article focuses on "how to quickly clean up kill sessions with oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "oracle how to quickly clean up kill sessions"!

Today, the developer reported that when executing a program update, hang lived and checked that it was a small table with only more than 3000 rows of data. The first reaction is to have a lock, put all the session kill of the instance, execute update or hang live, and execute the select under the where condition very quickly. So I thought there should be a lock on the 2 node, so I executed the query:

Select * from gv$lock where id1=383105

SQL > select * from gv$lock where id1=383105

INST_ID ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

--

2 0000000110AF61B0 0000000110AF6210 1333 TM 383105 0 3 0 6819 2

Sure enough, there is also a session holding lock on the 2 node.

Connect to 2 nodes and execute the query:

SQL > select sid,serial#,osuser from v$session where sid=1333

SID SERIAL# OSUSER

1333 22559 6005821

Alter system kill session '133pm 22559'

After killing the session, query again, and find that 1333 of the session still exists:

SQL > select sid,serial#,osuser from v$session where sid=1333

SID SERIAL# OSUSER

1333 22559 6005821

So I want to kill from the system layer, but I can query less than 1333 of the spid through the following sql

Select p.pidcamera p.spidres.sidrecovers.serial# from v$process precedicsession s where s.paddr=p.addr and s.sid=1333

After query, it is found that after kill session in Oracle, Oracle simply points the paddr of the relevant session to the same virtual address. At this point, v$process and v$session lose their association, and the process is interrupted. Then Oracle waits for PMON to clear the Session. So it usually takes a long time to wait for a Session exit marked Killed. If you try to execute the task again by the process of Kill at this time, you will immediately receive a prompt that the process is interrupted and process exits, and Oracle will immediately start PMON to clear the session. This is treated as an exception interrupt.

Locate the previous paddr based on the following sql:

Select p.addr from v$process p where pid 1

Minus

Select s.paddr from v$session s

07000107C8C050F8

07000107DCC19D88

Find spid based on v$process 's addr:

Select * from v$process where addr in ('07000107C8C050F8, 07000107DCC19D88')

Then find the system process number and kill it. Since some of the processes do not retain the execution results, we have to record the sql.

Oracle@cq2:] ps-ef | grep 8847870

Oracle 8847870 1 3 Aug 06-734 ora_pz99_CQRPT2

Oracle 10420652 13107576 0 11:11:01 pts/1 0:00 grep 8847870

[oracle@cq2:] ps-ef | grep 14221746

Oracle 12583324 13107576 0 11:11:22 pts/1 0:00 grep 14221746

Oracle 14221746 1 0 08:16:07-0:04 oracleCQRPT2 (LOCAL=NO)

[oracle@cq2:] kill-9 14221746

[oracle@cq2:] ps-ef | grep 14221746

Oracle 12583046 13107576 0 11:12:11 pts/1 0:00 grep 14221746

At this point, I believe you have a deeper understanding of "how oracle quickly cleans up kill sessions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.