关闭

查询v$lock视图慢到Hang住

标签: filtertablesqljoinmergeaccess
2942人阅读 评论(0) 收藏 举报
分类:
查询v$lock视图,仅仅53行数据hang住了40s

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Elapsed: 00:00:00.00


SQL> select sid,addr from v$lock;

       SID ADDR
---------- ----------------
       767 00000001DA8FD150
       393 00000001DA8FD5B0
       958 00000001DA8FDAF0
       385 00000001DA8FDDA8
              ....
              ....
      1345 00000001DA8FECA0
       385 00000001DA8FF3A0


53 rows selected.

Elapsed: 00:00:40.20

造成此种原因是sys的统计信息不准,导致走了错误的执行计划

SQL>  select sid,addr from v$lock;

       SID ADDR
---------- ----------------
       194 00000001DA8FD850
         5 00000001DA8FDA10
       385 00000001DA8FE4A8
       767 00000001DA8FD230
        10 00000001DA8FF9D8
       767 00000001DA8FD070

59 rows selected.

Elapsed: 00:00:26.42

Execution Plan
----------------------------------------------------------
Plan hash value: 1899724433

-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |    69 |     1 (100)| 00:00:01 |
|*  1 |  HASH JOIN             |            |     1 |    69 |     1 (100)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN |            |   100 |  5100 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL    | X$KSUSE    |     1 |    32 |     0   (0)| 00:00:01 |
|   4 |    BUFFER SORT         |            |   100 |  1900 |     0   (0)| 00:00:01 |
|   5 |     FIXED TABLE FULL   | X$KSQRS    |   100 |  1900 |     0   (0)| 00:00:01 |
|   6 |   VIEW                 | GV$_LOCK   |    10 |   180 |     0   (0)| 00:00:01 |
|   7 |    UNION-ALL           |            |       |       |            |          |
|*  8 |     FILTER             |            |       |       |            |          |
|   9 |      VIEW              | GV$_LOCK1  |     2 |    36 |     0   (0)| 00:00:01 |
|  10 |       UNION-ALL        |            |       |       |            |          |
|* 11 |        FIXED TABLE FULL| X$KDNSSF   |     1 |    70 |     0   (0)| 00:00:01 |
|* 12 |        FIXED TABLE FULL| X$KSQEQ    |     1 |    70 |     0   (0)| 00:00:01 |
|* 13 |     FIXED TABLE FULL   | X$KTADM    |     1 |    70 |     0   (0)| 00:00:01 |
|* 14 |     FIXED TABLE FULL   | X$KTATRFIL |     1 |    70 |     0   (0)| 00:00:01 |
|* 15 |     FIXED TABLE FULL   | X$KTATRFSL |     1 |    70 |     0   (0)| 00:00:01 |
|* 16 |     FIXED TABLE FULL   | X$KTATL    |     1 |    70 |     0   (0)| 00:00:01 |
|* 17 |     FIXED TABLE FULL   | X$KTSTUSC  |     1 |    70 |     0   (0)| 00:00:01 |
|* 18 |     FIXED TABLE FULL   | X$KTSTUSS  |     1 |    70 |     0   (0)| 00:00:01 |
|* 19 |     FIXED TABLE FULL   | X$KTSTUSG  |     1 |    70 |     0   (0)| 00:00:01 |
|* 20 |     FIXED TABLE FULL   | X$KTCXB    |     1 |    70 |     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"))
   3 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(USERENV('INSTANCE') IS NOT NULL)
  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("KSSOBFLG",1)<>0)
  19 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
              "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  20 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
              "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)


Statistics
----------------------------------------------------------
        577  recursive calls
        681  db block gets
          0  consistent gets
      49879  physical reads
          0  redo size
       2067  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         59  rows processed


解决方案 1: 可以根据使用RBO进行查询

当给予规则的hint在查询中使用时,优化器将不考虑统计信息,并按照基于规则的优化器,更快地执行查询


SQL> select /*+rule+*/ sid,addr  from v$lock;

       SID ADDR
---------- ----------------
       393 00000001DA8FD5B0
       391 00000001DA8FF560
         5 00000001DA8FDA10
       203 00000001DA900D30
       197 00000001DA8FE588

