Performance problems occur when accessing the V $ LOCK view Oracle 11g

I recently found a problem with Oracle11g. I will discuss it with you. Is in Oracle 11.2.0.3 For Linux X64 environment.

Check whether the database has the LOCK information. When querying the V $ LOCK view, we found that there was no response for a long time, and even thought that the database HANG was:

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

%7feeb24cbde8 %7feeb24cbe48 4255 TM 5124 0 3 0 5455 0

2017000c5a6a4860 2017000c5a6a48b8 1 AE 100 0 4 0 10323 0

%7feeb24cbde8 %7feeb24cbe48 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

2017000c5a6aa4d0 2017000c5a6aa528 3405 AE 100 0 4 0 9547 0

2017000c4f97cc28 2017000c4f97cca0 3405 TX 12517398 2118 6 0 8543 0

2017000c5a6a2278 2017000c5a6a22d0 3680 RS 25 1 2 0 10346 0

2017000c5a6a3790 2017000c5a6a37e8 3963 TS 16 1 3 0 8906 0

2017000c5a6aacc8 2017000c5a6aad20 4255 AE 100 0 4 0 5484 0

2017000c4fdc1288 2017000c4fdc1300 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

2017000c5a6a4940 2017000c5a6a4998 1983 KD 0 0 6 0 10324 0

0000000C5A6A4B00 0000000C5A6A4B58 1983 KT 12540 0 4 0 10324 0

2017000c5a6a2198 2017000c5a6a21f0 3680 CF 0 0 2 0 10350 0

2017000c5a6a2438 2017000c5a6a2490 3397 RT 1 0 6 0 10346 0

2017000c5a6a1e18 2017000c5a6a1e70 3680 XR 4 0 1 0 10350 0

2017000c5a6a5f38 2017000c5a6a5f90 1134 AE 100 0 4 0 109 0

2017000c5a6a4e80 2017000c5a6a4ed8 1420 AE 100 0 4 0 2824 0

2017000c4fcab578 2017000c4fcab5f0 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

%7feeb24cbde8 %7feeb24cbe48 1420 TM 5124 0 3 0 840 0

2017000c5a6a6018 2017000c5a6a6070 1990 AE 100 0 4 0 1489 0

2017000c5a6a4a20 2017000c5a6a4a78 2831 PW 1 0 3 0 10323 0

0000000C5A6A1EF8 0000000C5A6A1F50 3680 RD 1 0 10350 0

2017000c5a6aa5b0 2017000c5a6aa608 1144 AE 100 0 4 0 7459 0

%7feeb24cbde8 %7feeb24cbe48 3405 TM 5124 0 3 0 8543 0

2017000c5a6a2518 2017000c5a6a2570 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

The execution time of such a simple query exceeds 13 minutes. During the SQL running process, we found that this session is waiting for direct path writing:

SQL> select SQL _text from v $ SQL where SQL _id in (select SQL _id from v $ Session where sid = 1420 );

SQL _TEXT

Bytes --------------------------------------------------------------------------------------

SELECT * from v $ lock where type! =: "SYS_ B _0"

SQL & gt; select event, p1text, p1 from v $ session where sid = 1420;

EVENT P1TEXT P1

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

Directly path write temp file number 201

Check the execution plan:

SQL> explain plan

2 SELECT * from v $ lock where type! = 'Mr ';

Explained.

SQL> select * from table (dbms_XPlan.display );

PLAN_TABLE_OUTPUT

Bytes ------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1899724433

Bytes -------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes -------------------------------------------------------------------------------------

| 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 |

Bytes -------------------------------------------------------------------------------------

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

Apparently, the cause of slow query and temporary space reading is the execution plan error, and the Execution Plan error is that the statistical information is inaccurate.

There are two solutions: one is to use RBO to read the V $ LOCK view, the other is to collect the statistics of the V $ view, and the Oracle gets the correct execution plan:

SQL> select/* + rule */* from v $ lock where type! = 'Mr ';

Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK

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

2017000c5a6a4a20 2017000c5a6a4a78 2831 PW 1 0 3 0 9861 0

2017000c5a6a3790 2017000c5a6a37e8 3963 TS 16 1 3 0 8444 0

2017000c5a6a2518 2017000c5a6a2570 3963 TS 3 1 3 0 9863 0

%7feeb24cc490 %7feeb24cc4f0 1144 TM 75580 0 4 0 2615 0

2017000c5a6a6e30 2017000c5a6a6e88 1144 AE 0 1 4 0 2615 0

0000000C44D8FF08 0000000C44D8FF80 1144 TX 12124184 2594 6 0 2615 0

%7feeb24cc490 %7feeb24cc4f0 1144 TM 28 0 3 0 2615 0

0000000C5A6AA850 0000000C5A6AA8A8 1144 TO 5124 1 3 0 6979 0

