ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 11gr2访问V$LOCK视图出现性能问题

11gr2访问V$LOCK视图出现性能问题

原创 Linux操作系统 作者：yangtingkun 时间：2012-03-26 23:45:00 0 删除 编辑

在Oracle 11.2.0.3 For Linux X64环境中，碰到这个问题。

 

 

检查数据库是否存在锁信息，在查询V$LOCK视图时发现很长时间没有响应，甚至一度认为数据库HANG住：

SQL> select * from v$lock where type != 'MR';

ADDR             KADDR             SID TY      ID1  ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- -------- ---- ----- ------- ----- -----
0000000C5A6AA770 0000000C5A6AA7C8 3405 TO     5124    2     3       0  8543     0
0000000C5A6AAA10 0000000C5A6AAA68 3405 TO     5124    1     3       0  8543     0
00007FEEB24CBDE8 00007FEEB24CBE48 4255 TM     5124    0     3       0  5455     0
0000000C5A6A4860 0000000C5A6A48B8    1 AE      100    0     4       0 10323     0
00007FEEB24CBDE8 00007FEEB24CBE48 1144 TM     5124    0     3       0   107     0
0000000C5A6A9DD0 0000000C5A6A9E28 1144 TO     5124    2     3       0   107     0
0000000C44D8FF08 0000000C44D8FF80 1144 TX 12845057 2063     6       0   107     0
0000000C5A6AA4D0 0000000C5A6AA528 3405 AE      100    0     4       0  9547     0
0000000C4F97CC28 0000000C4F97CCA0 3405 TX 12517398 2118     6       0  8543     0
0000000C5A6A2278 0000000C5A6A22D0 3680 RS       25    1     2       0 10346     0
0000000C5A6A3790 0000000C5A6A37E8 3963 TS       16    1     3       0  8906     0
0000000C5A6AACC8 0000000C5A6AAD20 4255 AE      100    0     4       0  5484     0
0000000C4FDC1288 0000000C4FDC1300 4255 TX 15073308 1757     6       0  5455     0
0000000C5A6AA850 0000000C5A6AA8A8 1144 TO     5124    1     3       0  7441     0
0000000C5A6A9338 0000000C5A6A9390 1420 TO     5124    2     3       0   840     0
0000000C5A6A4940 0000000C5A6A4998 1983 KD        0    0     6       0 10324     0
0000000C5A6A4B00 0000000C5A6A4B58 1983 KT    12540    0     4       0 10324     0
0000000C5A6A2198 0000000C5A6A21F0 3680 CF        0    0     2       0 10350     0
0000000C5A6A2438 0000000C5A6A2490 3397 RT        1    0     6       0 10346     0
0000000C5A6A1E18 0000000C5A6A1E70 3680 XR        4    0     1       0 10350     0
0000000C5A6A5F38 0000000C5A6A5F90 1134 AE      100    0     4       0   109     0
0000000C5A6A4E80 0000000C5A6A4ED8 1420 AE      100    0     4       0  2824     0
0000000C4FCAB578 0000000C4FCAB5F0 1420 TX 10223617 2128     6       0   840     0
0000000C5A6A9EB0 0000000C5A6A9F08 1420 TO     5124    1     3       0   840     0
0000000C5A6AA690 0000000C5A6AA6E8 4255 TO     5124    2     3       0  5455     0
00007FEEB24CBDE8 00007FEEB24CBE48 1420 TM     5124    0     3       0   840     0
0000000C5A6A6018 0000000C5A6A6070 1990 AE      100    0     4       0  1489     0
0000000C5A6A4A20 0000000C5A6A4A78 2831 PW        1    0     3       0 10323     0
0000000C5A6A1EF8 0000000C5A6A1F50 3680 RD        1    0     1       0 10350     0
0000000C5A6AA5B0 0000000C5A6AA608 1144 AE      100    0     4       0  7459     0
00007FEEB24CBDE8 00007FEEB24CBE48 3405 TM     5124    0     3       0  8543     0
0000000C5A6A2518 0000000C5A6A2570 3963 TS        3    1     3       0 10325     0
0000000C5A6AB128 0000000C5A6AB180 4255 TO     5124    1     3       0  5455     0

33 rows selected.

Elapsed: 00:13:13.74

