SQL> create table t1(c1 number, c2 number);
테이블이 생성되었습니다.
SQL> insert into t1 values(1 , 1);
1 개의 행이 만들어졌습니다.
SQL> insert into t1 values(2 , 2);
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
-- 각 세션에 테이블 권한 주기
SQL> grant select, insert, update, delete on t1 to testuser;
권한이 부여되었습니다.
SQL> grant select, insert, update, delete on t1 to scott;
권한이 부여되었습니다.
-- 세션#1
SQL> create synonym t1 for sys.t1;
동의어가 생성되었습니다.
SQL> update t1 set c2 = 2 where c1 = 1;
1 행이 갱신되었습니다.
-- 세션#2
SQL> create synonym t1 for sys.t1;
동의어가 생성되었습니다.
SQL> update t1 set c2 = 2 where c1 = 2;
1 행이 갱신되었습니다.
-- 세션#1
SQL> update t1 set c2 = 2 where c1 = 2;
...(대기 중)...
-- 세션#2
SQL> update t1 set c2 = 2 where c1 = 1;
...(대기 중)...
-- 세션#1
SQL> update t1 set c2 = 2 where c1 = 2;
update t1 set c2 = 2 where c1 = 2
*
1행에 오류:
ORA-00060: 자원 대기중 교착 상태가 검출되었습니다
트레이스 파일 조회
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00090002-00002a10 20 123 X 28 112 X
TX-0002000f-0000293a 28 112 X 20 123 X
session 123: DID 0001-0014-00000104 session 112: DID 0001-001C-00000011
session 112: DID 0001-001C-00000011 session 123: DID 0001-0014-00000104
Rows waited on:
Session 123: obj - rowid = 00014864 - AAAUhkAABAAA7SKAAB
(dictionary objn - 84068, file - 1, block - 242826, slot - 1)
Session 112: obj - rowid = 00014864 - AAAUhkAABAAA7SKAAA
(dictionary objn - 84068, file - 1, block - 242826, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 112:
sid: 112 ser: 37 audsid: 6670546 user: 89/TESTUSER flags: 0x100045
pid: 28 O/S info: user: SYSTEM, term: MYMAIN, ospid: 904
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: MYMAIN\Administrator, term: MYMAIN, ospid: 2828:2832
machine: MSHOME\MYMAIN program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update t1 set c2 = 2 where c1 = 1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=6sj55xg10kudc) -----
update t1 set c2 = 2 where c1 = 2
-- 오브젝트 번호로 테이블명 조회
SQL> select object_name from dba_objects where data_object_id = 84068;
OBJECT_NAME
------------------------------
T1
-- 로우 아이디에 해당하는 로우 조회
SQL> select * from t1 where rowid = 'AAAUhkAABAAA7SKAAB';
C1 C2
---------- ----------
2 2
ORA- 04020 : deadlock detected while trying to lock object <OBJECT NANE>
----------------------------------------------------------
Object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
797992F8 7AA06C48 79FA5554 X 7AA06C48 79F8B3DO X
----------------------------------------------------------
V$LATCHHOLDER 뷰의 정의
desc v$latchholder
이름 널? 유형
--------- -------- -----------------
PID NUMBER
SID NUMBER
LADDR RAW(4)
NAME VARCHAR2(64)
GETS NUMBER
세션#1 아주 많은 수의 SQL 문장을 하드 파싱하는 작업 수행
exec dbms_application_info.set_client_info('session1');
declare
v_cnt number;
begin
for idx in 1..1000 loop
for r in (select table_name from dba_tables) loop
begin
execute immediate 'select count(*) from ' || r.table_name || ' where 1 = 0' into v_cnt;
exception when others then
null;
end;
end loop;
end loop;
end;
/
세션#2 에서 세션#1 이 작업을 수행하면서 어떤 래치를 얼마나 획득하는지 프로파일링함
col sid new_value sid1
select sid from v$session where client_info = 'session1';
-- simple profiling
select /*+ no_query_transformation ordered use_nl(h) */
name, count(*) as hits
from (select level from dual connect by level <= 100000) x
, v$latchholder h
where sid = &sid1
group by name
order by 2 desc
;
NAME HITS
---------------------------------------- ----------
shared pool 3783
row cache objects 867
enqueues 370
cache buffers chains 264
enqueue hash chains 243
shared pool simulator 108
simulator lru latch 45
SQL memory manager workarea list latch 4
simulator hash latch 2
MinActiveScn Latch 1
10 개의 행이 선택되었습니다.
V$LATCHHOLDER 뷰의 베이스 테이블인 X$KSUPRLAT 테이블을 이용하면 보다 다양한 정보를 얻을 수 있음
desc x$ksuprlat
이름 널? 유형
------------ -------- -----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSUPRPID NUMBER
KSUPRSID NUMBER
KSUPRLLV NUMBER
KSUPRLTY NUMBER
KSUPRLAT RAW(4)
KSUPRLNM VARCHAR2(64)
KSUPRLMD VARCHAR2(64)
KSULAWHY NUMBER
KSULAWHR NUMBER
KSULAGTS NUMBER
-- get current time
SQL> col cur_time new_value cur_time
SQL> select dbms_utility.get_time as cur_time from dual;
CUR_TIME
----------
1245612
SQL> col latch_name format a20
SQL> col code_name format a20
-- 작업이 수행되는 동안 대기이벤트가 잠깐씩 나타나므로 시간을 100에서 10000으로 늘려서 테스트함.
SQL> select /*+ opt_param('_optimizer_connect_by_cost_based', 'false')
2 NO_QUERY_TRANSFORMATION */
3 latch_name,
4 code_name,
5 trunc(100*(latch_hits/(max(total_hits) over())), 1) as held_pct,
6 trunc(1000*5 * (latch_hits/(max(total_hits) over())), 1) as held_time,
7 trunc(1000*5 * ((latch_hits/dist_hits) /(max(total_hits) over())), 1) as avg_held_time
8 from (select /*+ ordered use_nl(l) */
9 l.sid,
10 latch_name,
11 code_name,
12 NULL as sql_id,
13 count(*) as latch_hits,
14 count(distinct gets) as dist_hits,
15 max(lvl) as total_hits
16 from (select /*+ no_merge */
17 level as lvl
18 from dual
19 connect by level <= 1000000000000
20 and (dbms_utility.get_time - &cur_time) <= 10000) t1,
21 (select /*+ no_merge */
22 ksuprsid as sid,
23 ksuprlat as latch_addr,
24 ksulagts as gets,
25 (select ksllwnam from sys.x$ksllw where indx = ksulawhr) as code_name,
26 (select ksusesqi from sys.x$ksuse where indx = ksuprsid) as sql_id,
27 ksuprlnm as latch_name,
28 to_char(ksulawhy, 'xxxxxxxxxxxxxxxx') as obj_why
29 from sys.x$ksuprlat
30 where ksuprsid = &sid1) l
31 where latch_name like '%%'
32 group by l.sid, latch_name, code_name
33 order by count(*) desc
34 );
구 20: and (dbms_utility.get_time - &cur_time) <= 10000) t1,
신 20: and (dbms_utility.get_time - 1245612) <= 10000) t1,
구 30: where ksuprsid = &sid1) l
신 30: where ksuprsid = 131) l
LATCH_NAME CODE_NAME HELD_PCT HELD_TIME AVG_HELD_TIME
-------------------- -------------------- ---------- ---------- -------------
shared pool kghalo 1.5 78.9 0
shared pool kghfrh .7 38 0
shared pool kghfre .5 29 0
row cache objects kqrpre: find obj .5 26.5 0
shared pool kghupr1 .5 25.3 0
shared pool kghalp .3 19.4 0
cache buffers chains kcbgtcr: fast path ( .3 16.3 0
cr pin)
enqueues ksqdel .2 11.9 0
row cache objects kqreqd .1 8.9 0
row cache objects kqreqd: reget .1 7.1 0
enqueues ksqgel: create enque .1 6.5 0
ue
enqueue hash chains ksqrcl .1 6.3 0
enqueue hash chains ksqgtl3 .1 5.7 0
shared pool simulato kglsim_unpin_simhp: 0 3.6 0
r fast path
SQL memory manager l qesmmIQueryRefreshBo 0 1.8 0
atch und
simulator lru latch kcbs_simulate: simul 0 1.8 0
ate set
row cache objects kqrso 0 .2 0
SQL memory manager w qesmmIRegisterWorkAr 0 .1 0
orkarea list latch ea:2
SQL memory manager w qesmmIRefreshBound 0 .1 0
orkarea list latch
cache buffers chains kcbgtcr: fast path 0 .1 0
SQL memory manager w qesmmIUnRegisterWork 0 0 0
orkarea list latch Area
MinActiveScn Latch ktucloGetGlobalMinSc 0 0 0
n
cache buffers chains kcbrls_2 0 0 0
shared pool simulato kglsim_unpin_simhp 0 0 0
r
simulator hash latch kcbsacc: lookup dba 0 0 0
25 개의 행이 선택되었습니다.