v$lock视图是Oracle中经典的锁检测接口之一，该视图列出了当前实例中被持有的锁信息；其中TYPE列指出了该持有锁的类型，比较常见的TX锁即事务队列锁(Transaction enqueue)和TM锁即DML enqueue锁(俗称表锁);一般某个会话在其事务(transaction)结束前总是持有一个TX锁,并一个或多个TM ROW-X(SX)锁 (一个事务中可能对多个表或对象进行了更新). 但偶尔我们会发现某个会话持有一个TX锁，却没有对应的TM锁的情况. 第一次接触到该问题，可能会觉得有些不可思议，但这种情况却的确存在：
查询v$lock缓慢和direct path write temp等待
2010/01/06 by 1 Comment
v$lock是常用的enqueue lock队列锁动态性能视图，不管是用户自己部署的监控脚本也好、还是enterprise manager都多少会使用到该V$LOCK视图， 但是在10g中遇到了v$lock查询缓慢的问题， 例如下面的查询会等待较多direct path write temp等待事件：
select count(*) from v$lock;
COUNT(*)
----------
163
Elapsed: 00:00:60.90
Execution Plan
----------------------------------------------------------
Plan hash value: 2384831130
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 1 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 50 | | |
|* 2 | HASH JOIN | | 1 | 50 | 1 (100)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 100 | 3800 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 100 | 1900 | 0 (0)| 00:00:01 |
| 6 | FIXED TABLE FULL | X$KSQRS | 100 | 1900 | 0 (0)| 00:00:01 |
| 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | FILTER | | | | | |
| 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)| 00:00:01 |
|* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)| 00:00:01 |
|* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
direct path write temp
direct path write temp
direct path write temp
................
显然仅返回100多条记录的v$LOCK视图的查询不该这么慢，也不该由SORT或HASH造成大量的临时空间使用， 究其根本还是FIXED TABLE即X$的内部表上的统计信息不准确导致的执行计划使用,通过使用RULE HINT可以马上获得较好的性能：
select /*+ RULE */ count(*) from v$LOCK;
COUNT(*)
----------
190
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 2026431807
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | MERGE JOIN | |
| 5 | SORT JOIN | |
| 6 | FIXED TABLE FULL | X$KSQRS |
|* 7 | SORT JOIN | |
| 8 | VIEW | GV$_LOCK |
| 9 | UNION-ALL | |
|* 10 | FILTER | |
| 11 | VIEW | GV$_LOCK1 |
| 12 | UNION-ALL | |
|* 13 | FIXED TABLE FULL| X$KDNSSF |
|* 14 | FIXED TABLE FULL| X$KSQEQ |
|* 15 | FIXED TABLE FULL | X$KTADM |
|* 16 | FIXED TABLE FULL | X$KTATRFIL |
|* 17 | FIXED TABLE FULL | X$KTATRFSL |
|* 18 | FIXED TABLE FULL | X$KTATL |
|* 19 | FIXED TABLE FULL | X$KTSTUSC |
|* 20 | FIXED TABLE FULL | X$KTSTUSS |
|* 21 | FIXED TABLE FULL | X$KTSTUSG |
|* 22 | FIXED TABLE FULL | X$KTCXB |
|* 23 | SORT JOIN | |
|* 24 | FIXED TABLE FULL | X$KSUSE |
-------------------------------------------------
针对上述问题考虑为FIXED TABLE收集统计信息，可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS标准存储过程，特别是对于版本升级上来的数据库，特别需要考虑执行该存储过程更新FIXED TABLE STATISTICS:
SQL> set timing on; SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed. Elapsed: 00:01:24.87
Create fixed table statistics
Directly after catupgrd.sql has been completed
This will speed up processing for recompilation with utlrp.sql
Create fixed table statistics again after a week with regular production workload
This task should be done only a few times per year
