查询v$lock视图慢到Hang住
来源：互联网 发布：电脑自动朗读软件 编辑：程序博客网 时间：2024/06/09 04:45
查询v$lock视图，仅仅53行数据hang住了40sSQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionElapsed: 00:00:00.00SQL> select sid,addr from v$lock; SID ADDR---------- ---------------- 767 00000001DA8FD150 393 00000001DA8FD5B0 958 00000001DA8FDAF0 385 00000001DA8FDDA8 .... .... 1345 00000001DA8FECA0 385 00000001DA8FF3A053 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 00000001DA8FD07059 rows selected.Elapsed: 00:00:26.42Execution 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 00000001DA8FE58859 rows selected.Elapsed: 00:00:00.04Execution 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.75SQL> select sid,addr from v$lock SID ADDR---------- ---------------- 194 00000001DA8FD850 5 00000001DA8FDA10 385 00000001DA8FE4A8 194 00000001DA8FE9E8 ............... 10 00000001DA900378 767 00000001DA8FD07060 rows selected.Elapsed: 00:00:18.66Execution 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_statsSQL> 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 00000001DA9001B856 rows selected.Elapsed: 00:00:00.10Execution 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
