Shulou(Shulou.com)06/01 Report--

Once the developer does the delete table operating system directly on the database server, which runs during the rush hour, resulting in unresponsive execution for a long time, and then shuts down the SQLPLUS client, runs the delete statement again, and causes the database to run slowly.

The inquiry operation process knows that it is caused by the lock table and the final processing; this paper only simulates the lock table processing process and can deal with the same problem quickly next time.

1. View wait events for the database

If you don't know the reason, you can analyze it by waiting for the event first.

SQL > select sid,EVENT from v$session_wait where wait_class'Idle'

SID EVENT

-

158 SQL*Net message to client

159enq: TX-row lock contention

SQL >

two。 Check to see if the database has locks

SQL > select * from v$lock where block=1

3. Query the lock lock to see who locks whom

Description: a line with a BLOCK of 1 indicates that the resource is locked by it. REQUEST indicates that the lock is needed.

SQL > SELECT sid, id1, id2, lmode, block,request, type

FROM V$LOCK

WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)

ORDER BY id1, request

SID ID1 ID2 LMODE BLOCK REQUEST TY

153 655397 308 6 1 0 TX

157 655397 308 0 0 6 TX

4. Query the SID,Serial#, user of the locked table, start time, and then kill the process.

SQL > select t1.sidrecoveryt1.serialtheatrical t1.username from v$session t1.logonrecording time where t1.sid T1 where t1.sid vanguard lockedwriting object T2 where t1.sid = t2.session_id order by t1.logon_time

SID SERIAL# USERNAME LOGON_TIME

-

15312 ABC 2014-11-17 09:19:33

157 106 ABC 2014-11-17 09:54:24

5. SID,SPID and other information can be further queried.

SQL > select s.username, s.osuser, s.sid, s. Serializations, p.spid, s. Programmer. Status

From v$session spark vested process p

Where s.paddr = p.addr and s.username is not null

USERNAME OSUSER SID SERIAL# SPID PROGRAM STATUS

--

ABC oracle 15312 4290 sqlplus@oradb (TNS V1-V3) INACTIVE

SYS oracle 150 12 4417 sqlplus@oradb (TNS V1-V3) ACTIVE

ABC oracle 157119 4830 sqlplus@oradb (TNS V1-V3) ACTIVE

Either kill it in oracle or kill it in the operating system. For the statement whose status is killed, the application can kill it as quickly as possible through the kill command of the operating system.

Alter system kill session '153JI 12'

Alter system kill session '157119'

Or in the operating system

Kill-9 4290

Kill-9 4830

6. Query the SID and related resources of locked tables

Description: out of the locked table and SQL, or combined with the above query for manual processing, you can also find SQL statements through SID.

Set pagesize 999

Set linesize 200

Col PROGRAM for a25

Col TERMINAL for a10

SQL > select s. Status.sidement s.serialstatus.spid

S.last_call_et as exec_seconds,t.sql_text as curr_sql

From gv$session sjorific vandalism process pmcmt vandalism instance I majored vault lock k, v$sqltext t

Where s.paddr = p.addr

And s.type! = 'BACKGROUND'

And s.lockwait = k.kaddr

And s.sql_hash_value = t.hash_value

And s.username is not null

STATUS SID SERIAL# SPID EXEC_SECONDS CURR_SQL

-

ACTIVE 15928 4072 1285 update test_lock set name='aa4' where id = 1

SQL >

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.