一个如此简单的查询执行时间居然超过了13分钟，在这个SQL运行过程中检查发现，这个会话在等待直接路径写：

SQL> select sql_text from v$sql where sql_id in (select sql_id from v$Session where sid = 1420);

SQL_TEXT
--------------------------------------------------------------------------------------
SELECT * FROM V$LOCK WHERE TYPE != :"SYS_B_0"

SQL> select event, p1text, p1 from v$session where sid = 1420;

EVENT                                    P1TEXT                  P1
---------------------------------------- --------------- ----------
direct path write temp                   file number            201

检查了一下执行计划：

SQL> explain plan for
  2  SELECT * FROM V$LOCK WHERE TYPE != 'MR';

Explained.

SQL> select * from table(dbms_XPlan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1899724433

-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |   158 |     1 (100)| 00:00:01 |
|*  1 |  HASH JOIN             |            |     1 |   158 |     1 (100)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN |            |     5 |   400 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL    | X$KSUSE    |     1 |    32 |     0   (0)| 00:00:01 |
|   4 |    BUFFER SORT         |            |     5 |   240 |     0   (0)| 00:00:01 |
|*  5 |     FIXED TABLE FULL   | X$KSQRS    |     5 |   240 |     0   (0)| 00:00:01 |
|   6 |   VIEW                 | GV$_LOCK   |    10 |   780 |     0   (0)| 00:00:01 |
|   7 |    UNION-ALL           |            |       |       |            |          |
|*  8 |     FILTER             |            |       |       |            |          |
|   9 |      VIEW              | GV$_LOCK1  |     2 |   156 |     0   (0)| 00:00:01 |
|  10 |       UNION-ALL        |            |       |       |            |          |
|* 11 |        FIXED TABLE FULL| X$KDNSSF   |     1 |   102 |     0   (0)| 00:00:01 |
|* 12 |        FIXED TABLE FULL| X$KSQEQ    |     1 |   102 |     0   (0)| 00:00:01 |
|* 13 |     FIXED TABLE FULL   | X$KTADM    |     1 |   102 |     0   (0)| 00:00:01 |
|* 14 |     FIXED TABLE FULL   | X$KTATRFIL |     1 |   102 |     0   (0)| 00:00:01 |
|* 15 |     FIXED TABLE FULL   | X$KTATRFSL |     1 |   102 |     0   (0)| 00:00:01 |
|* 16 |     FIXED TABLE FULL   | X$KTATL    |     1 |   102 |     0   (0)| 00:00:01 |
|* 17 |     FIXED TABLE FULL   | X$KTSTUSC  |     1 |   102 |     0   (0)| 00:00:01 |
|* 18 |     FIXED TABLE FULL   | X$KTSTUSS  |     1 |   102 |     0   (0)| 00:00:01 |
|* 19 |     FIXED TABLE FULL   | X$KTSTUSG  |     1 |   102 |     0   (0)| 00:00:01 |
|* 20 |     FIXED TABLE FULL   | X$KTCXB    |     1 |   102 |     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'))
   5 - filter("R"."KSQRSIDT"<>'MR')
   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)

56 rows selected.

SQL> select count(*) from X$KSUSE;

  COUNT(*)
----------
      4528

SQL> select count(*) from X$KSQRS;

  COUNT(*)
----------
     20144

显然导致查询缓慢以及读取临时空间的问题原因在于执行计划的错误，而执行计划的错误在于统计信息不准确。

解决方法有两种，一种是使用RBO读取V$LOCK视图，另一种是收集V$视图的统计信息，是的Oracle获取正确的执行计划：

SQL> select /*+ rule */ * from v$lock where type != 'MR';

