转载

recyclebin未清引起的查询表空间使用率慢

今天客户反应说跑sql一条sql非常慢，看了一下发现发现是一条查询表空间相关信息的sql。询问是否recyclebin数据太多引起？
为什么recyclebin太多垃圾会引起sql执行慢呢？针对此我研究了一下。

通过查看sql的执行计划发现问题应该来自于sql会对recyclebin$这个表进行访问，而该sql 查询的视图是db_tablespaces,dba_data_files和dba_free_space三个视图，sql对recyclebin$访问正是来源于查询 dba_free_space。

SQL> set linesize 200 pagesize 200
SQL> explain plan for select bytes from dba_free_space;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2345329605
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10559 | 134K| 20 (10)| 00:00:01 |
| 1 | VIEW | DBA_FREE_SPACE | 10559 | 134K| 20 (10)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 1 | 56 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 45 | 1 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | I_FILE2 | 9 | 54 | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS CLUSTER | FET$ | 1 | 39 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 0 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 65 | 4030 | 6 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 65 | 3640 | 6 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | TS$ | 8 | 136 | 6 (0)| 00:00:01 |
|* 13 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 8 | 312 | 0 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 10492 | 1014K| 9 (23)| 00:00:01 |
| 16 | NESTED LOOPS | | 5 | 170 | 7 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 5 | 85 | 2 (0)| 00:00:01 |
| 18 | INDEX FULL SCAN | I_FILE2 | 9 | 54 | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 5 | | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS CLUSTER | TS$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 23 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 2222 | 141K| 0 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 80 | 4 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 63 | 4 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | RECYCLEBIN$ | 5 | 55 | 4 (0)| 00:00:01 |
| 28 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 0 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
|* 30 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | 0 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 6 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("F"."FILE#"="FI"."RELFILE#")
7 - access("F"."TS#"="FI"."TS#")
8 - filter("TS"."BITMAPPED"=0)
9 - access("TS"."TS#"="F"."TS#")
12 - filter("TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
"TS"."CONTENTS$"=0)
13 - filter("TS"."TS#"="F"."KTFBFETSN")
14 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
20 - access("RB"."TS#"="FI"."TS#")
21 - filter("TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
"TS"."CONTENTS$"=0)
22 - access("TS"."TS#"="RB"."TS#")
23 - filter("U"."KTFBUEFNO"="FI"."RELFILE#" AND "U"."KTFBUESEGTSN"="RB"."TS#" AND
"U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
29 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
"U"."SEGBLOCK#"="RB"."BLOCK#")
30 - filter("TS"."BITMAPPED"=0)
31 - access("TS"."TS#"="U"."TS#")
32 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
62 rows selected.

从执行计划中可以看出，RECYCLEBIN$两次被访问，
第一次是作为被驱动表与驱动表 FILE$进行nested loops。此时 RECYCLEBIN$ 因   FILE$返回的行数可能被扫描多次索引以及多次回表；
第二次是 RECYCLEBIN$作为驱动表与被驱动表UET$进行   nested loops，此时  RECYCLEBIN$进行一次全表扫描。

因此当回收站的堆积了太多的数据的时候，有时在跑一些查询表空间的脚本会越来越慢。

正文到此结束