'오라클' 카테고리의 글 목록 (20 Page)

오라클

Reog 대상 확인 2014.08.13

Reog 대상 확인

2014. 8. 13. 14:35

728x90

테이블 스페이스 공간 문제에 직면하던가, 쿼리 성능 향상을 위하여 Reog 대상 Object들을 확인해야 할 경우가 발생하게 된다.

Max값을 가져오는 단순한 쿼리이고, Index desc 힌트가 있음에도 해당 쿼리가 3000 블럭을 넘게 읽어 오고 있다.

10200 트레이스를 이용해서 확인해 보면 대부분 Delete에 의해서 데이터가 삭제 되어진 빈 블럭을 읽고 있다.

그래서 테이블 및 인덱스 관련하여 불필요하게 공간을 차지하고 있는 블럭에 대한 확인을 하게 됨.

오라클에서 제공하는 dbms_space 패키지를 사용하여 쉽게 확인 할 수 있다.

(ASSM 으로 관리되는 테이블스페이스 객체만 사용가능)

procedure unused_space(segment_owner IN varchar2,
                         segment_name IN varchar2,
                         segment_type IN varchar2,
                         total_blocks OUT number,
                         total_bytes OUT number,
                         unused_blocks OUT number,
                         unused_bytes OUT number,
                         last_used_extent_file_id OUT number,
                         last_used_extent_block_id OUT number,
                         last_used_block OUT number,
                         partition_name IN varchar2 DEFAULT NULL
                         );
  pragma restrict_references(unused_space,WNDS);

--  Returns information about unused space in an object (table, index,
  --    or cluster).
  --  Input arguments:
  --   segment_owner
  --      schema name of the segment to be analyzed
  --   segment_name
  --      object name of the segment to be analyzed
  --   partition_name
  --      partition name of the segment to be analyzed
  --   segment_type
  --      type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
  --  Output arguments:
  --   total_blocks
  --      total number of blocks in the segment
  --   total_bytes
  --      the same as above, expressed in bytes
  --   unused_blocks
  --      number of blocks which are not used
  --   unused_bytes
  --      the same as above, expressed in bytes
  --   last_used_extent_file_id
  --      the file ID of the last extent which contains data
  --   last_used_extent_block_id
  --      the block ID of the last extent which contains data
  --   last_used_block
  --      the last block within this extent which contains data

procedure space_usage(segment_owner IN varchar2,
                         segment_name IN varchar2,
                         segment_type IN varchar2,
                         unformatted_blocks OUT number,
                         unformatted_bytes OUT number,
                         fs1_blocks OUT number,
                         fs1_bytes  OUT number,
                         fs2_blocks OUT number,
                         fs2_bytes  OUT number,
                         fs3_blocks OUT number,
                         fs3_bytes  OUT number,
                         fs4_blocks OUT number,
                         fs4_bytes  OUT number,
                         full_blocks OUT number,
                         full_bytes OUT number,
                         partition_name IN varchar2 DEFAULT NULL
                         );
  pragma restrict_references(space_usage,WNDS);

--  Returns information about space occupation in an object (table, index,
  --    or cluster).
  --  Input arguments:
  --   segment_owner
  --      schema name of the segment to be analyzed
  --   segment_name
  --      object name of the segment to be analyzed
  --   partition_name
  --      partition name of the segment to be analyzed
  --   segment_type
  --      type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
  --  Output arguments:
  --   unformatted_blocks
  --      total number of blocks that are unformatted
  --   unformatted_bytes
  --      the same as above, expressed in bytes
  --   fs1_blocks
  --      number of blocks that have atleast 0 to 25% free space.
  --   fs1_bytes
  --      same as above, expressed in bytes
  --   fs2_blocks
  --      number of blocks that have atleast 25% to 50% free space.
  --   fs2_bytes
  --      same as above, expressed in bytes
  --   fs3_blocks
  --      number of blocks that have atleast 50% to 75% free space.
  --   fs3_bytes
  --      same as above, expressed in bytes
  --   fs4_blocks
  --      number of blocks that have atleast 75% to 100% free space.
  --   fs4_bytes
  --      same as above, expressed in bytes
  --   full_blocks
  --      total number of blocks that are full in the segment
  --   full_bytes
  --      the same as above, expressed in bytes

