Большое количество latch free: dml allocation latch при конкурентных запросах к v$lock


Проблема эта в общем-то старая, но только сейчас благодаря книге "Oracle Core" Джонатана Льюиса удалось узнать в чем собственно проблема и справиться с ней.
Цитата из главы "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 |
--------------------------------------------------------------------------------------------
Теперь уже зная корень проблемы, остается только узнать можно ли избавиться от лишних обращений к X$KTADM, что интуитивно кажется возможным учитывая, что она часть "union all".
То есть надо было "разобрать" этот 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$fixed_view_definition. Так получилось гораздо лучше:
/-------------------------- 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)        
Кстати, как можно заметить есть разница в gv$lock между версиями и ее нужно учесть(на 11.2.0.2 я еще не смотрел - позже подправлю):
в 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.

Файлы скриптов:

Comments

Отправка комментария