访问V$LOCK视图Oracle 11g出现性能问题
最近发现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
-------------------------------------------------------------------------------------
| 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
如果您还有其他的解决办法，欢迎留言讨论。
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
뜨거운 도구
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
드림위버 CS6
시각적 웹 개발 도구
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
뜨거운 주제
7403
15
1630
14
1358
52
1268
25
1218
29
Alter Table 문을 사용하여 MySQL에서 테이블을 어떻게 변경합니까?
Mar 19, 2025 pm 03:51 PM
이 기사는 MySQL의 Alter Table 문을 사용하여 열 추가/드롭 테이블/열 변경 및 열 데이터 유형 변경을 포함하여 테이블을 수정하는 것에 대해 설명합니다.
MySQL 연결에 대한 SSL/TLS 암호화를 어떻게 구성합니까?
Mar 18, 2025 pm 12:01 PM
기사는 인증서 생성 및 확인을 포함하여 MySQL에 대한 SSL/TLS 암호화 구성에 대해 설명합니다. 주요 문제는 자체 서명 인증서의 보안 영향을 사용하는 것입니다. [문자 수 : 159]
MySQL에서 큰 데이터 세트를 어떻게 처리합니까?
Mar 21, 2025 pm 12:15 PM
기사는 MySQL에서 파티셔닝, 샤딩, 인덱싱 및 쿼리 최적화를 포함하여 대규모 데이터 세트를 처리하기위한 전략에 대해 설명합니다.
인기있는 MySQL GUI 도구는 무엇입니까 (예 : MySQL Workbench, Phpmyadmin)?
Mar 21, 2025 pm 06:28 PM
기사는 MySQL Workbench 및 Phpmyadmin과 같은 인기있는 MySQL GUI 도구에 대해 논의하여 초보자 및 고급 사용자를위한 기능과 적합성을 비교합니다. [159 자].
드롭 테이블 문을 사용하여 MySQL에서 테이블을 어떻게 드롭합니까?
Mar 19, 2025 pm 03:52 PM
이 기사에서는 Drop Table 문을 사용하여 MySQL에서 테이블을 떨어 뜨리는 것에 대해 설명하여 예방 조치와 위험을 강조합니다. 백업 없이는 행동이 돌이킬 수 없으며 복구 방법 및 잠재적 생산 환경 위험을 상세하게합니다.
외국 키를 사용하여 관계를 어떻게 표현합니까?
Mar 19, 2025 pm 03:48 PM
기사는 외국 열쇠를 사용하여 데이터베이스의 관계를 나타내고 모범 사례, 데이터 무결성 및 피할 수있는 일반적인 함정에 중점을 둡니다.
일반적인 취약점 (SQL 주입, 무차별 적 공격)에 대해 MySQL을 어떻게 보호합니까?
Mar 18, 2025 pm 12:00 PM
기사는 준비된 명령문, 입력 검증 및 강력한 암호 정책을 사용하여 SQL 주입 및 무차별 적 공격에 대한 MySQL 보안에 대해 논의합니다 (159 자)
JSON 열에서 인덱스를 어떻게 생성합니까?
Mar 21, 2025 pm 12:13 PM
이 기사에서는 PostgreSQL, MySQL 및 MongoDB와 같은 다양한 데이터베이스에서 JSON 열에서 인덱스를 작성하여 쿼리 성능을 향상시킵니다. 특정 JSON 경로를 인덱싱하는 구문 및 이점을 설명하고 지원되는 데이터베이스 시스템을 나열합니다.
