查询v$lock慢

最近分析锁问题的时候发现查询v$lock视图很慢，查询v$lock主要就是查询下面这些内存结构表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Fixed tables :-
-----------------
X$KSUSE
X$KDNSSF
X$KSQEQ
X$KTADM
X$KTATRFIL
X$KTATRFSL
X$KTATL
X$KTSTUSC
X$KTSTUSS
X$KTSTUSG
X$KTCXB
X$KSQRS
X$KSLWT
X$KSLED

通常对于我们一般的表来说，如果表上没有相关的统计信息，那么CBO优化器会自动进行动态采样，而对于fixed tables却不会做这些操作，所以必须要收集它们的统计信息

MOS上这篇文章Query Against v$lock Run from OEM Performs Slowly (Doc ID 1328789.1)比较相似，但是其给出的解决方案是执行exec dbms_stats.GATHER_FIXED_OBJECTS_STATS,这个操作如果是在一个比较繁忙的数据库上会比较危险

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
SYS@xb> set autotrace traceonly

SYS@xb> select * from v$lock;

SYS@xb> set timing on

SYS@xb> select * from v$lock;

Elapsed: 00:00:04.14

Execution Plan
----------------------------------------------------------
Plan hash value: 554400005

-------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |   156 |     0   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |        |     1 |   156 |     0   (0)| 00:00:01 |
|   2 |   VIEW             | GV$_LOCK   |    10 |   760 |     0   (0)| 00:00:01 |
|   3 |    UNION-ALL           |        |       |       |        |      |
|*  4 |     FILTER         |        |       |       |        |      |
|   5 |      VIEW          | GV$_LOCK1  |     2 |   152 |     0   (0)| 00:00:01 |
|   6 |       UNION-ALL        |        |       |       |        |      |
|*  7 |        FIXED TABLE FULL| X$KDNSSF   |     1 |   102 |     0   (0)| 00:00:01 |
|*  8 |        FIXED TABLE FULL| X$KSQEQ    |     1 |   102 |     0   (0)| 00:00:01 |
|*  9 |     FIXED TABLE FULL   | X$KTADM    |     1 |   102 |     0   (0)| 00:00:01 |
|* 10 |     FIXED TABLE FULL   | X$KTATRFIL |     1 |   102 |     0   (0)| 00:00:01 |
|* 11 |     FIXED TABLE FULL   | X$KTATRFSL |     1 |   102 |     0   (0)| 00:00:01 |
|* 12 |     FIXED TABLE FULL   | X$KTATL    |     1 |   102 |     0   (0)| 00:00:01 |
|* 13 |     FIXED TABLE FULL   | X$KTSTUSC  |     1 |   102 |     0   (0)| 00:00:01 |
|* 14 |     FIXED TABLE FULL   | X$KTSTUSS  |     1 |   102 |     0   (0)| 00:00:01 |
|* 15 |     FIXED TABLE FULL   | X$KTSTUSG  |     1 |   102 |     0   (0)| 00:00:01 |
|* 16 |     FIXED TABLE FULL   | X$KTCXB    |     1 |   102 |     0   (0)| 00:00:01 |
|  17 |   MERGE JOIN CARTESIAN |        |   100 |  8000 |     0   (0)| 00:00:01 |
|* 18 |    FIXED TABLE FULL    | X$KSUSE    |     1 |    32 |     0   (0)| 00:00:01 |
|  19 |    BUFFER SORT         |        |   100 |  4800 |     0   (0)| 00:00:01 |
|  20 |     FIXED TABLE FULL   | X$KSQRS    |   100 |  4800 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("
          RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR"))
   4 - filter(USERENV('INSTANCE') IS NOT NULL)
   7 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
   8 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
   9 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  10 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  11 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  12 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  13 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  14 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  15 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  16 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)>>0)
  18 - filter("S"."INST_ID"=USERENV('INSTANCE'))


Statistics
----------------------------------------------------------
      0  recursive calls
      2  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
       2780  bytes sent via SQL*Net to client
    545  bytes received via SQL*Net from client
      4  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     36  rows processed

