Analyze the method of oracle locking select and the query of lock

Analyze the method of oracle locking select and the query of lock
1. Lock method of oracle to select

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  Simulated selection 1 Number 
SQL> select * from test where a =1 for update skip locked;
         A          B
---------- ----------
         1          2
---session 2  Right a=1 Re-proceed select
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>

2. Query those users, manipulate those tables and cause lock machine

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

3. Find out the locked table and the session ID that locked this table
select a.session_id ,b.* from v$locked_object a,all_objects b
where a.object_id=b.object_id
4. Find the corresponding SQL statement

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=( The conversations found out above ID)

5.
1. Check which process is locked
Check the V $DB_OBJECT_CACHE view:
SELECT * FROM V $DB_OBJECT_CACHE WHERE OWNER = 'The user of the procedure' AND LOCKS! = '0';

2. Check which SID it is, and you can know which SESSION it is through SID.
Check the V $ACCESS view:
SELECT * FROM V $ACCESS WHERE OWNER= 'User to which the process belongs' AND NAME= 'Process name just found';

3. Identify SID and SERIAL #
Check the V $SESSION view:
SELECT SID, SERIAL #, PADDR FROM V $SESSION WHERE SID = 'SID just found'

Check the V $PROCESS view:
SELECT SPID FROM V $PROCESS WHERE ADDR = 'PADDR just found';

4. Kill the process
(1). Kill the ORACLE process first:
ALTER SYSTEM KILL SESSION 'SID detected, SERIAL detected #';

(2). Kill the operating system process again:
KILL-9 SPID just detected
Or
ORAKILL just found out SID just found out SPID
6. Find the SQL that consumes the most system resources

--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

--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

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;