가장 간단하게 확인해 볼 수 있는 방법

일일이 변수 선언하기 귀찮으니 테이블 펑션으로 생성해서 쉽게 조회하는 방법

CREATE OR REPLACE PACKAGE SPACE_CHECK 
AS
    TYPE return_row IS RECORD (
             segment_owner  varchar2(30) ,
             segment_name varchar2(81) ,
             segment_type varchar2(19) ,
			 segment_space_management varchar2(6) ,
             total_blocks number,
             total_bytes number,
             unused_blocks number,
             unused_bytes number,
             last_used_extent_file_id number,
             last_used_extent_block_id number,
             last_used_block number,
             unformatted_blocks  number,
             unformatted_bytes  number,
             fs1_blocks  number,
             fs1_bytes   number,
             fs2_blocks  number,
             fs2_bytes   number,
             fs3_blocks  number,
             fs3_bytes   number,
             fs4_blocks  number,
             fs4_bytes   number,
             full_blocks  number,
             full_bytes  number ) ;

TYPE return_table IS TABLE OF return_row ;

FUNCTION F_SPACE_USAGE(i_owner varchar2)
     RETURN return_table
     PIPELINED;

END SPACE_CHECK ;

CREATE OR REPLACE PACKAGE BODY SPACE_CHECK AS

FUNCTION F_SPACE_USAGE(i_owner varchar2)
        RETURN return_table
        PIPELINED IS

rec            return_row;
     
        -- common 
        v_segment_owner  varchar2(30) ;
        v_segment_name varchar2(81) ;
        v_segment_type varchar2(19) ;
		v_management varchar2(6) ; 
        
        -- unused_space
        v_total_blocks number(22) ;
        v_total_bytes number(22) ;
        v_unused_blocks number(22) ;
        v_unused_bytes number(22) ;
        v_last_used_extent_file_id number(22) ;
        v_last_used_extent_block_id number(22) ;
        v_last_used_block number(22) ;
        v_partition_name varchar2(30) DEFAULT NULL ;
        
        -- space_usage
        v_unformatted_blocks  number;
        v_unformatted_bytes  number;
        v_fs1_blocks  number;
        v_fs1_bytes   number;
        v_fs2_blocks  number;
        v_fs2_bytes   number;
        v_fs3_blocks  number;
        v_fs3_bytes   number;
        v_fs4_blocks  number;
        v_fs4_bytes   number;
        v_full_blocks  number;
        v_full_bytes  number;

BEGIN

FOR v_for IN ( SELECT sm.owner , sm.segment_name , sm.segment_type , ts.segment_space_management 
                   FROM dba_segments sm , dba_tablespaces ts 
                   WHERE owner = i_owner 
                   AND segment_type IN ('TABLE','INDEX') 
                   AND sm.tablespace_name = ts.tablespace_name ) LOOP 
            
            v_segment_owner := v_for.owner ;
            v_segment_name := v_for.segment_name ;
            v_segment_type := v_for.segment_type	;	
            v_management := v_for.segment_space_management ;

--exec dbms_space package
			 -- unused_space DDL lock being held on until PL/SQL call is completed.
			 -- unused_space must have ANALYZE ANY privilege 
			 
            dbms_space.unused_space ( v_segment_owner 
                                      , v_segment_name 
                                      , v_segment_type 
                                      , v_total_blocks 
                                      , v_total_bytes 
                                      , v_unused_blocks 
                                      , v_unused_bytes 
                                      , v_last_used_extent_file_id 
                                      , v_last_used_extent_block_id 
                                      , v_last_used_block
                                      , v_partition_name ) ;

