はじめに
前回、DBMS_REDEFINITION を使用して DML を止めずにテーブルレイアウトを変更する方法をご紹介しましたが、今回はベンチマークツールの Swingbench を使ってよりリアルな状況でテーブルレイアウトを変更してみます。
Swingbench の設定
下記の要領でベンチマーク用データを作成しました。
[opc@gp labs]$ cat 1install.sh
#!/bin/sh
./swingbench/bin/oewizard \
-cf xx.zip \
-cs xx \
-ts DATA \
-dbap xx \
-dba xx \
-u soe \
-p xx \
-async_off \
-scale 1 \
-hashpart \
-create \
-cl \
-v
[opc@gp labs]$ ./1install.sh
SwingBench Wizard
Author : Dominic Giles
Version : 2.7.0.1561
Running in Lights Out Mode using config file : ../wizardconfigs/oewizard.xml
Running script ../sql/orderentry/soedgcreateuser.sql
(snip)
Data Generation Runtime Metrics
+-------------------------+-------------+
| Description | Value |
+-------------------------+-------------+
| Connection Time | 0:00:00.001 |
| Data Generation Time | 0:09:52.254 |
| DDL Creation Time | 0:03:07.593 |
| Total Run Time | 0:12:59.853 |
| Rows Inserted per sec | 26,787 |
| Actual Rows Generated | 15,878,121 |
| Commits Completed | 810 |
| Batch Updates Completed | 79,405 |
+-------------------------+-------------+
Validation Report
The schema appears to have been created successfully.
Valid Objects
Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADN','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS'
Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPP_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESILS_CUST_IX'
Valid Views : 'PRODUCTS','PRODUCT_PRICES'
Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DE
Valid Code : 'ORDERENTRY'
Schema Created
[opc@gp labs]$
[opc@gp labs]$ ./2-1check.sh
The Order Entry Schema appears to be valid.
--------------------------------------------------
|Object Type | Valid| Invalid| Missing|
--------------------------------------------------
|Table | 10| 0| 0|
|Index | 26| 0| 0|
|Sequence | 5| 0| 0|
|View | 2| 0| 0|
|Code | 1| 0| 0|
--------------------------------------------------
Collecting statistics for the schema
Collected statistics in : 0:00:16.142
Order Entry Schemas Tables
+----------------------+-----------+--------+---------+-------------+--------------+
| Table Name | Rows | Blocks | Size | Compressed? | Partitioned? |
+----------------------+-----------+--------+---------+-------------+--------------+
| ORDER_ITEMS | 7,158,785 | 96,704 | 768.0MB | | Yes |
| CARD_DETAILS | 1,500,000 | 32,192 | 256.0MB | | Yes |
| LOGON | 2,382,984 | 32,192 | 256.0MB | | Yes |
| ADDRESSES | 1,500,000 | 32,192 | 256.0MB | | Yes |
| ORDERS | 1,429,790 | 32,192 | 256.0MB | | Yes |
| CUSTOMERS | 1,000,000 | 32,192 | 256.0MB | | Yes |
| INVENTORIES | 903,562 | 2,512 | 20.0MB | Disabled | No |
| PRODUCT_DESCRIPTIONS | 1,000 | 35 | 320KB | Disabled | No |
| PRODUCT_INFORMATION | 1,000 | 28 | 256KB | Disabled | No |
| ORDERENTRY_METADATA | 4 | 5 | 64KB | Disabled | No |
| WAREHOUSES | 1,000 | 5 | 64KB | Disabled | No |
+----------------------+-----------+--------+---------+-------------+--------------+
Total Space 2.0GB
[opc@gp labs]$
ワークロードは下記の要領で動かします。
[opc@gp labs]$ cat 3execute.sh
#!/bin/sh
./swingbench/bin/charbench -c ~/labs/swingbench/configs/SOE_Server_Side_V2.xml \
-cf xx.zip \
-cs xx -u soe -p xx \
-v users,tpm,tps,vresp \
-intermin 50 \
-intermax 200 \
-min 0 \
-max 0 \
-uc 10 \
-di SQ,WQ,WA
[opc@gp labs]$
仮表の作成
今回、ワークロードを実行しながらレイアウトを変更するのはcustomersです。
(仮表：customers_int)
04:42:15 SQL> info soe.customers
TABLE: CUSTOMERS
LAST ANALYZED:2026-08-25 04:56:42.0
ROWS :1000000
SAMPLE SIZE :1000000
INMEMORY :
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*CUSTOMER_ID NUMBER(12,0) No
CUST_FIRST_NAME VARCHAR2(40 BYTE) No
CUST_LAST_NAME VARCHAR2(40 BYTE) No
NLS_LANGUAGE VARCHAR2(3 BYTE) Yes
NLS_TERRITORY VARCHAR2(90 BYTE) Yes
CREDIT_LIMIT NUMBER(9,2) Yes
CUST_EMAIL VARCHAR2(100 BYTE) Yes
ACCOUNT_MGR_ID NUMBER(12,0) Yes
CUSTOMER_SINCE DATE Yes
CUSTOMER_CLASS VARCHAR2(40 BYTE) Yes
SUGGESTIONS VARCHAR2(40 BYTE) Yes
DOB DATE Yes
MAILSHOT VARCHAR2(1 BYTE) Yes
PARTNER_MAILSHOT VARCHAR2(1 BYTE) Yes
PREFERRED_ADDRESS NUMBER(12,0) Yes
PREFERRED_CARD NUMBER(12,0) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
______________________________ _____________ _________ _________________ _____________________________
SOE.CUST_DOB_IX NONUNIQUE VALID DOB
SOE.CUSTOMERS_PK UNIQUE VALID CUSTOMER_ID
SOE.CUST_EMAIL_IX NONUNIQUE VALID CUST_EMAIL
SOE.CUST_ACCOUNT_MANAGER_IX NONUNIQUE VALID ACCOUNT_MGR_ID
SOE.CUST_FUNC_LOWER_NAME_IX NONUNIQUE VALID ENABLED SYS_NC00017$, SYS_NC00018$
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
_____________ ________________________ ______________ __________ _________________ ________________ ____________
ADDRESSES ADD_CUST_FK NO ACTION ENABLED DEFERRABLE NOT VALIDATED USER NAME
ORDERS ORDERS_CUSTOMER_ID_FK SET NULL ENABLED NOT DEFERRABLE NOT VALIDATED USER NAME
04:57:58 SQL>
04:57:58 SQL>
04:58:12 SQL> info soe.customers_int
TABLE: CUSTOMERS_INT
LAST ANALYZED:
ROWS :
SAMPLE SIZE :
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
CUSTOMER_ID NUMBER(12,0) Yes
CUST_FIRST_NAME VARCHAR2(40 BYTE) Yes
CUST_LAST_NAME VARCHAR2(40 BYTE) Yes
COL_INT DATE Yes ★追加カラム
NLS_LANGUAGE VARCHAR2(3 BYTE) Yes
NLS_TERRITORY VARCHAR2(90 BYTE) Yes
CREDIT_LIMIT NUMBER(9,2) Yes
CUST_EMAIL VARCHAR2(100 BYTE) Yes
ACCOUNT_MGR_ID NUMBER(12,0) Yes
CUSTOMER_SINCE DATE Yes
CUSTOMER_CLASS VARCHAR2(40 BYTE) Yes
SUGGESTIONS VARCHAR2(40 BYTE) Yes
DOB DATE Yes
MAILSHOT VARCHAR2(1 BYTE) Yes
PARTNER_MAILSHOT VARCHAR2(1 BYTE) Yes
PREFERRED_ADDRESS NUMBER(12,0) Yes
PREFERRED_CARD NUMBER(12,0) Yes
04:58:21 SQL>
ワークロード実行＆テーブル再定義開始
ワークロードを実行し、テーブル再定義を開始します。
[opc@gp labs]$
[opc@gp labs]$ ./3execute.sh
Swingbench
Author : Dominic Giles
Version : 2.7.0.1561
Results will be written to results.xml
Hit Return to Terminate Run...
Time Users TPM TPS NCR UCD BP OP PO BO SQ WQ WA
04:59:47 [0/10] 0 0 0 0 0 0 0 0 0 0 0
04:59:49 [10/10] 0 0 0 0 0 0 0 0 0 0 0
(snip)
04:58:21 SQL> --テーブル再定義開始
04:58:21 SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'soe',
4 orig_table => 'customers',
5 int_table => 'customers_int');
6 END;
7* /
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.661
05:00:39 SQL>
05:00:39 SQL> select count(1) from soe.customers;
COUNT(1)
___________
1000607
Elapsed: 00:00:00.039
05:00:51 SQL> select count(1) from soe.customers_int;
COUNT(1)
___________
1000273
Elapsed: 00:00:00.530
05:00:55 SQL>
05:00:55 SQL> --依存オブジェクトのクローン
05:00:55 SQL> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => 'soe',
6 orig_table => 'customers',
7 int_table => 'customers_int',
8 num_errors => num_errors);
9 END;
10* /
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.398
05:01:18 SQL>
05:01:18 SQL>
05:01:18 SQL>
05:01:18 SQL> info soe.customers_int
TABLE: CUSTOMERS_INT
LAST ANALYZED:2026-08-25 05:00:39.0
ROWS :1000273
SAMPLE SIZE :1000273
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*CUSTOMER_ID NUMBER(12,0) Yes
CUST_FIRST_NAME VARCHAR2(40 BYTE) Yes
CUST_LAST_NAME VARCHAR2(40 BYTE) Yes
COL_INT DATE Yes
NLS_LANGUAGE VARCHAR2(3 BYTE) Yes
NLS_TERRITORY VARCHAR2(90 BYTE) Yes
CREDIT_LIMIT NUMBER(9,2) Yes
CUST_EMAIL VARCHAR2(100 BYTE) Yes
ACCOUNT_MGR_ID NUMBER(12,0) Yes
CUSTOMER_SINCE DATE Yes
CUSTOMER_CLASS VARCHAR2(40 BYTE) Yes
SUGGESTIONS VARCHAR2(40 BYTE) Yes
DOB DATE Yes
MAILSHOT VARCHAR2(1 BYTE) Yes
PARTNER_MAILSHOT VARCHAR2(1 BYTE) Yes
PREFERRED_ADDRESS NUMBER(12,0) Yes
PREFERRED_CARD NUMBER(12,0) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
_____________________________________ _____________ _________ _________________ _____________________________
SOE.TMP$$_CUST_DOB_IX0 NONUNIQUE VALID DOB
SOE.TMP$$_CUSTOMERS_PK0 UNIQUE VALID CUSTOMER_ID
SOE.TMP$$_CUST_EMAIL_IX0 NONUNIQUE VALID CUST_EMAIL
SOE.TMP$$_CUST_ACCOUNT_MANAGER_IX0 NONUNIQUE VALID ACCOUNT_MGR_ID
SOE.TMP$$_CUST_FUNC_LOWER_NAME_IX0 NONUNIQUE VALID ENABLED SYS_NC00018$, SYS_NC00019$
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
_____________ _______________________________ ______________ ___________ _________________ ________________ ____________
ADDRESSES TMP$$_ADD_CUST_FK0 NO ACTION DISABLED DEFERRABLE NOT VALIDATED USER NAME
ORDERS TMP$$_ORDERS_CUSTOMER_ID_FK0 SET NULL DISABLED NOT DEFERRABLE NOT VALIDATED USER NAME
05:01:27 SQL>
05:01:27 SQL> --テーブル再定義完了
05:01:27 SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'soe',
4 orig_table => 'customers',
5 int_table => 'customers_int');
6 END;
7* /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.255
05:01:35 SQL>
05:01:35 SQL>
05:01:35 SQL> info soe.customers;
TABLE: CUSTOMERS
LAST ANALYZED:2026-08-25 05:00:39.0
ROWS :1000273
SAMPLE SIZE :1000273
INMEMORY :DISABLED
COMMENTS :
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*CUSTOMER_ID NUMBER(12,0) Yes
CUST_FIRST_NAME VARCHAR2(40 BYTE) No
CUST_LAST_NAME VARCHAR2(40 BYTE) No
COL_INT DATE Yes ★追加カラム
NLS_LANGUAGE VARCHAR2(3 BYTE) Yes
NLS_TERRITORY VARCHAR2(90 BYTE) Yes
CREDIT_LIMIT NUMBER(9,2) Yes
CUST_EMAIL VARCHAR2(100 BYTE) Yes
ACCOUNT_MGR_ID NUMBER(12,0) Yes
CUSTOMER_SINCE DATE Yes
CUSTOMER_CLASS VARCHAR2(40 BYTE) Yes
SUGGESTIONS VARCHAR2(40 BYTE) Yes
DOB DATE Yes
MAILSHOT VARCHAR2(1 BYTE) Yes
PARTNER_MAILSHOT VARCHAR2(1 BYTE) Yes
PREFERRED_ADDRESS NUMBER(12,0) Yes
PREFERRED_CARD NUMBER(12,0) Yes
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS
______________________________ _____________ _________ _________________ _____________________________
SOE.CUST_DOB_IX NONUNIQUE VALID DOB
SOE.CUSTOMERS_PK UNIQUE VALID CUSTOMER_ID
SOE.CUST_EMAIL_IX NONUNIQUE VALID CUST_EMAIL
SOE.CUST_ACCOUNT_MANAGER_IX NONUNIQUE VALID ACCOUNT_MGR_ID
SOE.CUST_FUNC_LOWER_NAME_IX NONUNIQUE VALID ENABLED SYS_NC00018$, SYS_NC00019$
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
_____________ ________________________ ______________ __________ _________________ ____________ ____________
ADDRESSES ADD_CUST_FK NO ACTION ENABLED DEFERRABLE VALIDATED USER NAME
ORDERS ORDERS_CUSTOMER_ID_FK SET NULL ENABLED NOT DEFERRABLE VALIDATED USER NAME
05:01:47 SQL>
05:01:47 SQL>
05:01:47 SQL>
結果
TPS と DBMS_REDEFINITION の実行期間をグラフにすると下表のようになります。(DeWitt条項があるため、念のため具体的な数値は割愛)
DBMS_REDEFINITION の実行中も TPS は顕著に劣化していないこと、処理を継続できていることがわかります。
