访问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
如果您还有其他的解决办法，欢迎留言讨论。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1421
52
1315
25
1266
29
1239
24
When might a full table scan be faster than using an index in MySQL?
Apr 09, 2025 am 12:05 AM
Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
MySQL: An Introduction to the World's Most Popular Database
Apr 12, 2025 am 12:18 AM
MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.
Explain the role of InnoDB redo logs and undo logs.
Apr 15, 2025 am 12:16 AM
InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.
MySQL's Place: Databases and Programming
Apr 13, 2025 am 12:18 AM
MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
Why Use MySQL? Benefits and Advantages
Apr 12, 2025 am 12:17 AM
MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.