这里可以看到第17步存在笛卡尔积，优化器估算的X$KSUSE(v$session)表只有1行，X$KSQRS(V$RESOURCE)为100行，按照这个统计值，笛卡尔积并无问题。第3步的UNION ALL考虑了各种锁的情况，最后将所有的结果汇总。一般来说v$session查出来的数据都远不止一行,所以对这两个内存结构表进行表分析

1
2
3
4
5
6
7
8
SYS@xb> 
begin
  dbms_stats.gather_table_stats('SYS','x$ksuse',method_opt=>'for all columns size 1');
  dbms_stats.gather_table_stats('SYS','x$ksqrs',method_opt=>'for all columns size 1');
end;
  5  /

PL/SQL procedure successfully completed.

查看x$表的统计信息情况

1
2
3
4
5
SYS@xb> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KSUSE';

OWNER                                              TABLE_NAME                                             LAST_ANALYZED
------------------------------------------------------------------------------------------ --------------------------------------------------------------- ---------------
SYS                                            X$KSUSE                                            02-NOV-18

重新查看执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
SYS@xb> select * from v$lock;

38 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1453144240

--------------------------------------------------------------------------------------
| Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        | 51198 |  5699K|     3 (100)| 00:00:01 |
|*  1 |  HASH JOIN      |        | 51198 |  5699K|     3 (100)| 00:00:01 |
|*  2 |   HASH JOIN     |        |   739 | 67988 |     2 (100)| 00:00:01 |
|*  3 |    FIXED TABLE FULL | X$KSUSE    |  1522 | 24352 |     0   (0)| 00:00:01 |
|   4 |    VIEW         | GV$_LOCK   |   739 | 56164 |     2 (100)| 00:00:01 |
|   5 |     UNION-ALL       |        |       |       |        |      |
|*  6 |      FILTER     |        |       |       |        |      |
|   7 |       VIEW      | GV$_LOCK1  |   731 | 55556 |     2 (100)| 00:00:01 |
|   8 |        UNION-ALL    |        |       |       |        |      |
|*  9 |     FIXED TABLE FULL| X$KDNSSF   |     1 |    40 |     0   (0)| 00:00:01 |
|* 10 |     FIXED TABLE FULL| X$KSQEQ    |   730 | 29930 |     2 (100)| 00:00:01 |
|* 11 |      FIXED TABLE FULL   | X$KTADM    |     1 |   102 |     0   (0)| 00:00:01 |
|* 12 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |   102 |     0   (0)| 00:00:01 |
|* 13 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |   102 |     0   (0)| 00:00:01 |
|* 14 |      FIXED TABLE FULL   | X$KTATL    |     1 |   102 |     0   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |   102 |     0   (0)| 00:00:01 |
|* 16 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |   102 |     0   (0)| 00:00:01 |
|* 17 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |   102 |     0   (0)| 00:00:01 |
|* 18 |      FIXED TABLE FULL   | X$KTCXB    |     1 |   102 |     0   (0)| 00:00:01 |
|  19 |   FIXED TABLE FULL  | X$KSQRS    |  6928 |   148K|     1 (100)| 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"))
   2 - access("SADDR"="S"."ADDR")
   3 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   6 - filter(USERENV('INSTANCE') IS NOT NULL)
   9 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND BITAND("KSSOBFLG",1)>>0
          AND "INST_ID"=USERENV('INSTANCE'))
  10 - filter(BITAND("KSSOBFLG",1)>>0 AND ("KSQLKMOD">>0 OR "KSQLKREQ">>0)
          AND "INST_ID"=USERENV('INSTANCE'))
  11 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  12 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  13 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  14 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  15 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  16 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  17 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0)
  18 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)>>0)


Statistics
----------------------------------------------------------
      1  recursive calls
      2  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
       2876  bytes sent via SQL*Net to client
    545  bytes received via SQL*Net from client
      4  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     38  rows processed
Oracle
#statistics
查询v$lock慢
https://www.xbdba.com/2018/11/12/query-vlock-slowly/
作者
xbdba
发布于
2018年11月12日
许可协议