DBA_FREE_SPACE 뷰 조회 시 응답 시간이 느린 경우

성능 모니터링 툴의 테이블스페이스 사용량을 보여주는 기능 구현 후 테스트를 진행하다 발견한 현상입니다. 모니터링 툴에는 모래시계가 돌며 전체 프로그램은 느려지며 결과는 안나오고.... 

구현이 잘 못 되었는지 다시 보아도 이상은 없었습니다.

그래서 열심히 검색을 해보니 쿼리기반의 SQL 편집 툴에서도 종종 발생한다고 하여 더 자세히 알아보니 10g 부터 추가된 휴지통 기능이 원인을 제공하고 있었습니다.

테이블스페이스의 여유 공간을 알아보기 위해 DBA_FREE_SPACE를 조회하였는데 응답시간이 느려지면서 모니터링 툴까지 영향을 받고 있었습니다.

메타링크 확인 결과 원인은 2가지로

1. 10g 부터 추가된 휴지통 기능으로 DBA_FREE_SPACE뷰에서 삭제된(버려진) 테이블들의 크기를 조회하기 위해 sys.recyclebin$과 JOIN하는 부분 때문입니다.

SQL> select text from dba_views where view_name='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

2. sys.recyclebin$에 삭제된 데이터가 있을 경우.

제 테스트 환경(12.1.0.2.0)에서는 단 2건의 데이터가 있었지만 10초 이상 응답시간이 없었습니다. 

해결 방법은 휴지통을 비우거나 휴지통 기능을 사용 안하는 방법이 있습니다.

1. 휴지통 비우는 방법

사용 방법

SQL> purge recyclebin;

또는 SYSDBA로 전체 휴지통을 비우는 방법이 있습니다.

SQL> purge dba_recyclebin;

2. 휴지통 기능을 사용 안하는 방법(Default : recyclebin = ON)

세션 레벨 : 

SQL> ALTER SESSION SET recyclebin = OFF;

시스템 레벨 : 

SQL> ALTER SYSTEM SET recyclebin = OFF;

참조 : Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)

       10g Recyclebin Features and How to Disable it (Doc ID 265253.1)