ADDR             KADDR             SID TY      ID1  ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- -------- ---- ----- ------- ----- -----
0000000C5A6A4A20 0000000C5A6A4A78 2831 PW        1    0     3       0  9861     0
0000000C5A6A3790 0000000C5A6A37E8 3963 TS       16    1     3       0  8444     0
0000000C5A6A2518 0000000C5A6A2570 3963 TS        3    1     3       0  9863     0
00007FEEB24CC490 00007FEEB24CC4F0 1144 TM    75580    0     4       0  2615     0
0000000C5A6A6E30 0000000C5A6A6E88 1144 AE        0    1     4       0  2615     0
0000000C44D8FF08 0000000C44D8FF80 1144 TX 12124184 2594     6       0  2615     0
00007FEEB24CC490 00007FEEB24CC4F0 1144 TM       28    0     3       0  2615     0
0000000C5A6AA850 0000000C5A6AA8A8 1144 TO     5124    1     3       0  6979     0
00007FEEB24CC490 00007FEEB24CC4F0 1144 TM    75584    0     4       0  2615     0
0000000C5A6AA5B0 0000000C5A6AA608 1144 AE      100    0     4       0  6997     0
0000000C5A6A6018 0000000C5A6A6070 1990 AE      100    0     4       0  1027     0
0000000C5A6A4860 0000000C5A6A48B8    1 AE      100    0     4       0  9861     0
0000000C5A6A4940 0000000C5A6A4998 1983 KD        0    0     6       0  9862     0
0000000C5A6A4B00 0000000C5A6A4B58 1983 KT    12540    0     4       0  9862     0
0000000C5A6AA770 0000000C5A6AA7C8 3405 TO     5124    2     3       0  8081     0
00007FEEB24CC490 00007FEEB24CC4F0 3405 TM     5124    0     3       0  8081     0
0000000C5A6AA4D0 0000000C5A6AA528 3405 AE      100    0     4       0  9085     0
0000000C4F97CC28 0000000C4F97CCA0 3405 TX 12517398 2118     6       0  8081     0
0000000C5A6AAA10 0000000C5A6AAA68 3405 TO     5124    1     3       0  8081     0
0000000C5A6A2438 0000000C5A6A2490 3397 RT        1    0     6       0  9884     0
0000000C4FDC1288 0000000C4FDC1300 4255 TX 15073308 1757     6       0  4993     0
0000000C5A6AACC8 0000000C5A6AAD20 4255 AE      100    0     4       0  5022     0
0000000C5A6AB128 0000000C5A6AB180 4255 TO     5124    1     3       0  4993     0
0000000C5A6AA690 0000000C5A6AA6E8 4255 TO     5124    2     3       0  4993     0
00007FEEB24CC490 00007FEEB24CC4F0 4255 TM     5124    0     3       0  4993     0
0000000C5A6A9EB0 0000000C5A6A9F08 1420 TO     5124    1     3       0   378     0
0000000C5A6A9338 0000000C5A6A9390 1420 TO     5124    2     3       0   378     0
0000000C5A6A4E80 0000000C5A6A4ED8 1420 AE      100    0     4       0  2362     0
00007FEEB24CC490 00007FEEB24CC4F0 1420 TM     5124    0     3       0   378     0
0000000C4FCAB578 0000000C4FCAB5F0 1420 TX 10223617 2128     6       0   378     0
0000000C5A6A1EF8 0000000C5A6A1F50 3680 RD        1    0     1       0  9888     0
0000000C5A6A1E18 0000000C5A6A1E70 3680 XR        4    0     1       0  9888     0
0000000C5A6A2198 0000000C5A6A21F0 3680 CF        0    0     2       0  9888     0
0000000C5A6A2278 0000000C5A6A22D0 3680 RS       25    1     2       0  9884     0

34 rows selected.

Elapsed: 00:00:00.17

SQL> exec dbms_stats.gather_fixed_objects_stats

PL/SQL procedure successfully completed.

Elapsed: 00:04:03.17
SQL> explain plan for
  2  select * from v$lock where type != 'MR';

Explained.

