1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Alternate option for v$Lock in oracle

Discussion in 'Server Administration and Options' started by Nirubama, Jul 4, 2012.

  1. Nirubama

    Nirubama Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Please tell me an alternate option for v$lock in oracle.My DBA says we should not use the v$lock views in the stored procedure coding.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This DBA gave you good reason for this? Because I can think of none. Even without fixed objects stats computed on the database the plan for "select * from v$lock;" shows no bottlenecks and returns quickly

    Code (SQL):
    WITHOUT fixed objects stats:
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1877249640
    --------------------------------------------------------------------------------------
    | Id  | Operation               | Name       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |            | 36264 |  3470K|     2 (100)| 00:00:01 |
    |*  1 |  HASH JOIN              |            | 36264 |  3470K|     2 (100)| 00:00:01 |
    |   2 |   FIXED TABLE FULL      | X$KSQRS    |  1312 | 23616 |     0   (0)| 00:00:01 |
    |*  3 |   HASH JOIN             |            |  2764 |   215K|     1 (100)| 00:00:01 |
    |*  4 |    FIXED TABLE FULL     | X$KSUSE    |   248 |  2976 |     0   (0)| 00:00:01 |
    |   5 |    VIEW                 | GV$_LOCK   |  2764 |   183K|     1 (100)| 00:00:01 |
    |   6 |     UNION-ALL           |            |       |       |            |          |
    |*  7 |      FILTER             |            |       |       |            |          |
    |   8 |       VIEW              | GV$_LOCK1  |  2748 |   182K|     0   (0)| 00:00:01 |
    |   9 |        UNION-ALL        |            |       |       |            |          |
    |* 10 |         FIXED TABLE FULL| X$KDNSSF   |     2 |    72 |     0   (0)| 00:00:01 |
    |* 11 |         FIXED TABLE FULL| X$KSQEQ    |  2746 |    99K|     0   (0)| 00:00:01 |
    |* 12 |      FIXED TABLE FULL   | X$KTADM    |     2 |    66 |     0   (0)| 00:00:01 |
    |* 13 |      FIXED TABLE FULL   | X$KTATRFIL |     2 |    66 |     0   (0)| 00:00:01 |
    |* 14 |      FIXED TABLE FULL   | X$KTATRFSL |     2 |    66 |     0   (0)| 00:00:01 |
    |* 15 |      FIXED TABLE FULL   | X$KTATL    |     2 |    72 |     0   (0)| 00:00:01 |
    |* 16 |      FIXED TABLE FULL   | X$KTSTUSC  |     2 |    66 |     0   (0)| 00:00:01 |
    |* 17 |      FIXED TABLE FULL   | X$KTSTUSS  |     2 |    72 |     0   (0)| 00:00:01 |
    |* 18 |      FIXED TABLE FULL   | X$KTSTUSG  |     2 |    66 |     0   (0)| 00:00:01 |
    |* 19 |      FIXED TABLE FULL   | X$KTCXB    |     2 |    60 |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INS
                  T_ID"
    )||RAWTOHEX("R"."ADDR"))
       3 - access("SADDR"="S"."ADDR")
       4 - FILTER("S"."INST_ID"=USERENV('INSTANCE'))
       7 - FILTER(USERENV('INSTANCE') IS NOT NULL)
      10 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      11 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      12 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      13 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      14 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      15 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      16 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      17 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      18 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))
      19 - FILTER(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0
                  AND "INST_ID"=USERENV('INSTANCE'))

    Statistics
    ----------------------------------------------------------
            230  recursive calls
              0  db block gets
            411  consistent gets
              8  physical reads
              0  redo SIZE
           1922  bytes sent via SQL*Net TO client
            430  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
             28  ROWS processed

    WITH fixed objects stats:
    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 3524752130
    --------------------------------------------------------------------------------------
    | Id  | Operation               | Name       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |            |   394 | 38612 |     2 (100)| 00:00:01 |
    |   1 |  HASH JOIN              |            |   394 | 38612 |     2 (100)| 00:00:01 |
    |   2 |   HASH JOIN             |            |    30 |  2400 |     1 (100)| 00:00:01 |
    |   3 |    VIEW                 | GV$_LOCK   |    30 |  2040 |     1 (100)| 00:00:01 |
    |   4 |     UNION-ALL           |            |       |       |            |          |
    |   5 |      FILTER             |            |       |       |            |          |
    |   6 |       VIEW              | GV$_LOCK1  |    22 |  1496 |     0   (0)| 00:00:01 |
    |   7 |        UNION-ALL        |            |       |       |            |          |
    |   8 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    36 |     0   (0)| 00:00:01 |
    |   9 |         FIXED TABLE FULL| X$KSQEQ    |    21 |   777 |     0   (0)| 00:00:01 |
    |  10 |      FIXED TABLE FULL   | X$KTADM    |     1 |    31 |     0   (0)| 00:00:01 |
    |  11 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    33 |     0   (0)| 00:00:01 |
    |  12 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    33 |     0   (0)| 00:00:01 |
    |  13 |      FIXED TABLE FULL   | X$KTATL    |     1 |    33 |     0   (0)| 00:00:01 |
    |  14 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    31 |     0   (0)| 00:00:01 |
    |  15 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    34 |     0   (0)| 00:00:01 |
    |  16 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    31 |     0   (0)| 00:00:01 |
    |  17 |      FIXED TABLE FULL   | X$KTCXB    |     1 |    30 |     0   (0)| 00:00:01 |
    |  18 |    FIXED TABLE FULL     | X$KSUSE    |   248 |  2976 |     0   (0)| 00:00:01 |
    |  19 |   FIXED TABLE FULL      | X$KSQRS    |  1312 | 23616 |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
              0  recursive calls
              1  db block gets
              0  consistent gets
              0  physical reads
              0  redo SIZE
           1741  bytes sent via SQL*Net TO client
            430  bytes received via SQL*Net FROM client
              3  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
             23  ROWS processed
     
    Such advice cannot be given without qualification and apparently you received no such information.