oracle 查询 v$lock 奇慢无比
8.31号中午12点左右, 突然接到开发部门报告.
declare cur_lock cursor for
select distinct sid
from v$lock
where block=1;
open cur_lock;
执行这个cursor 的时候, 相当慢.
直接从sql 客户端执行也是很慢.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 493571612
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23724 | 3035K| 68 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 23724 | 3035K| 68 (100)| 00:00:01 |
|* 2 | HASH JOIN | | 75 | 7875 | 54 (100)| 00:00:01 |
| 3 | VIEW | GV$_LOCK | 75 | 6675 | 51 (100)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | VIEW | GV$_LOCK1 | 64 | 5696 | 34 (100)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
|* 8 | FIXED TABLE FULL| X$KDNSSF | 1 | 40 | 3 (100)| 00:00:01 |
|* 9 | FIXED TABLE FULL| X$KSQEQ | 63 | 2583 | 31 (100)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTADM | 1 | 42 | 12 (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 | 56 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSC | 3 | 120 | 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 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 17 | FIXED TABLE FULL | X$KTCXB | 2 | 100 | 3 (100)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 6048 | 96768 | 3 (100)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 31632 | 803K| 14 (100)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INS
T_ID")||RAWTOHEX("R"."ADDR"))
2 - access("SADDR"="S"."ADDR")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 - filter(USERENV('INSTANCE') IS NOT NULL)
8 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
9 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
10 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
11 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
12 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
14 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
15 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
16 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
17 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0
AND "INST_ID"=USERENV('INSTANCE'))
18 - filter("S"."INST_ID"=USERENV('INSTANCE'))
已选择 55 行。
执行计划并没有问题.
都是从底层表捞数据.
Oracle 官方文档: 文档 ID 1328789.1
解决方案: exec dbms_stats.GATHER_FIXED_OBJECTS_STATS
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/133735/viewspace-2979857/ ，如需转载，请注明出处，否则将追究法律责任。
