oracle 10g recyclebin引起的dba_free_space性能问题
来源：互联网 发布：淘宝母婴包括哪些类目 编辑：程序博客网 时间：2024/05/18 02:51
oracle 10g recyclebin中数据太多引起的dba_free_space执行非常缓慢。最后通过清理recyclebin进行解决。SQL> select * from V$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64biPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0ProductionTNS for 64-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
经查看recyclebin中有大概3500条的记录
对回收站进行了清库
SQL>purge dba_recyclebin;
解决后大概需要 0.03秒就可以出结果
清空后的执行计划
SQL如下：
select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;
原来执行大概需要23s，优化前的执行计划
详细的执行计划如下：---------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |---------------------------------------------------------------------------------------------------------------------------------------------------| 1 | HASH GROUP BY | | 1 | 187 | 44 |00:00:22.63 | 20269 | 4693 | | | || 2 | VIEW | DBA_FREE_SPACE | 1 | 187 | 9464 |00:00:22.59 | 20269 | 4693 | | | || 3 | UNION-ALL | | 1 | | 9464 |00:00:22.56 | 20269 | 4693 | | | || 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 55 | 0 | | | || 5 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 55 | 0 | | | || 6 | TABLE ACCESS FULL | FET$ | 1 | 1 | 0 |00:00:00.01 | 55 | 0 | | | ||* 7 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | ||* 8 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | || 9 | NESTED LOOPS | | 1 | 90 | 362 |00:00:00.02 | 320 | 0 | | | || 10 | NESTED LOOPS | | 1 | 90 | 362 |00:00:00.01 | 318 | 0 | | | ||* 11 | TABLE ACCESS FULL | TS$ | 1 | 44 | 44 |00:00:00.01 | 55 | 0 | | | ||* 12 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 44 | 2 | 362 |00:00:00.01 | 263 | 0 | | | ||* 13 | INDEX UNIQUE SCAN | I_FILE2 | 362 | 1 | 362 |00:00:00.01 | 2 | 0 | | | || 14 | NESTED LOOPS | | 1 | 95 | 9102 |00:00:22.47 | 19240 | 4693 | | | || 15 | NESTED LOOPS | | 1 | 95 | 9102 |00:00:22.33 | 19238 | 4693 | | | ||* 16 | HASH JOIN | | 1 | 4603 | 4693 |00:00:00.05 | 134 | 0 | 1155K| 1155K| 1400K (0)|| 17 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 4870 | 4870 |00:00:00.01 | 79 | 0 | | | ||* 18 | TABLE ACCESS FULL | TS$ | 1 | 44 | 44 |00:00:00.01 | 55 | 0 | | | ||* 19 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 4693 | 1 | 9102 |00:00:22.22 | 19104 | 4693 | | | ||* 20 | INDEX UNIQUE SCAN | I_FILE2 | 9102 | 1 | 9102 |00:00:00.09 | 2 | 0 | | | || 21 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 654 | 0 | | | || 22 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 654 | 0 | | | || 23 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 654 | 0 | | | || 24 | TABLE ACCESS FULL | UET$ | 1 | 1 | 0 |00:00:00.01 | 654 | 0 | | | ||* 25 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | ||* 26 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | ||* 27 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 0 | 271 | 0 |00:00:00.01 | 0 | 0 | | | ||* 28 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | ||* 29 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |---------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 7 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#") 8 - filter(("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")) 11 - filter(("TS"."CONTENTS$"=0 AND INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."BITMAPPED"<>0)) 12 - filter("TS"."TS#"="F"."KTFBFETSN") 13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#") 16 - access("TS"."TS#"="RB"."TS#") 18 - filter(("TS"."CONTENTS$"=0 AND INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."BITMAPPED"<>0)) 19 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")) 20 - access("RB"."TS#"="FI"."TS#" AND "RB"."FILE#"="FI"."RELFILE#") 25 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#") 26 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")) 27 - access("U"."TS#"="RB"."TS#") 28 - filter("TS"."BITMAPPED"=0) 29 - access("TS"."TS#"="U"."TS#")经查看recyclebin中有大概3500条的记录
解决办法
对回收站进行了清库
SQL>purge dba_recyclebin;
解决后大概需要 0.03秒就可以出结果
清空后的执行计划
------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------------| 1 | HASH GROUP BY | | 1 | 187 | 44 |00:00:00.03 | 1108 | | | || 2 | VIEW | DBA_FREE_SPACE | 1 | 187 | 461 |00:00:00.03 | 1108 | | | || 3 | UNION-ALL | | 1 | | 461 |00:00:00.03 | 1108 | | | || 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 55 | | | || 5 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 55 | | | || 6 | TABLE ACCESS FULL | FET$ | 1 | 1 | 0 |00:00:00.01 | 55 | | | ||* 7 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | ||* 8 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | || 9 | NESTED LOOPS | | 1 | 90 | 461 |00:00:00.02 | 320 | | | || 10 | NESTED LOOPS | | 1 | 90 | 461 |00:00:00.01 | 318 | | | ||* 11 | TABLE ACCESS FULL | TS$ | 1 | 44 | 44 |00:00:00.01 | 55 | | | ||* 12 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 44 | 2 | 461 |00:00:00.01 | 263 | | | ||* 13 | INDEX UNIQUE SCAN | I_FILE2 | 461 | 1 | 461 |00:00:00.01 | 2 | | | || 14 | NESTED LOOPS | | 1 | 95 | 0 |00:00:00.01 | 79 | | | || 15 | NESTED LOOPS | | 1 | 95 | 0 |00:00:00.01 | 79 | | | ||* 16 | HASH JOIN | | 1 | 4603 | 0 |00:00:00.01 | 79 | 1155K| 1155K| 200K (0)|| 17 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 4870 | 0 |00:00:00.01 | 79 | | | ||* 18 | TABLE ACCESS FULL | TS$ | 0 | 44 | 0 |00:00:00.01 | 0 | | | ||* 19 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 0 | 1 | 0 |00:00:00.01 | 0 | | | ||* 20 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | || 21 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 654 | | | || 22 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 654 | | | || 23 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 654 | | | || 24 | TABLE ACCESS FULL | UET$ | 1 | 1 | 0 |00:00:00.01 | 654 | | | ||* 25 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | ||* 26 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | ||* 27 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 0 | 271 | 0 |00:00:00.01 | 0 | | | ||* 28 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | ||* 29 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | | | |------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 7 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#") 8 - filter(("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")) 11 - filter(("TS"."CONTENTS$"=0 AND INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."BITMAPPED"<>0)) 12 - filter("TS"."TS#"="F"."KTFBFETSN") 13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#") 16 - access("TS"."TS#"="RB"."TS#") 18 - filter(("TS"."CONTENTS$"=0 AND INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."BITMAPPED"<>0)) 19 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")) 20 - access("RB"."TS#"="FI"."TS#" AND "RB"."FILE#"="FI"."RELFILE#") 25 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#") 26 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")) 27 - access("U"."TS#"="RB"."TS#") 28 - filter("TS"."BITMAPPED"=0) 29 - access("TS"."TS#"="U"."TS#") 0 0
- oracle 10g recyclebin引起的dba_free_space性能问题
- oracle 回收站(recyclebin)引起的性能问题
- ORACLE 10G中的RecycleBin
- oracle 11g 回收站(recyclebin)的使用
- Oracle 11g中recyclebin参数的微小变化
- Oracle的recyclebin
- Oracle的recyclebin
- Oracle优化01-引起数据库性能问题的因素
- DBA_FREE_SPACE的file_id和relative_fno问题
- 常见引起性能的问题
- Oracle 静态SQL引起性能问题
- 10g Recyclebin Feature…
- Oracle dba_data_files dba_segments dba_free_space
- Oracle Recyclebin
- oracle recyclebin
- Oracle Recyclebin
- Oracle Recyclebin
- Oracle Recyclebin
- SQL操作常规语法【以后遇到更加好的,我会继续整理】
- leetcode: Longest Substring Without Repeating Characters
- 多线程与锁的知识小结
- ZOJ 1240 IBM Minus One
- poj 2892 Tunnel Warfare
- oracle 10g recyclebin引起的dba_free_space性能问题
- 【android官方文档】android 多国语言支持 国际化
- html 文本输入框效果
- libsvm代码阅读：svm.cpp浅谈和函数指针
- 串口异步读写
- 第三方存管与普通转账的区别
- python-pcap
- linux下查看电脑硬件设备属性
- http://baiy.cn/doc/cpp/inside_exception.htm#栈回退（Stack_Unwind）机制