Elapsed: 00:00:00.20
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3524752130

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |   104K|    11M|    55 (100)| 00:00:01 |
|   1 |  HASH JOIN              |            |   104K|    11M|    55 (100)| 00:00:01 |
|   2 |   HASH JOIN             |            |   522 | 49068 |    43 (100)| 00:00:01 |
|   3 |    VIEW                 | GV$_LOCK   |   522 | 40716 |    40 (100)| 00:00:01 |
|   4 |     UNION-ALL           |            |       |       |            |          |
|   5 |      FILTER             |            |       |       |            |          |
|   6 |       VIEW              | GV$_LOCK1  |   207 | 16146 |    28 (100)| 00:00:01 |
|   7 |        UNION-ALL        |            |       |       |            |          |
|   8 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    40 |     2 (100)| 00:00:01 |
|   9 |         FIXED TABLE FULL| X$KSQEQ    |   206 |  8446 |    26 (100)| 00:00:01 |
|  10 |      FIXED TABLE FULL   | X$KTADM    |   254 | 10160 |    10 (100)| 00:00:01 |
|  11 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    36 |     0   (0)| 00:00:01 |
|  12 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    36 |     0   (0)| 00:00:01 |
|  13 |      FIXED TABLE FULL   | X$KTATL    |     1 |    54 |     0   (0)| 00:00:01 |
|  14 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    39 |     0   (0)| 00:00:01 |
|  15 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    39 |     0   (0)| 00:00:01 |
|  16 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    36 |     0   (0)| 00:00:01 |
|  17 |      FIXED TABLE FULL   | X$KTCXB    |    55 |  2200 |     2 (100)| 00:00:01 |
|  18 |    FIXED TABLE FULL     | X$KSUSE    |  4528 | 72448 |     2 (100)| 00:00:01 |
|  19 |   FIXED TABLE FULL      | X$KSQRS    | 20093 |   431K|    10 (100)| 00:00:01 |
--------------------------------------------------------------------------------------

26 rows selected.

Elapsed: 00:00:00.06
SQL> select * from v$lock where type != 'MR';

ADDR             KADDR             SID TY      ID1  ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---- -- -------- ---- ----- ------- ----- -----
0000000C5A6A1E18 0000000C5A6A1E70 3680 XR        4    0     1       0 11480     0
0000000C5A6A1EF8 0000000C5A6A1F50 3680 RD        1    0     1       0 11480     0
0000000C5A6A2198 0000000C5A6A21F0 3680 CF        0    0     2       0 11480     0
0000000C5A6A2438 0000000C5A6A2490 3397 RT        1    0     6       0 11476     0
0000000C5A6A2278 0000000C5A6A22D0 3680 RS       25    1     2       0 11476     0
0000000C4F97A3E8 0000000C4F97A460 1136 TX 15335430  878     6       0     0     0
0000000C5A6A2518 0000000C5A6A2570 3963 TS        3    1     3       0 11455     0
0000000C4FC26180 0000000C4FC261F8  569 TX 15007765 1622     6       0     0     0
0000000C4FDBD628 0000000C4FDBD6A0 3682 TX 13107205 2166     6       0     0     0
0000000C4FA02A30 0000000C4FA02AA8 2272 TX 14090252 2079     6       0     0     0
0000000C5A6A4940 0000000C5A6A4998 1983 KD        0    0     6       0 11454     0
0000000C5A6A4B00 0000000C5A6A4B58 1983 KT    12540    0     4       0 11454     0
0000000C4FC23940 0000000C4FC239B8 1989 TX 10289157 2350     6       0     0     0
0000000C5A6A4A20 0000000C5A6A4A78 2831 PW        1    0     3       0 11453     0
0000000C5A6A9DD0 0000000C5A6A9E28 4258 AE      100    0     4       0   371     0
0000000C5A6AACC8 0000000C5A6AAD20 4255 AE      100    0     4       0  6614     0
0000000C5A6A68F0 0000000C5A6A6948 4251 AE      100    0     4       0   369     0
.
.
.
0000000C4FCAB578 0000000C4FCAB5F0 1421 TX 14417935 2082     6       0     0     0
0000000C4FA8CEA8 0000000C4FA8CF20 3118 TX 13828121 2491     6       0     0     0
0000000C4FA08ED0 0000000C4FA08F48 1420 TX 11730968 2288     6       0    41     0
0000000C4FE47090 0000000C4FE47108 3965 TX 13369364 2318     6       0     0     0
0000000C44E18550 0000000C44E185C8 2835 TX 13238301 2255     6       0     0     0
0000000C4FCAC998 0000000C4FCACA10 1423 TX 10158112 2226     6       0     0     0
0000000C44F291E0 0000000C44F29258  290 TX 12779538 2146     6       0     0     0

519 rows selected.

Elapsed: 00:00:00.19

 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-719876/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： RMAN-600(8201)错误
下一篇： 消除11.2上的db file parallel read
请登录后发表评论 登录
全部评论
yangtingkun
暂无介绍

注册时间：2007-12-29

  • 博文量
    1964
  • 访问量
    11076943

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593