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