Oracle 10g에서 Shrink 대상 Segment 찾기

Oracle 10g에서 Online Segment Shrink 기능이 추가되면서, 동적으로 Segment의 크기를 줄여 줄 수 있게 되었다. 그러면 다음과 같은 질문을 할 수 있겠다.

"Shrink 대상이 되는 Segment(Table/Index/Partition) 목록을 어떻게 추출할 것인가?"

이런 작업을 수동으로 하려면 dbms_space 패키지를 이용한 일련의 복잡한 Script 작업이 필요하다. Segment 수가 많고 크기가 크다면 많은 시간과 리소스를 필요로 하는 일이 되어 버린다.

다행히 Oracle 10g에서 이러한 작업을 자동화하는 기능이 추가되었다.
아래와 같이 dba_scheduler_job 뷰를 조회해보면, auto_space_advisor_job이라는 스케쥴 작업이 등록되어 있고, 이 작업은 auto_space_advisor_prog이라는 프로그램을 수행한다.

select job_name, program_name
from dba_scheduler_jobs
where job_name like '%SPACE%';
-----------------------------------------------------
JOB_NAME               : AUTO_SPACE_ADVISOR_JOB      
PROGRAM_NAME           : AUTO_SPACE_ADVISOR_PROG

auto_space_advisor 프로그램은 dbms_space.auto_space_advisor_job_proc이라는 프로시저를 수행한다.

select program_name, program_action
from dba_scheduler_programs
where program_name = 'AUTO_SPACE_ADVISOR_PROG';
------------------------------------------------------------
PROGRAM_NAME          : AUTO_SPACE_ADVISOR_PROG   
PROGRAM_ACTION        : dbms_space.auto_space_advisor_job_proc

Auto Space Advisor는 Advisor의 한 종류로 10g에서 새로 추가된 Advisor이다. 이 Advisor는 Tablespace/Segment의 공간 사용 정도를 분석하여 공간을 절약하기 위해 적절한 충고/가이드를 하는 역할을 제공한다.

Auto Space Advisor에 의해 만들어진 가이드는 다음과 같은 방법으로 간편하게 조회 가능하다.

-- dbms_space.verify_shrink_candidate(_tbf) 이용
select * from
table(dbms_space.verify_shrink_candidate_tbf(user,'BIG_TABLE','TABLE',273395165));

declare
    b_shrinkable boolean;
begin
    b_shrinkable := dbms_space.verify_shrink_candidate
        (user,'T_SHRINK','TABLE',1000);
    if b_shrinkable then
        dbms_output.put_line('Shrinkable');
    else
        dbms_output.put_line('Unshrinkable');
    end if;
end;
/

-- dbms_space.asa_recommendations 이용
select * from table(dbms_space.asa_recommendations());

-- dbms_space.asa_recommendations은 다음과 같이 어떤 Segment가 얼마나 공간을 절약할 수 있고, 어떤 명령문을 사용하면 되는지 친절하게 알려준다.
-------------------------------------------------------------
SEGMENT_NAME                  : BIG_TABLE
SEGMENT_TYPE                  : TABLE
ALLOCATED_SPACE               : 300619974
USED_SPACE                    : 273395165
RECLAIMABLE_SPACE             : 27224809
RECOMMENDATIONS               : OWI.BIG_TABLE 테이블의 행 이동을 가능하게 하고 축소 작업을 수행하는 경우 예상되는 절약 공간은 27224809바이트입니다.
C1                            : alter table "OWI"."BIG_TABLE" shrink space
C2                            : alter table "OWI"."BIG_TABLE" shrink space COMPACT
C3                            : alter table "OWI"."BIG_TABLE" enable row movement

Oracle 이 제공하는 Advisor 기능이 점점 다양해지고 정밀해지면서 데이터베이스 진단에 필요한 각종 검증 작업이 대부분 자동화되고 있다. Buffer Cache의 크기나 Shared Pool 크기 진단에서 시작해서 Segment Space 크기 진단으로, 그리고 SQL 성능 자동 진단으로까지 발전하고 있다. Oracle 11g에서는 SQL 성능 진단에서 Index/Materialized View(10g)에 Partition 추천까지 추가될 정도로 이 자동 진단 기능이 확장되고 있다.

아마 이런 자동 진단 및 추천 기능들이 앞으로는 DBA들이 알아야 할 필수 지식이 되지 않을까.

출처 : http://ukja.tistory.com/89