Цитата из главы "Latches for lock":
If the enqueue resource is in place already, then pick a row from the relevant enqueue structure (x$ksqeq, et al.), but to do this you have to get the associated enqueue latch to stop other people from picking the same enqueue row at the same time. The latch you need to acquire depends on the specific type of enqueue you are using; for example, if you want a row from x$ksqeq you need to get the enqueue latch but for a row from x$ktadm you need to get the dml allocation latch. Drop this latch as soon as you have made the enqueue row safe.
А эта fixed таблица и есть в v$lock, это я многократно видел в планах c ней:
DB11G/XTENDER> explain plan for select * from v$lock; Explained. Elapsed: 00:00:00.28 DB11G/XTENDER> @xplan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 3074737110 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 131 | 1 (100)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 131 | 1 (100)| 00:00:01 | |* 2 | HASH JOIN | | 1 | 98 | 1 (100)| 00:00:01 | |* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 | | 4 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 | | 5 | UNION-ALL | | | | | | |* 6 | FILTER | | | | | | | 7 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 | | 8 | UNION-ALL | | | | | | |* 9 | FIXED TABLE FULL | X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 | |* 10 | FIXED TABLE FULL | X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 | |* 11 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 | |* 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 | |* 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 | |* 14 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 | |* 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 | |* 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 | |* 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 | |* 18 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 | |* 19 | FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) | 1 | 33 | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
То есть надо было "разобрать" этот v$lock, чтобы получить полный текст запроса. Для этого я сначала получил текст запроса через трассировку 10053:
SELECT "S"."INST_ID" "INST_ID",
"L"."LADDR" "ADDR",
"L"."KADDR" "KADDR",
"S"."KSUSENUM" "SID",
"R"."KSQRSIDT" "TYPE",
"R"."KSQRSID1" "ID1",
"R"."KSQRSID2" "ID2",
"L"."LMODE" "LMODE",
"L"."REQUEST" "REQUEST",
"L"."CTIME" "CTIME",
DECODE("L"."LMODE", 0, 0, "L"."BLOCK") "BLOCK"
FROM (SELECT "GV$_LOCK"."LADDR" "LADDR",
"GV$_LOCK"."KADDR" "KADDR",
"GV$_LOCK"."SADDR" "SADDR",
"GV$_LOCK"."RADDR" "RADDR",
"GV$_LOCK"."LMODE" "LMODE",
"GV$_LOCK"."REQUEST" "REQUEST",
"GV$_LOCK"."CTIME" "CTIME",
"GV$_LOCK"."BLOCK" "BLOCK"
FROM ( (SELECT USERENV('INSTANCE') "INST_ID",
"V$_LOCK1"."LADDR" "LADDR",
"V$_LOCK1"."KADDR" "KADDR",
"V$_LOCK1"."SADDR" "SADDR",
"V$_LOCK1"."RADDR" "RADDR",
"V$_LOCK1"."LMODE" "LMODE",
"V$_LOCK1"."REQUEST" "REQUEST",
"V$_LOCK1"."CTIME" "CTIME",
"V$_LOCK1"."BLOCK" "BLOCK"
FROM (SELECT "GV$_LOCK1"."LADDR" "LADDR",
"GV$_LOCK1"."KADDR" "KADDR",
"GV$_LOCK1"."SADDR" "SADDR",
"GV$_LOCK1"."RADDR" "RADDR",
"GV$_LOCK1"."LMODE" "LMODE",
"GV$_LOCK1"."REQUEST" "REQUEST",
"GV$_LOCK1"."CTIME" "CTIME",
"GV$_LOCK1"."BLOCK" "BLOCK"
FROM ((SELECT "X$KDNSSF"."INST_ID" "INST_ID",
"X$KDNSSF"."ADDR" "LADDR",
"X$KDNSSF"."KSQLKADR" "KADDR",
"X$KDNSSF"."KSQLKSES" "SADDR",
"X$KDNSSF"."KSQLKRES" "RADDR",
"X$KDNSSF"."KSQLKMOD" "LMODE",
"X$KDNSSF"."KSQLKREQ" "REQUEST",
"X$KDNSSF"."KSQLKCTIM" "CTIME",
"X$KDNSSF"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KDNSSF" "X$KDNSSF"
WHERE BITAND("X$KDNSSF"."KSSOBFLG", 1) <> 0
AND ("X$KDNSSF"."KSQLKMOD" <> 0 OR
"X$KDNSSF"."KSQLKREQ" <> 0))
UNION ALL
(SELECT "X$KSQEQ"."INST_ID" "INST_ID",
"X$KSQEQ"."ADDR" "LADDR",
"X$KSQEQ"."KSQLKADR" "KADDR",
"X$KSQEQ"."KSQLKSES" "SADDR",
"X$KSQEQ"."KSQLKRES" "RADDR",
"X$KSQEQ"."KSQLKMOD" "LMODE",
"X$KSQEQ"."KSQLKREQ" "REQUEST",
"X$KSQEQ"."KSQLKCTIM" "CTIME",
"X$KSQEQ"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KSQEQ" "X$KSQEQ"
WHERE BITAND("X$KSQEQ"."KSSOBFLG", 1) <> 0
AND ("X$KSQEQ"."KSQLKMOD" <> 0 OR
"X$KSQEQ"."KSQLKREQ" <> 0))
) "GV$_LOCK1"
WHERE "GV$_LOCK1"."INST_ID" = USERENV('INSTANCE')
) "V$_LOCK1"
)
UNION ALL
(SELECT "X$KTADM"."INST_ID" "INST_ID",
"X$KTADM"."ADDR" "LADDR",
"X$KTADM"."KSQLKADR" "KADDR",
"X$KTADM"."KSQLKSES" "SADDR",
"X$KTADM"."KSQLKRES" "RADDR",
"X$KTADM"."KSQLKMOD" "LMODE",
"X$KTADM"."KSQLKREQ" "REQUEST",
"X$KTADM"."KSQLKCTIM" "CTIME",
"X$KTADM"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTADM" "X$KTADM" /*** 1 ***/
WHERE BITAND("X$KTADM"."KSSOBFLG", 1) <> 0
AND ("X$KTADM"."KSQLKMOD" <> 0 OR
"X$KTADM"."KSQLKREQ" <> 0)
)
UNION ALL
(SELECT "X$KTATRFIL"."INST_ID" "INST_ID",
"X$KTATRFIL"."ADDR" "LADDR",
"X$KTATRFIL"."KSQLKADR" "KADDR",
"X$KTATRFIL"."KSQLKSES" "SADDR",
"X$KTATRFIL"."KSQLKRES" "RADDR",
"X$KTATRFIL"."KSQLKMOD" "LMODE",
"X$KTATRFIL"."KSQLKREQ" "REQUEST",
"X$KTATRFIL"."KSQLKCTIM" "CTIME",
"X$KTATRFIL"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTATRFIL" "X$KTATRFIL"
WHERE BITAND("X$KTATRFIL"."KSSOBFLG", 1) <> 0
AND ("X$KTATRFIL"."KSQLKMOD" <> 0 OR
"X$KTATRFIL"."KSQLKREQ" <> 0)
)
UNION ALL
(SELECT "X$KTATRFSL"."INST_ID" "INST_ID",
"X$KTATRFSL"."ADDR" "LADDR",
"X$KTATRFSL"."KSQLKADR" "KADDR",
"X$KTATRFSL"."KSQLKSES" "SADDR",
"X$KTATRFSL"."KSQLKRES" "RADDR",
"X$KTATRFSL"."KSQLKMOD" "LMODE",
"X$KTATRFSL"."KSQLKREQ" "REQUEST",
"X$KTATRFSL"."KSQLKCTIM" "CTIME",
"X$KTATRFSL"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTATRFSL" "X$KTATRFSL"
WHERE BITAND("X$KTATRFSL"."KSSOBFLG", 1) <> 0
AND ("X$KTATRFSL"."KSQLKMOD" <> 0 OR
"X$KTATRFSL"."KSQLKREQ" <> 0))
UNION ALL
(SELECT "X$KTATL"."INST_ID" "INST_ID",
"X$KTATL"."ADDR" "LADDR",
"X$KTATL"."KSQLKADR" "KADDR",
"X$KTATL"."KSQLKSES" "SADDR",
"X$KTATL"."KSQLKRES" "RADDR",
"X$KTATL"."KSQLKMOD" "LMODE",
"X$KTATL"."KSQLKREQ" "REQUEST",
"X$KTATL"."KSQLKCTIM" "CTIME",
"X$KTATL"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTATL" "X$KTATL"
WHERE BITAND("X$KTATL"."KSSOBFLG", 1) <> 0
AND ("X$KTATL"."KSQLKMOD" <> 0 OR
"X$KTATL"."KSQLKREQ" <> 0))
UNION ALL
(SELECT "X$KTSTUSC"."INST_ID" "INST_ID",
"X$KTSTUSC"."ADDR" "LADDR",
"X$KTSTUSC"."KSQLKADR" "KADDR",
"X$KTSTUSC"."KSQLKSES" "SADDR",
"X$KTSTUSC"."KSQLKRES" "RADDR",
"X$KTSTUSC"."KSQLKMOD" "LMODE",
"X$KTSTUSC"."KSQLKREQ" "REQUEST",
"X$KTSTUSC"."KSQLKCTIM" "CTIME",
"X$KTSTUSC"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTSTUSC" "X$KTSTUSC"
WHERE BITAND("X$KTSTUSC"."KSSOBFLG", 1) <> 0
AND ("X$KTSTUSC"."KSQLKMOD" <> 0 OR
"X$KTSTUSC"."KSQLKREQ" <> 0))
UNION ALL
(SELECT "X$KTSTUSS"."INST_ID" "INST_ID",
"X$KTSTUSS"."ADDR" "LADDR",
"X$KTSTUSS"."KSQLKADR" "KADDR",
"X$KTSTUSS"."KSQLKSES" "SADDR",
"X$KTSTUSS"."KSQLKRES" "RADDR",
"X$KTSTUSS"."KSQLKMOD" "LMODE",
"X$KTSTUSS"."KSQLKREQ" "REQUEST",
"X$KTSTUSS"."KSQLKCTIM" "CTIME",
"X$KTSTUSS"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTSTUSS" "X$KTSTUSS"
WHERE BITAND("X$KTSTUSS"."KSSOBFLG", 1) <> 0
AND ("X$KTSTUSS"."KSQLKMOD" <> 0 OR
"X$KTSTUSS"."KSQLKREQ" <> 0))
UNION ALL
(SELECT "X$KTSTUSG"."INST_ID" "INST_ID",
"X$KTSTUSG"."ADDR" "LADDR",
"X$KTSTUSG"."KSQLKADR" "KADDR",
"X$KTSTUSG"."KSQLKSES" "SADDR",
"X$KTSTUSG"."KSQLKRES" "RADDR",
"X$KTSTUSG"."KSQLKMOD" "LMODE",
"X$KTSTUSG"."KSQLKREQ" "REQUEST",
"X$KTSTUSG"."KSQLKCTIM" "CTIME",
"X$KTSTUSG"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTSTUSG" "X$KTSTUSG"
WHERE BITAND("X$KTSTUSG"."KSSOBFLG", 1) <> 0
AND ("X$KTSTUSG"."KSQLKMOD" <> 0 OR
"X$KTSTUSG"."KSQLKREQ" <> 0))
UNION ALL
(SELECT "X$KTCXB"."INST_ID" "INST_ID",
"X$KTCXB"."KTCXBXBA" "LADDR",
"X$KTCXB"."KTCXBLKP" "KADDR",
"X$KTCXB"."KSQLKSES" "SADDR",
"X$KTCXB"."KSQLKRES" "RADDR",
"X$KTCXB"."KSQLKMOD" "LMODE",
"X$KTCXB"."KSQLKREQ" "REQUEST",
"X$KTCXB"."KSQLKCTIM" "CTIME",
"X$KTCXB"."KSQLKLBLK" "BLOCK"
FROM SYS."X$KTCXB" "X$KTCXB"
WHERE BITAND("X$KTCXB"."KSSPAFLG", 1) <> 0
AND ("X$KTCXB"."KSQLKMOD" <> 0 OR
"X$KTCXB"."KSQLKREQ" <> 0))) "GV$_LOCK"
WHERE "GV$_LOCK"."INST_ID" = USERENV('INSTANCE')
) "L",
SYS."X$KSUSE" "S",
SYS."X$KSQRS" "R"
WHERE "L"."SADDR" = "S"."ADDR"
AND "L"."RADDR" = "R"."ADDR"
/-------------------------- V$LOCK
select ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK
from GV$LOCK
where inst_id = USERENV('Instance')
/-------------------------- GV$LOCK
select s.inst_id INST_ID
,l.laddr ADDR
,l.kaddr KADDR
,s.ksusenum SID
,r.ksqrsidt TYPE
,r.ksqrsid1 ID1
,r.ksqrsid2 ID2
,l.lmode LMODE
,l.request REQUEST
,l.ctime CTIME
,decode(l.lmode, 0, 0, l.block) BLOCK
from v$_lock l, x$ksuse s, x$ksqrs r
where l.saddr=s.addr and l.raddr=r.addr
/-------------------- V$_LOCK -----------------------------------------
select LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK
from GV$_LOCK
where inst_id = USERENV('Instance')
/-------------------- GV$_LOCK -----------------------------------------
select USERENV('Instance') inst_id,laddr,kaddr,saddr,raddr,lmode,request,ctime, block
from v$_lock1
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktadm /**** 1 *****/
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatrfil
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatrfsl
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatl
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstusc
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstuss
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstusg
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,ktcxbxba,ktcxblkp,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktcxb
where bitand(ksspaflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
/------------------ V$_LOCK1
select LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK
from GV$_LOCK1
where inst_id = USERENV('Instance')
/------------------ GV$_LOCK1
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk
from x$kdnssf
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk
from x$ksqeq
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
/-------------------------- V$LOCK
select ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK
from GV$LOCK
where inst_id = USERENV('Instance')
/-------------------------- GV$LOCK
select s.inst_id INST_ID
,l.laddr ADDR
,l.kaddr KADDR
,s.ksusenum SID
,r.ksqrsidt TYPE
,r.ksqrsid1 ID1
,r.ksqrsid2 ID2
,l.lmode LMODE
,l.request REQUEST
,l.ctime CTIME
,decode(l.lmode, 0, 0, l.block) BLOCK
from v$_lock l, x$ksuse s, x$ksqrs r
where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr)
/-------------------- V$_LOCK -----------------------------------------
select LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK
from GV$_LOCK
where inst_id = USERENV('Instance')
/-------------------- GV$_LOCK -----------------------------------------
select USERENV('Instance') inst_id,laddr,kaddr,saddr,raddr,lmode,request,ctime, block
from v$_lock1
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktadm
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatrfil
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatrfsl
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatl
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstusc
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstuss
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstusg
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,ktcxbxba,ktcxblkp,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktcxb
where bitand(ksspaflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
/------------------ V$_LOCK1
select LADDR , KADDR , SADDR , RADDR , LMODE , REQUEST , CTIME , BLOCK
from GV$_LOCK1
where inst_id = USERENV('Instance')
/------------------ GV$_LOCK1
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk
from x$kdnssf
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim, ksqlklblk
from x$ksqeq
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
в 11.2.0.3 предикат l.raddr=r.addr изменился на concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr).
В моей ситуации из v$lock запрашивались только конкретные блокировки, причем основными из них были пользовательские, т.е. с типом 'UL' - user locks. Поэтому разобрав код, нужно было получить какие блокировки возвращает каждый конкретный блок union all. Для этого я создал модифицированный GV$LOCK:
create or replace view xt_gv$_lock as
with XT_GV$_LOCK as (
select 1 sq
,USERENV('Instance') inst_id,laddr,kaddr,saddr,raddr,lmode,request,ctime, block
from v$_lock1
union all
select 2
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktadm
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 3
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatrfil
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 4
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatrfsl
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 5
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktatl
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 6
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstusc
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 7
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstuss
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 8
,inst_id,addr,ksqlkadr,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktstusg
where bitand(kssobflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select 9
,inst_id,ktcxbxba,ktcxblkp,ksqlkses,ksqlkres,ksqlkmod,ksqlkreq, ksqlkctim,ksqlklblk
from x$ktcxb
where bitand(ksspaflg,1)!=0 and (ksqlkmod!=0 or ksqlkreq!=0)
)
select l.sq
,s.inst_id INST_ID
,l.laddr ADDR
,l.kaddr KADDR
,s.ksusenum SID
,r.ksqrsidt TYPE
,r.ksqrsid1 ID1
,r.ksqrsid2 ID2
,l.lmode LMODE
,l.request REQUEST
,l.ctime CTIME
,decode(l.lmode,0,0,l.block) BLOCK
from XT_GV$_LOCK l, x$ksuse s, x$ksqrs r
where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr)
/
create or replace public synonym xt_gv$lock for xt_gv$_lock
/
grant select on xt_gv$lock to public
/
Теперь можем получить на нагруженной базе соответствие типов блокировок ~ конкретному блоку:
with t as (select distinct sq,type from xt_gv$lock l)
select sq
,listagg(t.TYPE,',') within group(order by t.type)
from t
group by sq
Где sq - это номер блока union all.Или просто получить номер блока фильтром по нужному типу блокировки. Так, например, 'UL' будут в первом блоке и теперь, чтобы без проблем их выбирать можно запрашивать из этой новой вьюхи с добавлением предиката sq=1, чтобы не мучать зря другие блоки.
select * from xt_gv$lock l where l.type='UL' and l.sq=1 -- первый блок, где ul и бываютВ моем нагрузочном тесте в условиях конкуренции модифицированный запрос по user locks не только полностью решил проблему с латчами, да еще и приблизительно в 200 раз ускорил выполнение запроса и существенно снял нагрузку с cpu.
Файлы скриптов:
- Для 10.2 - 11.2.0.1: xt_gv$_lock_11_2_0_1.sql
- Для 11.2.0.3: xt_gv$_lock_11_2_0_3.sql
Сообщения
Just another Oracle developers
Comments
Отправить комментарий