访问V$LOCK视图Oracle 11g出现性能问题-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:13 PM

最近发现Oracle 11g有个问题，拿出来和大家讨论。是在Oracle 11.2.0.3 For Linux X64环境中。检查数据库是否存在锁信息，在查询V

最近发现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

　　-------------------------------------------------------------------------------------

　　-------------------------------------------------------------------------------------

　　| 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