10g 回收站(RECYCLE BIN)導致查詢表空間的利用率時很慢

ysping發表於2009-08-06
一日使用OEM檢查資料庫(10g)資料檔案、表空間時,發現頁面彈出很慢,大概需要十幾分鍾![@more@]

以前在9i中使用OEM檢查資料檔案、表空間時,頁面彈出很快。後來發現是由於10g 的recyclebin的原因。

1、使用sys使用者登入

2、SQL>select count(*) from sys.recyclebin$;

9657

SQL>select count(*) from recyclebin;

0

3、SQL>purge recyclebin; --purge當前使用者在回收站中的物件

Done.

4、SQL>select count(*) from recyclebin;

0

SQL>select count(*) from sys.recyclebin$;

9657

5、SQL>purge dba_recyclebin; --purge所有使用者在回收站中的物件

Done

6、SQL>select count(*) from recyclebin;

0

SQL>select count(*) from sys.recyclebin$;

0

7、再次使用OEM查詢資料檔案、表空間,頁面彈出很快。


8、關閉回收站

SQL>alter system set recyclebin=off scope=both sid='*';

purge掉回收站的內容後再次查詢表空間頁面,彈出很快。

在eygle的一篇文章中提到:

關於dba_free_space檢視的構成,9i 、10g發生了變化。在Oracle10g中,如果啟用flash drop功能,在drop表時,資料庫不會直接刪除,而是將其放在回收站中,當空間出現短缺時,才會逐漸回收這部分空間。當物件刪除之後,這部分空間會記入free space,也就是說被包含在dba_free_space的查詢結果之中。

我們看一下這個檢視的變化,在Oracle9i之中,這個檢視構建如下:

SQL> set long 9999
SQL> set pagesize 99
SQL> select text from dba_views where view_name=upper('&view_name');
Enter value for view_name: dba_free_space
old 1: select text from dba_views where view_name=upper('&view_name')
new 1: select text from dba_views where view_name=upper('dba_free_space')

TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

而在Oracle10g中,這個檢視增加了關於recyclebin的部分:

SQL> set long 9999
SQL> set pagesize 99
SQL> select text from dba_views where view_name=upper('&view_name');
Enter value for view_name: dba_free_space
old 1: select text from dba_views where view_name=upper('&view_name')
new 1: select text from dba_views where view_name=upper('dba_free_space')

TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.
recyclebin
$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and rb.file# = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.
recyclebin
$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0

這就是Oracle10g的回收站(recyclebin)和自由空間管理的變化。

Oracle10g中,有時候查詢dba_free_space檢視異常緩慢,就是因為recyclebin中物件太多的緣故。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/670493/viewspace-1024965/,如需轉載,請註明出處,否則將追究法律責任。

相關文章