[DBMS_SPACE,DBMS_ADVISOR]리오그 대상 확인 빌트인 패키지 비교

이전에 reorg 대상 확인하는 빌트인패키지(프로시저)를 정리한 글이 있었습니다.

아래 URL입니다.

최근에 리오그 대상 테이블을 확인하는 방법을 하나더 알게되어서 기존방법과 새로운 방법 중 어느것을 쓸지, 값은 비슷하게 나오는지 등을 이번에 비교하려고 합니다.

새로 알게된 빌트인 패키지는 DBMS_ADVISOR 패키지를 사용하여 리오그대상을 확인할 수 있습니다.

 

https://bae9086.tistory.com/359

테이블의 Reorg 대상확인 프로시저(DBMS_SPACE.SPACE_USAGE)

DBMS_SPACE에 대한 정리는 이전 글에 정리가 되어 있으므로 이번에는 DBMS_ADVISOR에 대해서 정리하고 테스트를 진행하겠습니다.

 

DBMS_ADVISOR

데이터베이스 구성요소와 관련된 성능 문제를 식별하고 해결하는데 도움이 되는 패키지입니다.

https://docs.oracle.com/database/121/ARPLS/d_advis.htm#ARPLS65084

DBMS_ADVISOR

사용방법은 아래와 같습니다.

Task 생성 > Task에 Object 정보생성 > Task 파라미터 설정 > Task 수행 > 결과값 확인 >Task 삭제

 

Task 생성

BEGIN
DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor', --## advisor 이름 (구별가능한 이름으로 작성)
    task_name         => 'Reorg Task'); --## **중요 Task 이름 작성
END;
/

 

Task에 Object 정보생성

declare
  l_object_id     NUMBER;
begin
DBMS_ADVISOR.create_object (
    task_name   => 'Reorg Task',
    object_type => 'TABLE', --##TABLE,TABLESPACE,INDEX 중 선택
    attr1       => 'DEV', --## TABLE일 경우 OWNER , TABLESPACE의 경우 TABLESPACE 이름
    attr2       => 'ORD_ITEM_RANGE2', --## TABLE일 경우 테이블명, TABLESPACE의 경우 NULL
    attr3       => NULL,
    attr4       => null,
    attr5       => NULL,
    object_id   => l_object_id);
end;
/

 

Task 파라미터 설정

begin
    DBMS_ADVISOR.set_task_parameter (
    task_name => 'Reorg Task', --##Task 이름
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');
end;
/

 

Task 수행

begin
DBMS_ADVISOR.execute_task(task_name =>'Reorg Task');
end;
/

 

결과값 확인

select * from dba_advisor_findings where task_name='Reorg Task';

DEV	42372	Reorg Task		1		PROBLEM	1	1	1			객체의 사용 가능 공간이 10MB 미만입니다.	할당된 공간: 177799168, 사용된 공간: 176184921, 확보 가능한 공간: 1614247	N	

SELECT * FROM dba_advisor_tasks where task_name='Reorg Task';

DEV	42372	Reorg Task		Segment Advisor	2023/02/22 15:25:47	2023/02/22 15:25:56	0	0	EXEC_43816				2023/02/22 15:25:56	2023/02/22 15:25:56	COMPLETED		0	0		0	0		PRIMARY	CMD	FALSE	FALSE	5	3

select * from dbms_space.asa_recommendations();

 

Task 삭제

begin
DBMS_ADVISOR.delete_task(task_name =>'Reorg Task');
end;
/

 

사용법은 이렇게 사용하면 리오그가 필요하다면 명령어(alter table shrink space) 와 확보 바이트를 알려주게 됩니다.

 

테스트

 

시나리오

테스트 테이블 생성 > 데이터 300만건 삽입 > 정보 확인 > 삭제,삽입 반복 (3회) > 정보 확인 > 리오그 > 정보확인

 

테스트 테이블 생성

DROP TABLE HWMTEST1 PURGE;

CREATE TABLE HWMTEST(A VARCHAR2(20), B NUMBER, C NUMBER,D VARCHAR2(30), E VARCHAR2(30));

 CREATE INDEX IDX_HWM_B ON HWMTEST(B);

 

데이터 300만건 삽입

DECLARE

TYPE tbl_ins IS TABLE OF HWMTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 1..1000000 LOOP

   w_ins(i).A :=dbms_random.string('x',10);

   w_ins(i).B :=i;

   w_ins(i).C :=99;

   w_ins(i).D :='ABC'||dbms_random.string('x',10);

   w_ins(i).E :='EEEEEEEEEEEEEEEE';

END LOOP;

   FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i);

   COMMIT;

   FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i);

   COMMIT;

   FORALL i in 1..1000000 INSERT INTO HWMTEST VALUES w_ins(i);

   COMMIT;

END;
/

select count(*) from HWMTEST
3000000

 

정보 확인

 

## 블록수 확인
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST;

USED
-----
22724

## DBMS_SPACE 결과 확인
SQL>  FS1 Blocks = 1 Bytes = 8192
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 124 Bytes = 1015808
Full Blocks = 22723 Bytes = 186146816

## DBMS_ADVISOR 확인
TABLE	DEV	HWMTEST	객체의 사용 가능 공간이 10MB 미만입니다.	
할당된 공간: 195035136, 사용된 공간: 185285806, 확보 가능한 공간: 9749330

##통계정보 수집 후 크기 및 딕셔너리뷰 블록 수 확인
exec dbms_stats.gather_table_stats('DEV','HWMTEST');