59 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2783647107

------------------------------------------------
| Id  | Operation                 | Name       |
------------------------------------------------
|   0 | SELECT STATEMENT          |            |
|   1 |  MERGE JOIN               |            |
|   2 |   SORT JOIN               |            |
|   3 |    MERGE JOIN             |            |
|   4 |     SORT JOIN             |            |
|   5 |      FIXED TABLE FULL     | X$KSQRS    |
|*  6 |     SORT JOIN             |            |
|   7 |      VIEW                 | GV$_LOCK   |
|   8 |       UNION-ALL           |            |
|*  9 |        FILTER             |            |
|  10 |         VIEW              | GV$_LOCK1  |
|  11 |          UNION-ALL        |            |
|* 12 |           FIXED TABLE FULL| X$KDNSSF   |
|* 13 |           FIXED TABLE FULL| X$KSQEQ    |
|* 14 |        FIXED TABLE FULL   | X$KTADM    |
|* 15 |        FIXED TABLE FULL   | X$KTATRFIL |
|* 16 |        FIXED TABLE FULL   | X$KTATRFSL |
|* 17 |        FIXED TABLE FULL   | X$KTATL    |
|* 18 |        FIXED TABLE FULL   | X$KTSTUSC  |
|* 19 |        FIXED TABLE FULL   | X$KTSTUSS  |
|* 20 |        FIXED TABLE FULL   | X$KTSTUSG  |
|* 21 |        FIXED TABLE FULL   | X$KTCXB    |
|* 22 |   SORT JOIN               |            |
|* 23 |    FIXED TABLE FULL       | X$KSUSE    |
------------------------------------------------

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

   6 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R
              "."INST_ID")||RAWTOHEX("R"."ADDR"))
       filter(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R
              "."INST_ID")||RAWTOHEX("R"."ADDR"))
   9 - filter(USERENV('INSTANCE')=USERENV('INSTANCE'))
  12 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  13 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  14 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  15 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  16 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  17 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  18 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  19 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  20 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
  21 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0)
  22 - access("SADDR"="S"."ADDR")
       filter("SADDR"="S"."ADDR")
  23 - filter("S"."INST_ID"=USERENV('INSTANCE'))

Note
-----
   - rule based optimizer used (consider using cbo)


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



解决方案2:

要删除涉及本次查询数据字典的统计信息,结果还是依旧慢

SQL> exec dbms_stats.delete_schema_stats('sys');

PL/SQL procedure successfully completed.

Elapsed: 00:01:47.75

SQL> select sid,addr from v$lock

       SID ADDR
---------- ----------------
       194 00000001DA8FD850
         5 00000001DA8FDA10
       385 00000001DA8FE4A8
       194 00000001DA8FE9E8
         ...............
        10 00000001DA900378
       767 00000001DA8FD070

60 rows selected.

Elapsed: 00:00:18.66

Execution Plan
----------------------------------------------------------
Plan hash value: 1899724433

-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |    69 |     1 (100)| 00:00:01 |
|*  1 |  HASH JOIN             |            |     1 |    69 |     1 (100)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN |            |   100 |  5100 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL    | X$KSUSE    |     1 |    32 |     0   (0)| 00:00:01 |
|   4 |    BUFFER SORT         |            |   100 |  1900 |     0   (0)| 00:00:01 |
|   5 |     FIXED TABLE FULL   | X$KSQRS    |   100 |  1900 |     0   (0)| 00:00:01 |
|   6 |   VIEW                 | GV$_LOCK   |    10 |   180 |     0   (0)| 00:00:01 |
|   7 |    UNION-ALL           |            |       |       |            |          |
|*  8 |     FILTER             |            |       |       |            |          |
|   9 |      VIEW              | GV$_LOCK1  |     2 |    36 |     0   (0)| 00:00:01 |
|  10 |       UNION-ALL        |            |       |       |            |          |
|* 11 |        FIXED TABLE FULL| X$KDNSSF   |     1 |    70 |     0   (0)| 00:00:01 |
|* 12 |        FIXED TABLE FULL| X$KSQEQ    |     1 |    70 |     0   (0)| 00:00:01 |
|* 13 |     FIXED TABLE FULL   | X$KTADM    |     1 |    70 |     0   (0)| 00:00:01 |
|* 14 |     FIXED TABLE FULL   | X$KTATRFIL |     1 |    70 |     0   (0)| 00:00:01 |
|* 15 |     FIXED TABLE FULL   | X$KTATRFSL |     1 |    70 |     0   (0)| 00:00:01 |
|* 16 |     FIXED TABLE FULL   | X$KTATL    |     1 |    70 |     0   (0)| 00:00:01 |
|* 17 |     FIXED TABLE FULL   | X$KTSTUSC  |     1 |    70 |     0   (0)| 00:00:01 |
|* 18 |     FIXED TABLE FULL   | X$KTSTUSS  |     1 |    70 |     0   (0)| 00:00:01 |
|* 19 |     FIXED TABLE FULL   | X$KTSTUSG  |     1 |    70 |     0   (0)| 00:00:01 |
|* 20 |     FIXED TABLE FULL   | X$KTCXB    |     1 |    70 |     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"))
   3 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   8 - filter(USERENV('INSTANCE') IS NOT NULL)
  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("KSSOBFLG",1)<>0)
  19 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
              "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  20 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
              "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)


