rebalance 시간은 움직이는 데이터량에 비례한다
compact 단계는 memory disk 환경에서는 불필요하므로 비활성
현재 사용중인 데이터 사이즈 및 compact 옵션별 add disk 시 rebalance 시간 테스트 진행
6GB 데이터, 20GB disk 1개씩 추가, compact 포함
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 1| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 0| 0| 0|
0| 5| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
0| 4| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 0| 0| 0|
0| 0| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 0| 0| 0|
0| 6| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 3| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 0| 0| 0|
1| 0|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 6036| 14440|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
-- 20GB 첫번째, 15초
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_2' name DATA_0001;
Diskgroup altered.
Elapsed: 00:00:03.14
-- ASM alert.log
2024-10-12T13:18:22.351013+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:18:37.475556+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
-- 20GB 두번째, 15초
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_3' name DATA_0002;
Diskgroup altered.
Elapsed: 00:00:04.06
-- ASM alert.log
2024-10-12T13:22:07.605464+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:22:22.639772+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
-- 20G 세번째, 12초
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_4' name DATA_0003;
Diskgroup altered.
Elapsed: 00:00:03.17
-- ASM alert.log
2024-10-12T13:23:22.696914+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:23:34.721758+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
16GB 데이터, 20GB disk 1개씩 추가, compact 포함
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 1| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 0| 0| 0|
0| 5| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
0| 4| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 0| 0| 0|
0| 0| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 0| 0| 0|
0| 6| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 3| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 0| 0| 0|
1| 0|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 16280| 4196|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
10 row(s) fetched.
-- 20GB 첫번째, 51초
-- ASM alert.log
2024-10-12T13:36:20.340625+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:37:11.405273+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
-- 2번째 실행전 예측 수행
SQL> explain work set statement_id='add_disk' for alter diskgroup data add disk '/dev/oracleasm/disks/ASM20_3' ;
Explained.
Elapsed: 00:00:00.14
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
Session altered.
Elapsed: 00:00:00.00
SQL> select * from v$asm_estimate where statement_id='add_disk';
GROUP_NUMBER STATEMEN TIMESTAMP EST_WORK CON_ID
------------ -------- ------------------- ---------- ----------
1 add_disk 2024/10/12 13:39:25 1353 0
Elapsed: 00:00:00.00
-- 예측과 실제 일량은 동일하게 나타남
GROUP_NUMBER OPERA PASS STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
1 REBAL COMPACT WAIT 1 1 0 0 0 0 0
1 REBAL REBALANCE RUN 1 1 174 1353 198899 0 0
1 REBAL REBUILD DONE 1 1 0 0 0 0 0
-- 20GB 두번째, 39초
-- ASM alert.log
2024-10-12T13:40:23.512565+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:41:02.626029+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
-- 20GB 세번째, 30초
-- ASM alert.log
2024-10-12T13:42:59.693047+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:43:29.752539+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)반응형
compact 단계 비활성
-- 현재 설정 조회
SQL> select GROUP_KFENV,NAME_KFENV,VALUE_KFENV from x$kfenv where NAME_KFENV like '%compact%';
GROUP_KFENV NAME_KFENV VALUE_KFENV
----------- ------------------------------------------------------------------------------------------------------------ ------------------
1 _rebalance_compact TRUE
2 _rebalance_compact TRUE
Elapsed: 00:00:00.01
-- DATA diskgroup compact 비활성
SQL> alter diskgroup DATA set attribute '_rebalance_compact'='FALSE' ;
Diskgroup altered.
Elapsed: 00:00:00.00
-- 설정값 조회
SQL> select GROUP_KFENV,NAME_KFENV,VALUE_KFENV from x$kfenv where NAME_KFENV like '%compact%';
GROUP_KFENV NAME_KFENV VALUE_KFENV
----------- ------------------------------------------------------------------------------------------------------------ ------------------
1 _rebalance_compact FALSE
2 _rebalance_compact TRUE
Elapsed: 00:00:00.01
-- 재 테스트를 위한 disk drop
SQL> alter diskgroup DATA drop disk 'DATA_0001','DATA_0002','DATA_0003';
Diskgroup altered.
Elapsed: 00:00:00.02
-- ASM alert.log
2024-10-12T13:44:44.805587+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:45:41.845081+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
16GB 데이터, 20GB disk 1개씩 추가, compact 단계 비활성
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 1| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 0| 0| 0|
0| 5| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
0| 4| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 0| 0| 0|
0| 0| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 0| 0| 0|
0| 6| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 3| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 0| 0| 0|
1| 0|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 16800| 3676|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
10 row(s) fetched.
-- 20GB 첫번째, 39초
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_2' name DATA_0001;
Diskgroup altered.
Elapsed: 00:00:02.79
-- ASM alert.log
2024-10-12T13:48:15.058271+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:48:54.128915+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
-- 20GB 두번째, 27초
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_3' name DATA_0002;
Diskgroup altered.
Elapsed: 00:00:04.50
-- ASM alert.log
2024-10-12T13:50:21.208147+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:50:48.282644+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
-- 20GB 세번째, 21초
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_4' name DATA_0003;
Diskgroup altered.
Elapsed: 00:00:05.67
-- ASM alert.log
2024-10-12T13:51:51.332138+09:00
NOTE: starting rebalance of group 1/0x1d20f8cf (DATA) at power 1
...
2024-10-12T13:52:12.371915+09:00
SUCCESS: rebalance completed for group 1/0x1d20f8cf (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 3| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
0| 1| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 0| 0| 0|
0| 0| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
1| 0|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 4216| 16260|
1| 1|DATA_0001|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 20476| 4212| 16264|
1| 2|DATA_0002|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 20476| 4212| 16264|
1| 3|DATA_0003|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 20476| 4196| 16280|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
10 row(s) fetched.
16GB 데이터, 20GB disk 3개 한번에 추가
compact 포함
-- compact 포함
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 1| |CLOSED |PROVISIONED |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 0| 0| 0|
0| 5| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
0| 4| |CLOSED |PROVISIONED |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 0| 0| 0|
0| 0| |CLOSED |PROVISIONED |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 0| 0| 0|
0| 6| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 3| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 0| 0| 0|
1| 4|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 16400| 4076|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
-- 20GB 3개 한번에 추가
alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_2' name data_0002
, '/dev/oracleasm/disks/ASM20_3' name data_0003
, '/dev/oracleasm/disks/ASM20_4' name data_0004
;
-- 1분57초
2024-10-12T17:41:38.232247+09:00
NOTE: starting rebalance of group 1/0x5650c44b (DATA) at power 1
...
2024-10-12T17:43:35.339876+09:00
SUCCESS: rebalance completed for group 1/0x5650c44b (DATA)
-- drop disk
SQL> alter diskgroup DATA drop disk 'DATA_0004','DATA_0002','DATA_0003';
Diskgroup altered.
-- 1분
2024-10-12T17:46:53.440598+09:00
NOTE: starting rebalance of group 1/0x5650c44b (DATA) at power 1
...
2024-10-12T17:47:53.469227+09:00
SUCCESS: rebalance completed for group 1/0x5650c44b (DATA)
compact 비활성
-- compact 비활성
-- 20GB 3개 한번에 추가
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM20_2' name data_0001
, '/dev/oracleasm/disks/ASM20_3' name data_0002
, '/dev/oracleasm/disks/ASM20_4' name data_0003
;
Diskgroup altered.
-- 1분
2024-10-12T17:48:59.530277+09:00
NOTE: starting rebalance of group 1/0x5650c44b (DATA) at power 1
...
2024-10-12T17:49:59.669091+09:00
SUCCESS: rebalance completed for group 1/0x5650c44b (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
16GB 데이터, 5GB disk 추가, compact 비활성
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 3| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
0| 0| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 1| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 0| 0| 0|
1| 4|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 4208| 16268|
1| 0|DATA_0001|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 20476| 4208| 16268|
1| 1|DATA_0002|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 20476| 4208| 16268|
1| 2|DATA_0003|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 20476| 4204| 16272|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
10 row(s) fetched.
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASM1' ;
Diskgroup altered.
-- 12초
2024-10-12T18:05:48.282507+09:00
NOTE: starting rebalance of group 1/0x5650c44b (DATA) at power 1
...
2024-10-12T18:06:00.713735+09:00
SUCCESS: rebalance completed for group 1/0x5650c44b (DATA)
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
> select group_number, disk_number, name, mount_status, header_status, state, path, os_mb, total_mb, cold_used_mb, free_mb
from v$asm_disk
WHERE 1=1
order by 1,3
GROUP_NUMBER|DISK_NUMBER|NAME |MOUNT_STATUS|HEADER_STATUS|STATE |PATH |OS_MB|TOTAL_MB|COLD_USED_MB|FREE_MB|
------------+-----------+---------+------------+-------------+------+----------------------------+-----+--------+------------+-------+
0| 1| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM01 | 1023| 0| 0| 0|
0| 0| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM03 | 1023| 0| 0| 0|
0| 2| |CLOSED |FORMER |NORMAL|/dev/oracleasm/disks/ASM02 | 1023| 0| 0| 0|
1| 3|ASM1 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM1 | 5119| 5116| 1000| 4116|
1| 4|DATA_0000|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_1|20479| 20476| 3960| 16516|
1| 0|DATA_0001|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_2|20479| 20476| 3956| 16520|
1| 1|DATA_0002|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_3|20479| 20476| 3960| 16516|
1| 2|DATA_0003|CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/ASM20_4|20479| 20476| 3964| 16512|
2| 0|OCR_0000 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR1 | 5119| 5116| 204| 4912|
2| 1|OCR_0001 |CACHED |MEMBER |NORMAL|/dev/oracleasm/disks/OCR2 | 5119| 5116| 176| 4940|
10 row(s) fetched.'DBMS > ORACLE' 카테고리의 다른 글
| [oracle] 테이블 nologging 모드여도 특정 상황에서만 로깅함 (로그마이너) (0) | 2024.10.18 |
|---|---|
| [oracle] 계정 만료(expired) 시 account_status 상태 변경 시점 (1) | 2024.09.26 |
| [oracle] WITH GRANT OPTION 회수 (1) | 2024.09.06 |
| [oracle] RU 패치 적용/롤백 시 기존 interim 패치 상태는? (0) | 2024.08.29 |
| [oracle] ORA-27300 에러 발생 status: 105 (0) | 2024.07.26 |
댓글