반응형
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 포함
-- 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 비활성
데이터 이동량이 20GB 추가 시 보다 적어 빨리 끝남
> 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] 최대 접속 세션 수 변경 시 processes, sessions 파라미터 테스트 (0) | 2025.03.19 |
|---|---|
| [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 |
댓글