数据库内部对象X$统计信息过旧，导致v$lock查询慢
前段时间用python写了个zabbix监控脚本，里面有一个检查锁的sql语句，sql语句是这样子的
select count(*) retvalue from v$lock where type in('TM', 'TX') and ctime > 600;
但是zabbix界面显示这条语句超时，zabbix超时时间默认是3s，我将其改为15s，竟然还是超时，看样子要仔细研究这个sql语句了。
这一看不得了，这条语句执行用了18s，统计v$lock的行数竟然要7min之久，这明显无法接受。
SQL> select count(*) retvalue from v$lock where type in('TM', 'TX') and ctime > 600;
RETVALUE
----------
0
Elapsed: 00:00:18.82
查看其执行计划
SQL> select * from table(dbms_xplan.display_cursor());
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 53 | |
|* 2 | HASH JOIN | | 1 | 53 | 0 (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 41 | 0 (0)|
|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)|
| 5 | BUFFER SORT | | 1 | 22 | 0 (0)|
|* 6 | FIXED TABLE FULL | X$KSQRS | 1 | 22 | 0 (0)|
| 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)|
| 8 | UNION-ALL | | | | |
|* 9 | FILTER | | | | |
| 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)|
| 11 | UNION-ALL | | | | |
|* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 77 | 0 (0)|
|* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 77 | 0 (0)|
|* 14 | FIXED TABLE FULL | X$KTADM | 1 | 77 | 0 (0)|
|* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 77 | 0 (0)|
|* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 77 | 0 (0)|
|* 17 | FIXED TABLE FULL | X$KTATL | 1 | 77 | 0 (0)|
|* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 77 | 0 (0)|
|* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 77 | 0 (0)|
|* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 77 | 0 (0)|
|* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 77 | 0 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SADDR"="S"."ADDR" AND
TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||
RAWTOHEX("R"."ADDR"))
4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
6 - filter(("R"."KSQRSIDT"='TM' OR "R"."KSQRSIDT"='TX'))
9 - filter(USERENV('INSTANCE') IS NOT NULL)
统计v$lock的行数
SQL> select count(*) from v$lock;
COUNT(*)
----------
600
Elapsed: 00:07:46.84
这条语句的执行计划与上面的一样，这里我就不贴出来了
v$lock只有600行，怎么会执行时间这么久，通过v$session能看到这条语句的等待事件为"direct path read temp"
该等待事件表示服务器进程直接读取临时表空间的数据，通常由临时表太大造成。从上面的执行计划中可以看出临时表很大的原因可能是"MERGE JOIN CARTESIAN"。
"MERGE JOIN CARTESIAN"表示笛卡尔联接，如果两表的行数都不小的话，这的确会造成临时表过大。查看X$KSUSE，X$KSQRS的行数
SQL> select count(*) from X$KSUSE;
COUNT(*)
----------
4544
SQL> select count(*) from X$KSQRS;
COUNT(*)
----------
20224
这几千和几万来个笛卡尔积就是几千万的临时数据了，而我的pga只有4g，pga不够所以就用到了临时表空间进行表关联，也就造成了等待事件，所以说这条语句慢的主因就是这个笛卡儿积。
这条语句之前执行都好好的，为什么现在慢了呢，最可能的情况是统计信息过旧，因为自动统计信息收集job不会收集固定对象也就是X$表的统计信息。
收集下固定对象的统计信息
SQL> begin
dbms_stats.gather_fixed_objects_stats;
end;
/
PL/SQL procedure successfully completed.
再执行以下语句，可以看到执行时间0.1s都不到，而且执行计划也恢复正常，赶紧在我这边的生产库把类似问题进行处理，嘿嘿。
SQL> select count(*) from v$lock;
COUNT(*)
----------
600
Elapsed: 00:00:00.08
SQL> select * from table(dbms_xplan.display_cursor());
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| 2 | HASH JOIN | | 3034 | 106K| 29 (100)| 00:00:01 |
| 3 | HASH JOIN | | 15 | 360 | 23 (100)| 00:00:01 |
| 4 | VIEW | GV$_LOCK | 15 | 180 | 22 (100)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FILTER | | | | | |
| 7 | VIEW | GV$_LOCK1 | 7 | 84 | 15 (100)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 16 | 1 (100)| 00:00:01 |
| 10 | FIXED TABLE FULL| X$KSQEQ | 6 | 102 | 14 (100)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KTADM | 1 | 20 | 5 (100)| 00:00:01 |
| 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 14 | 0 (0)| |
| 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 14 | 0 (0)| |
| 14 | FIXED TABLE FULL | X$KTATL | 1 | 20 | 0 (0)| |
| 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 14 | 0 (0)| |
| 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 16 | 0 (0)| |
| 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 14 | 0 (0)| |
| 18 | FIXED TABLE FULL | X$KTCXB | 1 | 18 | 1 (100)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSUSE | 4544 | 54528 | 1 (100)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 20224 | 237K| 5 (100)| 00:00:01 |
---------------------------------------------------------------------------------------
总结：
1.一些动态性能视图v$查询很慢的话，可能是由于动态性能视图所查询的内部对象表x$统计信息过旧，cbo选择了错误的执行计划造成。
2.自动统计信息收集job不会收集内部对象表的统计信息，所以需要dba定时手工收集，或者是自己创建个job定期执行。