%7feeb24cc490 %7feeb24cc4f0 1144 TM 75584 0 4 0 2615 0

2017000c5a6aa5b0 2017000c5a6aa608 1144 AE 100 0 4 0 6997 0

2017000c5a6a6018 2017000c5a6a6070 1990 AE 100 0 4 0 1027 0

2017000c5a6a4860 2017000c5a6a48b8 1 AE 100 0 4 0 9861 0

2017000c5a6a4940 2017000c5a6a4998 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

%7feeb24cc490 %7feeb24cc4f0 3405 TM 5124 0 3 0 8081 0

2017000c5a6aa4d0 2017000c5a6aa528 3405 AE 100 0 4 0 9085 0

2017000c4f97cc28 2017000c4f97cca0 3405 TX 12517398 2118 6 0 8081 0

0000000C5A6AAA10 0000000C5A6AAA68 3405 TO 5124 1 3 0 8081 0

2017000c5a6a2438 2017000c5a6a2490 3397 RT 1 0 6 0 9884 0

2017000c4fdc1288 2017000c4fdc1300 4255 TX 15073308 1757 6 0 4993 0

�� 000c5a6aacc8 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

%7feeb24cc490 %7feeb24cc4f0 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

2017000c5a6a4e80 2017000c5a6a4ed8 1420 AE 100 0 4 0 2362 0

%7feeb24cc490 %7feeb24cc4f0 1420 TM 5124 0 3 0 378 0

2017000c4fcab578 2017000c4fcab5f0 1420 TX 10223617 2128 6 0 378 0

0000000C5A6A1EF8 0000000C5A6A1F50 3680 RD 1 0 9888 0

2017000c5a6a1e18 2017000c5a6a1e70 3680 XR 4 0 1 0 9888 0

2017000c5a6a2198 2017000c5a6a21f0 3680 CF 0 0 2 0 9888 0

2017000c5a6a2278 2017000c5a6a22d0 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

2 select * from v $ lock where type! = 'Mr ';

Explained.

Elapsed: 00:00:00. 20

SQL> select * from table (dbms_xplan.display );

PLAN_TABLE_OUTPUT

Bytes ---------------------------------------------------------------------------------------

Plan hash value: 3524752130

Bytes --------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes --------------------------------------------------------------------------------------

| 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 |

Bytes --------------------------------------------------------------------------------------

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

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

2017000c5a6a1e18 2017000c5a6a1e70 3680 XR 4 0 1 0 11480 0

0000000C5A6A1EF8 0000000C5A6A1F50 3680 RD 1 0 11480 0

2017000c5a6a2198 2017000c5a6a21f0 3680 CF 0 0 2 0 11480 0

2017000c5a6a2438 2017000c5a6a2490 3397 RT 1 0 6 0 11476 0

2017000c5a6a2278 2017000c5a6a22d0 3680 RS 25 1 2 0 11476 0

0000000C4F97A3E8 0000000C4F97A460 1136 TX 15335430 878 6 0 0

2017000c5a6a2518 2017000c5a6a2570 3963 TS 3 1 3 0 11455 0

2017000c4fc26180 2017000c4fc261f8 569 TX 15007765 1622 6 0 0

0000000C4FDBD628 0000000C4FDBD6A0 3682 TX 13107205 2166 6 0 0

0000000C4FA02A30 0000000C4FA02AA8 2272 TX 14090252 2079 6 0 0

2017000c5a6a4940 2017000c5a6a4998 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

2017000c5a6a4a20 2017000c5a6a4a78 2831 PW 1 0 3 0 11453 0

2017000c5a6a9dd0 2017000c5a6a9e28 4258 AE 100 0 4 0 371 0

2017000c5a6aacc8 2017000c5a6aad20 4255 AE 100 0 4 0 6614 0

2017000c5a6a68f0 2017000c5a6a6948 4251 AE 100 0 4 0 369 0

.

.

.

2017000c4fcab578 2017000c4fcab5f0 1421 TX 14417935 2082 6 0 0

0000000C4FA8CEA8 0000000C4FA8CF20 3118 TX 13828121 2491 6 0 0

2017000c4fa08ed0 2017000c4fa08f48 1420 TX 11730968 2288 6 0 41 0

0000000C4FE47090 0000000C4FE47108 3965 TX 13369364 2318 6 0 0

0000000C44E18550 0000000C44E185C8 2835 TX 13238301 2255 6 0 0

0000000C4FCAC998 0000000C4FCACA10 1423 TX 10158112 2226 6 0 0

0000000C44F291E0 0000000C44F29258 290 TX 12779538 2146 6 0 0

519 rows selected.

Elapsed: 00:00:00. 19

If you have other solutions, leave a message to discuss them.