Parsing Oracle's method for select locking and query for locks
Oracle to select Lock method
Copy Code code as follows:
CREATE TABLE Test (a number,b number);
INSERT into test values (1,2);
INSERT into test values (3,4);
INSERT into test values (8,9);
Commit
---session 1 simulation Select a number
Sql> SELECT * FROM Test where A is =1 for update skip locked;
A B
---------- ----------
1 2
---session 2 to a=1 select again
Sql> SELECT * FROM Test where a = 1 for update skip locked;
No rows selected
--Session 3 full table Select
Sql> SELECT * FROM test for update skip locked;
A B
---------- ----------
3 4
8 9
Sql>
Second, query those users, manipulated those tables caused the lock machine
Copy Code code as follows:
SELECT S.username,
Decode (L.type, ' TM ', ' TABLE LOCK ',
' TX ', ' ROW LOCK ',
NULL) Lock_level,
O.owner,o.object_name,o.object_type,
S.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
From V$session s,v$lock l,all_objects o
WHERE L.sid = S.sid
and L.ID1 = o.object_id (+)
And S.username is not Null
Third, detect the locked table, and lock the table's session ID
Select a.session_id, b.* from V$locked_object a,all_objects b
where a.object_id=b.object_id
Iv. identify the corresponding SQL statement
Copy Code code as follows:
Select vs. Sql_text,vsess.sid,vsess. Serial#,vsess. Machine,vsess. Osuser
, Vsess. Terminal,vsess. Program,vs. Cpu_time,vs. Disk_reads
From V$sql vs,v$session vsess
Where vs. Address=vsess. Sql_address
and vsess.sid= (session ID identified above)
Five,
1. Check which process is locked
Check V$db_object_cache view:
SELECT * from V$db_object_cache WHERE owner= ' The owning user ' and locks!= ' 0 ';
2. Check which SID, through the SID can know which session.
Check v$access view:
SELECT * from v$access WHERE owner= ' The owning user ' and Name= ' the process name ' just found ';
3. Identify SIDs and serial#
Check v$session view:
SELECT sid,serial#,paddr from v$session WHERE sid= ' SID just found '
Check v$process view:
SELECT the SPID from v$process WHERE addr= ' just found paddr ';
4. Killing process
(1). Kill the Oracle Process first:
ALTER SYSTEM KILL Session ' detected SID, detected serial# ';
(2). Then kill the operating system process:
KILL-9 just identified the SPID.
Or
Orakill just identified the SPID that SID just identified.
Vi. finding SQL that consumes the most system resources
Copy Code code as follows:
--cpu
Select B.sql_text,
A.buffer_gets,
A.executions,
A.buffer_gets/decode (a.executions, 0, 1, a.executions),
C.username
From V$sqlarea A,
V$sqltext_with_newlines B,
Dba_users C
where a.parsing_user_id = c.user_id
and a.address = b.address
ORDER BY a.buffer_gets Desc, b.piece
Copy Code code as follows:
--io
Select B.sql_text,
A.disk_reads,
A.executions,
A.disk_reads/decode (a.executions, 0, 1, a.executions),
C.username
From V$sqlarea A,
V$sqltext_with_newlines B,
Dba_users C
where a.parsing_user_id = c.user_id
and a.address = b.address
ORDER BY a.disk_reads Desc, b.piece
Copy Code code as follows:
Select S.sid,s.value "CPU Used"
From V$sesstat s,v$statname n
where s.statistic#=n.statistic# and N.name= ' CPU used by this session '
and s.value>0
ORDER BY 2 Desc;