rec.segment_owner :=  v_segment_owner ;           
             rec.segment_name := v_segment_name ;
             rec.segment_type := v_segment_type ;
			 rec.segment_space_management := v_management ;
			 
             rec.total_blocks := v_total_blocks ;
             rec.total_bytes := v_total_bytes ;
             rec.unused_blocks := v_unused_blocks ;
             rec.unused_bytes := v_unused_bytes ;
             rec.last_used_extent_file_id := v_last_used_extent_file_id ;
             rec.last_used_extent_block_id := v_last_used_extent_block_id ;
             rec.last_used_block := v_last_used_block ;			
			
			IF 	v_management = 'AUTO' THEN			 
                 dbms_space.space_usage ( v_segment_owner 
                                          , v_segment_name 
                                          , v_segment_type 
                                          , v_unformatted_blocks  
                                          , v_unformatted_bytes  
                                          , v_fs1_blocks  
                                          , v_fs1_bytes   
                                          , v_fs2_blocks  
                                          , v_fs2_bytes   
                                          , v_fs3_blocks  
                                          , v_fs3_bytes   
                                          , v_fs4_blocks  
                                          , v_fs4_bytes   
                                          , v_full_blocks  
                                          , v_full_bytes) ;	
                 
                  rec.unformatted_blocks := v_unformatted_blocks  ;
                  rec.unformatted_bytes := v_unformatted_bytes  ;
                  rec.fs1_blocks := v_fs1_blocks  ;
                  rec.fs1_bytes := v_fs1_bytes   ;
                  rec.fs2_blocks := v_fs2_blocks  ;
                  rec.fs2_bytes := v_fs2_bytes   ;
                  rec.fs3_blocks := v_fs3_blocks  ;
                  rec.fs3_bytes :=  v_fs3_bytes   ;
                  rec.fs4_blocks := v_fs4_blocks  ;
                  rec.fs4_bytes := v_fs4_bytes   ;
                  rec.full_blocks := v_full_blocks  ;
                  rec.full_bytes := v_full_bytes  ;
            ELSE

rec.unformatted_blocks := 0  ;
                  rec.unformatted_bytes := 0  ;
                  rec.fs1_blocks := 0  ;
                  rec.fs1_bytes := 0   ;
                  rec.fs2_blocks := 0  ;
                  rec.fs2_bytes := 0   ;
                  rec.fs3_blocks := 0  ;
                  rec.fs3_bytes :=  0   ;
                  rec.fs4_blocks := 0  ;
                  rec.fs4_bytes := 0   ;
                  rec.full_blocks := 0  ;
                  rec.full_bytes := 0  ;
			
			END IF ;
			
             PIPE ROW (rec);
             
	END LOOP ;
    
	RETURN;
	
    END F_SPACE_USAGE ;
END;

사용법은 단순하다.

다음과 같이 스키마명만 넣어 주고 사용하면 됨.

SELECT segment_owner ,
       segment_name ,
       segment_type ,
       segment_space_management ,
       total_blocks ,
       total_bytes ,
       unused_blocks ,
       unused_bytes ,
       unformatted_blocks ,
       unformatted_bytes ,
       fs1_blocks ,
       fs1_bytes ,
       fs2_blocks ,
       fs2_bytes ,
       fs3_blocks ,
       fs3_bytes ,
       fs4_blocks ,
       fs4_bytes ,
       full_blocks ,
       full_bytes
FROM   TABLE( space_check.f_space_usage( 'SCOTT' ) ) ;

그럼 해당 패키지를 이용하여 대상 Object를 선정해 보자.

fs1 : 블럭내의 Free 공간이 0~25% 
fs2 : 블럭내의 Free 공간이 25~50%
fs3 : 블럭내의 Free 공간이 50~75%
fs4 : 블럭내의 Free 공간이 75~100%

fs1 / fs2 / fs3 / fs4 모두 관련 Object를 Shrink 할 경우 테이블 스페이스로 반환 되어질 공간들이 있다는 소리이므로

