访问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ヘンタイを無料で生成します。
人気の記事
ホットツール
メモ帳++7.3.1
使いやすく無料のコードエディター
SublimeText3 中国語版
中国語版、とても使いやすい
ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境
ドリームウィーバー CS6
ビジュアル Web 開発ツール
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ワークベンチやPHPMyAdminなどの人気のあるMySQL GUIツールについて説明し、初心者と上級ユーザーの機能と適合性を比較します。[159文字]
ドロップテーブルステートメントを使用してMySQLにテーブルをドロップするにはどうすればよいですか？
Mar 19, 2025 pm 03:52 PM
この記事では、ドロップテーブルステートメントを使用して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パスのインデックス作成の構文と利点を説明し、サポートされているデータベースシステムをリストします。
