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
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277089