Oracle SQL
  • LICENSE

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

Posted on June 30, 2012 by Sayan Malakshinov Posted in Uncategorized

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

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

[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

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"

[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

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

[collapse]

11.2.0.3
/-------------------------- 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)

[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

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
/

[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

« dbms_random in parallel
An interesting question from Valentin Nikotin »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL