Category Archives: Uncategorized

An interesting question from Valentin Nikotin

Posted on by Posted in Uncategorized 1,690 Page views Leave a comment

Test table:

create table tclob(c clob);

What is this code going to put out from the following blocks with and without “rollback”:

Task 1

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1:=’1′;
insert into tclob values(cl1) returning c into cl2;
cl3:=cl2;
dbms_lob.append(cl3,’2′);
select c into cl4 from tclob;
— rollback;

dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]

Task 2

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1 := ‘1’;
insert into tclob values (cl1) returning c into cl2;
cl3 := cl2;
dbms_lob.append(cl2, ‘2’);
select c into cl4 from tclob;
— rollback;

dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]
Task 3

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1 := ‘1’;
insert into tclob values (cl1) returning c into cl2;
cl3 := cl2;
dbms_lob.append(cl2, ‘2’);
dbms_lob.append(cl3, ‘3’);
select c into cl4 from tclob;
— rollback;

dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]
Task 4

[sourcecode language=”sql”]
declare
cl1 clob;
cl2 clob;
cl3 clob;
cl4 clob;
begin
cl1 := ‘1’;
insert into tclob values (cl1) returning c into cl2;
cl3 := cl2;
dbms_lob.append(cl2, ’22’);
dbms_lob.append(cl3, ‘3’);
dbms_lob.append(cl2, ’44’);
select c into cl4 from tclob;
— rollback;
dbms_output.put_line(cl1);
dbms_output.put_line(cl2);
dbms_output.put_line(cl3);
dbms_output.put_line(cl4);
end;
/
[/sourcecode]

[collapse]

Check it both in Windows and Solaris/Linux 🙂

Explanation
It is clear that this bug is platform-depending and that the matter is in specifics of working with memory. The answer lies in the fact that cl3 and cl2 have unsynchronized lengths, which means that Oracle “forgets” to change the lengths of all remaining variables, that point to this “clob”, and as every operation of changing cl2/cl3 in fact changes the same thing, it turns out that “superfluous” becomes overwritten.

A lot of “latch free dml allocation latch” in concurrent queries to v$lock

Posted on by Posted in Uncategorized 1,935 Page views Leave a comment

This problem is old actually, but it became possible to find out what is the real matter here and to deal with it only now thanks to the “Oracle Core” book by Jonathan Lewis.

Here is a quote from the chapter “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.

And this “fixed” table itself is in “v$lock”, which I had repeatedly seen in the plans for it:

Plan

[sourcecode language=”sql”]
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 |
——————————————————————————————–
[/sourcecode]

[collapse]

Now as we know the root of the problem, what’s left is to find out if we can escape unnecessary requests to X$KTADM, which seems possible considering that it’s a part of “union all”.
So we had to “parse” this v$lock, to get the full text of the query. To do this I began with getting the query text by tracing 10053:

Spoiler

[sourcecode language=”sql”]
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"
[/sourcecode]

[collapse]

But I was too lazy to format it after beautifier processing, so I decided to parse it progressively with v$fixed_view_definition. This led to a much better result:

10.2.0.4-11.2.0.1

[sourcecode language=”sql”]
/————————– 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)
[/sourcecode]

[collapse]

11.2.0.3

[sourcecode language=”sql”]
/————————– 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)
[/sourcecode]

[collapse]

By the way, as you can see gv$lock varies in different versions, which has to be taken into account (I haven’t tried it on 11.2.0.2 so I will update it later):
in 11.2.0.3 predicate “l.raddr=r.addr” was changed to concat(USERENV(‘Instance’),l.raddr)=concat(r.inst_id,r.addr).
Only particular blockings were queried from v$lock in my case, where most important of them were “user-locks”, which means they were of ‘UL’ type. That’s why after parsing the code, I had to get what locks are returned by each particular “union all” block. For this purpose I have created a modified GV$LOCK:
xt_gv$_lock for 11.2.0.3

[sourcecode language=”sql”]
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
/
[/sourcecode]

[collapse]

Now we can get relevance of block types ~ particular block:

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

Where “sq” is a number of “union all” block.
Or just get the block number using filter by block type. So, for example ‘UL’ will now be in the first block, and if we need to select them we can just make queries from this new view adding “sq=1” predicate to avoid torturing other blocks.

select * 
from xt_gv$lock l
where l.type='UL'
and l.sq=1 -- ul in first block only

During a load testing in a concurrency my modified query with “user locks” not only completely solved the problem with latches, but also increased the speed of query processing by 200 times and lowered the CPU usage.

Script files:
11.2.0.1: xt_gv$_lock_11_2_0_1.sql
11.2.0.3: xt_gv$_lock_11_2_0_3.sql