태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'Oracle 10g'에 해당되는 글 2건

  1. 2008.01.11 Oracle 10g에서 Shrink 대상 Segment 찾기 (6)
  2. 2008.01.02 (Oracle 10g) dbms_stats.gather_schema_stats 수행에 시간이 너무 많이 걸려요... (1)

Oracle 10g에서 Shrink 대상 Segment 찾기

오라클 2008.01.11 10:52
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들이 알아야 할 필수 지식이 되지 않을까...

신고
Trackback 0 : Comments 6
  1. 궁금이~ 2009.01.12 18:20 신고 Modify/Delete Reply

    좀 다른내용입니다만, 궁금해서요..
    부탁드려요~ ^^
    1. index shrink는 어떻게 이루어 지는 건가요? index coalesce와 같은 건가요?
    2. dba_indexes에는 왜 empty_block이라는 column이 없을까요?
    3. dbms_space의 unusage_space가 empty_block인가요?
    4. empty_block이 hwm 위에 있는 공간을 가리키는 건가요?

  2. 욱짜 2009.01.12 19:12 신고 Modify/Delete Reply

    1. Index Shrink의 동작 방식은 Coalesce와 비슷하지만,
    - Shrink는 실제 공간 해제가 필요하므로 물리적으로도 Segment의 앞 쪽으로 병합을 해야 합니다.
    - Alter Index ... Shrink Space Compact 명령은 병합만 수행할 뿐 실제 공간 해제를 하지 않고
    - Alter Index ... Shrink Space 명령은 실제 공간 해제까지 실행합니다.

    2. Empty_Block의 정확한 정의가 무엇입니까?

  3. 궁금이~ 2009.01.13 11:49 신고 Modify/Delete Reply

    empty_block은 HWM위에 있는, 즉, 할당되었지만 한번도 사용되지 않은 block의 수를 나타내는 column이 아닌지요?

  4. 욱짜 2009.01.13 12:46 신고 Modify/Delete Reply

    dba_tables(혹은 dba_tab_partitions).empty_block을 말씀하시는 걸로 이해됩니다. 아래 두 문서를 참조하시면 됩니다.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091

    Metalink doc# 149516.1

  5. Oratong 2011.06.29 16:14 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  6. LOPEZ18Deborah 2011.11.01 16:27 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


(Oracle 10g) dbms_stats.gather_schema_stats 수행에 시간이 너무 많이 걸려요...

오라클 2008.01.02 22:41
dbms_stats.gather_schema_stats 프로시저를 이용해서 통계 정보를 수집하는 경우 수행 시간이 너무 많이 걸린다는 불만이 종종 있다. 이런 현상은 대부분 다음과 같은 경우에 발생한다.

- 특정 테이블들의 크기가 매우 크다.
- 일부 큰 테이블들은 Partitioning이 되어 있다.

즉 큰 테이블들이 있고 더구나 Partitioning까지 되어 있으니(Global/Partiton Statistics모두 수집해야 하므로) 그만큼 통계 정보 수집에 시간이 걸리기 마련이다. 하지만 Oracle 10g에서는 estimate_percent 파라미터와 method_opt 파라미터의 값이 Oracle에 의해 적절하게 판단되어 통계 정보 생성 시간을 최적화한다. 그렇다면 왜 필요 이상 많은 시간이 걸리는 경우가 발생하는가?

이것은 이 두 파라미터에 다음과 같은 결함이 있기 때문이다.

- estimate_percent 파라미터의 값이 AUTO이다. 즉, Oracle이 테이블의 크기에 따라 적절한 샘플 크기를 선택한다는 의미이다. 대부분의 경우 Oracle은 테이블의 크기에 따라 적절한 값을 찾지만, 간혹 아주 큰 테이블에 대해 100%의 값을 사용하는 경우가 있다.
- method_opt 파라미터의 기본값이 FOR ALL COLUMNS SIZE AUTO이다. 즉 모든 컬럼에 대해 통계정보를 수집하면 히스토그램의 생성 여부는 Oracle이 판단한다. 대부분의 경우 Oracle은 적절한 히스토그램 버킷 크기를 할당하지만, 간혹 불필요하게(가령 Unique Key에 대해) 히스토그램을 생성하는 경우가 있다.

위와 같은 상황이 발생하면 예상보다 지나치게 많은 시간이 걸리게 되는 셈이다.

이런 상황을 해소할 수 있는 방법이 있을까? 가령 다음과 같이 하면...?

dbms_stats.gather_schema_stats(estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

물론 위의 방법으로 수행 시간 문제는 해결하겠지만, 좋은 방법은 될 수 없다. 만일 크기가 작은 테이블들이 있다면 샘플 크기가 작으면 왜곡된 통계 정보가 수집될 수 있다. 만일 특정 테이블에 대해서는 모든 컬럼(ALL COLUMNS)에 대해 통계 정보를 수집해야 한다면? 또는 특정 테이블의 특정 컬럼에 대해서는 히스토그램 크기를 주고 싶다면?

그렇다고 테이블 레벨로 일일이 dbms_stats.gather_table_stats를 호출하는 것은 너무나 지겨운 일이다.

그래서 보통 다음과 같은 트릭을 종종 사용한다.(편의상 정확한 문법은 생략)

-- 특정 테이블 통계 정보 변경을 막음
dbms_stats.lock_table_stats(user, 'TABLE1');

-- 스키마 레벨로 통계 정보 수집
dbms_stats.gather_schema_stats(user);

-- 통계 정보 활성화 후 이 테이블만 다시 통계 정보 수집
dbms_stats.unlock_table_stats(user, 'TABLE1');
dbms_stats.gather_table_stats(user,'TABLE', estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

위와 같이 하면 Schema 레벨로 대부분의 테이블에 대해 통계 정보를 수집한 후 특정 문제가 되는 테이블에 대해서만 별도로 통계 정보를 수집할 수 있다.

지저분한 트릭같지만 매우 유용한 방법이다.

Oracle 11g에서는 "Preference"라는 새로운 개념을 통해 위와 같은 불편을 일시에 해소하고 있다. Oracle 11g에서라면 다음과 같이 훨씬 직관적이고 깔끔한 방법을 쓸 수 있다.

-- 아래와 같이 한번만 설정
dbms_stats.set_table_prefs(user,'TABLE1', 'ESTIMATE_PERCENT', '10');
dbms_stats.set_table_prefs(user,'TABLE1','METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- Schema 레벨에서 통계 정보 수집
dbms_stats.gather_schema_stats(user);

즉, 특정 테이블별로 Preference(선호도)를 저장할 수 있어서 10g에서와 같은 부자연스러운 트릭은 불필요한 것이다. 11g에서 가장 마음에 드는 New Feature중 하나이다.
신고
Trackback 0 : Comment 1
  1. 김민기 2011.10.22 17:50 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바