Statistics
----------------------------------------------------------
        577  recursive calls
          1  db block gets
          0  consistent gets
      49879  physical reads
          0  redo size
       2081  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         60  rows processed


 只有重新收集统计信息

SQL> exec dbms_stats.gather_fixed_objects_stats

SQL> select sid,addr from v$lock;

       SID ADDR
---------- ----------------
       767 00000001DA8FD070
       956 00000001DA8FCF90
       767 00000001DA8FD150
       385 00000001DA8FDCB0
       767 00000001DA8FD690
        ...............
       385 00000001DA8FDBD0
      1339 00000001DA8FFAB8
       971 00000001DA900E10

       SID ADDR
---------- ----------------
       971 00000001DA9001B8

56 rows selected.

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 3524752130

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |  4583 |   205K|     7 (100)| 00:00:01 |
|   1 |  HASH JOIN              |            |  4583 |   205K|     7 (100)| 00:00:01 |
|   2 |   HASH JOIN             |            |    66 |  2244 |     6 (100)| 00:00:01 |
|   3 |    VIEW                 | GV$_LOCK   |    66 |  1188 |     5 (100)| 00:00:01 |
|   4 |     UNION-ALL           |            |       |       |            |          |
|   5 |      FILTER             |            |       |       |            |          |
|   6 |       VIEW              | GV$_LOCK1  |    58 |  1044 |     3 (100)| 00:00:01 |
|   7 |        UNION-ALL        |            |       |       |            |          |
|   8 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    25 |     0   (0)| 00:00:01 |
|   9 |         FIXED TABLE FULL| X$KSQEQ    |    57 |  1482 |     3 (100)| 00:00:01 |
|  10 |      FIXED TABLE FULL   | X$KTADM    |     1 |    25 |     1 (100)| 00:00:01 |
|  11 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    23 |     0   (0)| 00:00:01 |
|  12 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    23 |     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 |    23 |     0   (0)| 00:00:01 |
|  15 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    25 |     0   (0)| 00:00:01 |
|  16 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    23 |     0   (0)| 00:00:01 |
|  17 |      FIXED TABLE FULL   | X$KTCXB    |     1 |    25 |     0   (0)| 00:00:01 |
|  18 |    FIXED TABLE FULL     | X$KSUSE    |  1528 | 24448 |     0   (0)| 00:00:01 |
|  19 |   FIXED TABLE FULL      | X$KSQRS    |  6944 | 83328 |     1 (100)| 00:00:01 |
--------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        183  recursive calls
          0  db block gets
        405  consistent gets
          0  physical reads
          0  redo size
       2125  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         40  sorts (memory)
          0  sorts (disk)
         56  rows processed

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:792137次
    • 积分:8857
    • 等级:
    • 排名:第2429名
    • 原创:137篇
    • 转载:45篇
    • 译文:9篇
    • 评论:43条
    简介
    此博客为笔者的DT工作记录,欢迎一起交流学习工作中的点滴,若博客文章观点存在原理的错误、纰漏,笔者非常欢迎各位不保留地指出
    最新评论