SQL> select segment_name,bytes/1024/1024 as MB from dba_segments where segment_name='HWMTEST';

SEGMENT_NAME    MB        
--------------- --------- 
HWMTEST               186

1 rows selected.

SQL> select table_name,num_rows,blocks,(blocks*8192)/1024/1024 as MB from dba_tables 
where table_name='HWMTEST';

TABLE_NAME     NUM_ROWS  BLOCKS    MB        
-------------- --------- --------- --------- 
HWMTEST          3000000     23612 184.46875

1 rows selected.

 

 삭제,삽입 반복 (3회)

delete HWMTEST where mod(b,3) = 0;
commit;

insert into HWMTEST
select dbms_random.string('x',10), level ,88,'DELETE AFTER INSERT (FIRST)','YYYYYYYYYYYYYYYYYYYYYYY'
from dual connect by level <= 1000000; 
commit;
--
delete HWMTEST where mod(b,3) = 1;
commit;

insert into HWMTEST
select dbms_random.string('x',10), level ,88,'DELETE AFTER INSERT (SECOND)','YYYYYYYYYYYYYYYYYYYYYYY'
from dual connect by level <= 1000000; 
COMMIT;
--
delete HWMTEST where mod(b,3) = 2;
commit;

insert into HWMTEST
select dbms_random.string('x',10), level ,88,'DELETE AFTER INSERT (THRID)','YYYYYYYYYYYYYYYYYYYYYYY'
from dual connect by level <= 1000000; 
COMMIT;

 

 정보 확인

##건수
select count(*) from hwmtest;
2000000

##블록수
SELECT COUNT(DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM HWMTEST;

26928

##딕셔너리뷰 확인
SQL> select table_name,num_rows,blocks,(blocks*8192)/1024/1024 as MB from dba_tables 
where table_name='HWMTEST';

TABLE_NAME    NUM_ROWS  BLOCKS    MB        
------------- --------- --------- --------- 
HWMTEST         2000000     27692 216.34375

1 rows selected.

SQL> select segment_name,bytes/1024/1024 as MB from dba_segments where segment_name='HWMTEST';

SEGMENT_NAME   MB        
-------------- --------- 
HWMTEST              218

1 rows selected.


##DBMS_SPACE 확인
SQL>  FS1 Blocks = 0 Bytes = 0
 FS2 Blocks = 3824 Bytes = 31326208
 FS3 Blocks = 2730 Bytes = 22364160
 FS4 Blocks = 2428 Bytes = 19890176
Full Blocks = 17946 Bytes = 147013632

##DBMS_ADVISOR 확인

TABLE	DEV	HWMTEST	DEV.HWMTEST 테이블의 행 이동을 사용으로 설정하고 축소 작업을 수행하는 경우 예상되는 절약 공간은 43493667바이트입니다.	
할당된 공간: 228589568, 사용된 공간: 185095901, 확보 가능한 공간: 43493667

맨처음 삽입하고 확인했을때보다

DBMS_SPACE의 경우에는

FS2,FS3,FS4 블록수가 늘어났습니다. (0,0,124 > 3824,2730,2428) 즉, 리오그를 했을 때 8,982블록이 효과를 볼것이고 약 70.17MB 감소 효과가 있을 것으로 나타났습니다.

DBMS_ADVISOR의 경우에는

약 41.47MB 확보가능한 공간이라고 나타났습니다.

 

DBMS_SPACE의 경우에는 각 블록사용별로 나누어져서 보여주기 때문에 합은 제가 임의로 한것이어서 좀더 크게나온것 같습니다.

 

 

리오그

alter table "DEV"."HWMTEST" enable row movement ;

alter table "DEV"."HWMTEST" shrink space;

alter table "DEV"."HWMTEST" disable row movement ;

 

정보 확인

##DBMS_SPACE
SQL>  FS1 Blocks = 1 Bytes = 8192
 FS2 Blocks = 0 Bytes = 0
 FS3 Blocks = 0 Bytes = 0
 FS4 Blocks = 3 Bytes = 24576
Full Blocks = 21506
   Bytes = 176177152
   

##DBMS_ADVISOR
TABLE	DEV	HWMTEST	객체의 사용 가능 공간이 10MB 미만입니다.	
할당된 공간: 177799168, 사용된 공간: 176184921, 확보 가능한 공간: 1614247


exec dbms_stats.gather_table_stats('DEV','HWMTEST');

select table_name,num_rows,blocks,(blocks*8192)/1024/1024 as MB from dba_tables 
where table_name='HWMTEST';

TABLE_NAME      NUM_ROWS  BLOCKS    MB        
--------------- --------- --------- --------- 
HWMTEST           2000000     21510  168.046875
;


SQL> select segment_name,bytes/1024/1024 as MB from dba_segments where segment_name='HWMTEST';

SEGMENT_NAME        MB        
------------------- --------- 
HWMTEST              169.5625
1 rows selected.

리오그 후에 정보를 확인해보면

DBMS_SPACE의 경우에는 FS2,FS3,FS4블록이 0,0,3으로 갯수가 엄청 줄어든 것을 확인할 수 있습니다.

총 block 수도 딕셔너리 뷰(dba_tables)의 블록수와 거의 비슷한 결과값을 보여줍니다.

DBMS_ADVISOR의 경우에는 bytes 로 확인해야 하는데, 할당된 공간이 약 168.02MB로 딕셔너리 뷰의 MB와 비슷한 결과값을 보여줍니다.

 

결론적으로 두 방법 다 리오그시 사용하기 좋은 빌트인 패키지인것 같습니다.