col segment_name format a24 heading "Seg|Name"
col segment_owner format a8 heading "Seg|Owner"
col segment_type format a6 heading "Seg|Type"
col total_blocks format 99,999,999 heading "Total|Blocks"
col total_bytes format 99,999,999,999 heading "Total|Byte"
col fs1_blocks format 999,999 heading " 0~25|Blocks" 
col fs1_bytes format 999,999 heading "0~25 |Byte" 
col fs2_blocks format 99,999,999 heading "25~50|Block" 
col fs2_bytes format 99,999,999,999 heading "25~50|Byte" 
col fs3_blocks format 99,999,999 heading "50~75|Blocks" 
col fs3_bytes format 99,999,999,999 heading "50~75|Byte" 
col fs4_blocks format 99,999,999 heading "75~100|Blocks" 
col fs4_bytes format 99,999,999,999 heading "75~100|Byte" 
col full_blocks format 99,999,999 heading "Full|Blocks" 
col full_bytes format 99,999,999,999 heading "Full|Byte" 
col free_sum_blocks format 99,999,999 heading "FreeSum|Blocks"
col free_sum_bytes format 99,999,999,999 heading "FreeSum|Byte" 
col free_block_percent format 999.9 heading "Free|%"
set pagesize 300
set line 300

select segment_name ,
       segment_owner ,
       segment_type ,
       total_blocks ,
       total_bytes ,
       fs1_blocks ,
       fs1_bytes ,
       fs2_blocks ,
       fs2_bytes ,
       fs3_blocks ,
       fs3_bytes ,
       fs4_blocks ,
       fs4_bytes ,
       full_blocks ,
       full_bytes ,
	   (fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) free_sum_blocks ,
	   (fs1_bytes + fs2_bytes + fs3_bytes + fs4_bytes) free_sum_bytes ,
	   ((fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) / total_blocks) * 100 free_block_percent
from table(space_check.f_space_usage('SCOT_APP'))
where fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks > 1000
order by (fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) desc ;

Seg                      Seg      Seg          Total           Total     0~25    0~25        25~50           25~50       50~75           50~75      75~100          75~100        Full            Full     FreeSum         FreeSum   Free
Name                     Owner    Type        Blocks            Byte   Blocks     Byte       Block            Byte      Blocks            Byte      Blocks            Byte      Blocks            Byte      Blocks            Byte      %
------------------------ -------- ------ ----------- --------------- -------- -------- ----------- --------------- ----------- --------------- ----------- --------------- ----------- --------------- ----------- --------------- ------
TEST_BBB_LIST_DTL        SCOT_APP TABLE       95,360     781,189,120       18  147,456          39         319,488          43         352,256      68,363     560,029,696      26,233     214,900,736      68,463     560,848,896   71.8
TEST_BBB_TASK_RSLT       SCOT_APP TABLE       26,624     218,103,808        6   49,152          22         180,224          16         131,072      24,885     203,857,920       1,312      10,747,904      24,929     204,218,368   93.6
TEST_TEST12_RSLT         SCOT_APP TABLE       47,104     385,875,968        8   65,536          45         368,640          40         327,680      21,075     172,646,400      25,649     210,116,608      21,168     173,408,256   44.9
TEST_BBB_LIST_DTL_PK     SCOT_APP INDEX       21,520     176,291,840        0        0      15,685     128,491,520           0               0           0               0       5,589      45,785,088      15,685     128,491,520   72.9
TEST_TEST12_RSLT_PK      SCOT_APP INDEX       33,280     272,629,760        0        0      11,934      97,763,328           0               0           0               0      21,112     172,949,504      11,934      97,763,328   35.9
TEST_AAAAAAAAA_DTL_PK    SCOT_APP INDEX       19,048     156,041,216        0        0       7,417      60,760,064           0               0           0               0      11,442      93,732,864       7,417      60,760,064   38.9
TEST_AAAAAAAAA_DTL       SCOT_APP TABLE       19,200     157,286,400        7   57,344          39         319,488          41         335,872       7,288      59,703,296      11,646      95,404,032       7,375      60,416,000   38.4
TEST_DEPTF_MANAG_RSLT    SCOT_APP TABLE      282,240   2,312,110,080        2   16,384           5          40,960           7          57,344       6,487      53,141,504     271,927   2,227,625,984       6,501      53,256,192    2.3
TEST_BBB_TASK_RSLT_PK    SCOT_APP INDEX        5,760      47,185,920        0        0       5,380      44,072,960           0               0           0               0         281       2,301,952       5,380      44,072,960   93.4
TEST_DEPTF_LOC_RSLT      SCOT_APP TABLE      364,160   2,983,198,720       16  131,072          10          81,920          13         106,496       2,570      21,053,440     356,679   2,921,914,368       2,609      21,372,928     .7
TEST_SAL_BND_ISU         SCOT_APP TABLE      236,032   1,933,574,144        1    8,192           3          24,576           0               0       1,594      13,058,048     228,989   1,875,877,888       1,598      13,090,816     .7
TEST_DEPTF_LOC_RSLT_PK   SCOT_APP INDEX      221,952   1,818,230,784        0        0       1,427      11,689,984           0               0           0               0     217,947   1,785,421,824       1,427      11,689,984     .6
TEST_AVG_VAR             SCOT_APP TABLE      271,104   2,220,883,968        1    8,192           4          32,768           1           8,192       1,365      11,182,080     263,870   2,161,623,040       1,371      11,231,232     .5
TEST_CCCC_FLOW           SCOT_APP TABLE       13,312     109,051,904        1    8,192           5          40,960           3          24,576         992       8,126,464      10,152      83,165,184       1,001       8,200,192    7.5

