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

原创 2012年03月30日 09:44:40

  最近发现oracle11g有个问题,拿出来和大家讨论。是在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


  如果您还有其他的解决办法,欢迎留言讨论。

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Oracle11g安装过程出现问题

1.未找到文件...WFMLRSVCApp.ear文件 解决方法如下: oracle 11.2.4有两个压缩包,将压缩包解压至同一目录再安装即可解决此类问题。 2.Win7_...
  • U___U
  • U___U
  • 2017-03-08 23:20
  • 636

1、Oracle11g中浏览器访问不了http://localhost:1158/em的问题

以前接触过oracle,但是没有真正的去用过,这段时间不是很忙,就开始从零开始学习oracle,这篇文章是我第一次写。    网上可以找到很多资料都是解决浏览器访问不了http://localhos...
  • wei02
  • wei02
  • 2012-08-01 17:46
  • 6632

oracle11g可能出现的问题

  • 2013-10-23 14:36
  • 34KB
  • 下载

c#向Oracle数据库中插入数据, 出现乱码问题, oracle11g(版本11.2)

数据库语言是:SIMPLIFIED CHINESE_CHINA.ZHS16GBK, 注册表machine->software->Oracle下的NLS_LANG键; 环境变量: nls_lang=S...

Oracle11G-SQL开发指南-10-创建表、序列、索引和视图

1. 表table    a> 创建表 CREATE TABLE    b> 获取表信息 user_tables    c> 获取表中列信息 user_tab_columns\ all_tab_...
  • lzhat
  • lzhat
  • 2017-02-28 17:47
  • 329

Oracle11g性能调优--使用SQL Plan Management(2)

本文描述如何使用SQL Plan Management管理SQL执行计划。SQL Plan management通过提供capturing,selecting和evolving SQL Plan信息的...

Oracle11g count使用函数时在性能与返回结果的差别

实验对象: count(0) count(*) count(字段名) 实验结果: 1、无主键的情况下,实验结果:三种count消耗的性能相同: 2、有主键的情况下,实验结果:三种count消耗的...

Oracle11g性能优化

  • 2012-03-31 18:37
  • 3.54MB
  • 下载

Oracle11g性能优化

  • 2015-09-07 11:32
  • 3.54MB
  • 下载

oracle11g无法连接到数据库故障排除(win7)用户名口令问题

遇到的情况: 检查: 一、用户名是否正确             二、用户名是系统规定的三个sys/system/dbsnmp/sysman             ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)