14 rows selected.

위의 결과에서 Free Block이 많이 있는 Object들을 대상으로 삼으면 된다.

SHRINK 명령을 이용하여 빈 공간을 테이블 스페이스로 반환 시킬 수 있다.

SHRINK를 사용하기 전에 제약사항 부터 확인해 보자

1. 10g 이상에서 사용이 가능하므로 혹시 마이그레이션으로 버젼을 올렸을 경우 파라메터를 다음과 같이 설정
      (Init.ora parameter 'Compatible' must be >=10.0)
   2. ASSM 으로 관리되는 테이블 스페이스여야 함
   3. FBI(Function-Based Index)를 Shrink 할 경우는 오류가 발생 하므로 해당 FBI는 지우고 새로 생성
   4. Shrink 중 DML 작업은 가능하나, Parallel DML은 수행 불가
   5. HWM를 옮기는 과정에서 잠깐의 TM Lock(exclusive)이 잡힘
   6. MView 형태의 테이블중 on-commit materialized view와 관련된 세그먼트는 Shrink 안됨.
      rowid에 기반을 둔 MView에 대해서는 Refresh 또는 Rebuild를 수행하면 됨.

SHRINK 순서

1. NOLOGGING 설정 
      
      ALTER TABLE ....... NOLOGGING ;   OR ALTER INDEX ...... NOLOGGING ;

2. 테이블일 경우 Row를 이동시켜야 하므로, ROW MOVEMENT 활성 화
 
       ALTER TABLE ....... ENABLE ROW MOVEMENT ;
   
   3. SHRINK 명령 수행
   
      - 테이블
        ALTER TABLE ......... SHRINK SPACE CASCADE ;  --> 테이블과 관련된 OBJECT 까지 같이 Shrink 하고 테이블 스페이스로 반환 (HWM 이동됨)
                                       SHRINK SPACE ; --> 테이블 공간만 Shrink 하고 빈공간 테이블 스페이스로 반환 (HWM 이동 됨)
                                       SHRINK SPACE COMPACT ; --> 행들만 이동 , 공간 반환 안함 (HWM 이동 없음)
       
                                       CASCADE 옵션과 같이 사용하지 않았을 경우 인덱스 Rebuild 필요.

- 인덱스
       ALTER INDEX ...... SHRINK SPACE ; 
                                  SHRINK SPACE COMPACT ;

4. 테이블일 경우 ROW MOVEMENT Disable
   
      ALTER TABLE ........ DISABLE ROW MOVEMENT ;
  
   5. LOGGING 설정

ALTER TABLE ....... LOGGING ;   OR ALTER INDEX ...... LOGGING ;

예)

ALTER TABLE SCOTT.NLJ2 NOLOGGING ;
ALTER TABLE SCOTT.NLJ2 ENABLE ROW MOVEMENT ;
ALTER TABLE SCOTT.NLJ2 SHRINK SPACE CASCADE ;
ALTER TABLE SCOTT.NLJ2 DISABLE ROW MOVEMENT ;
ALTER TABLE SCOTT.NLJ2 LOGGING ;

PREV 1 ···17 18 19 20 21 22 23 ···43 NEXT

멋지게 놀아라

오라클

Reog 대상 확인

+ Recent posts

티스토리툴바