Shulou(Shulou.com)05/31 Report--

This article mainly introduces "analyzing whether Oracle can complete the field type change of table structure online within 2 hours". In the daily operation, I believe many people have doubts about whether Oracle can complete the field type change of table structure online within 2 hours. The editor consulted all kinds of data and sorted out a simple and useful operation method. I hope it will be helpful to answer the question of "analyzing whether Oracle can complete the field type change of table structure online within 2 hours". Next, please follow the editor to study!

Summary: can Oracle 12c change the table structure field type of a 1.4 billion records online within 2 hours?

Overview

Previously shared the script for online modification of Oracle table (online redefinition), after several rounds of testing found that there are some defects, the efficiency is not very satisfactory. After taking out the index and statistics this time, it is found that the efficiency is relatively the highest.

The goal of online redefinition is to minimize the impact on online business, to change the table structure through the shortest locking time, which only occurs in the finish_redef_table process. Before formal switching, the sync_interim_table process is executed to synchronize data asynchronously to minimize business impact.

Because it is to change the core business of the customer, according to the management requirements, there is no way to implement the pre-finish_redef_table process in advance, and the maintenance window time is limited, and the business can not be completely stopped.

Two common scenarios are mainly tested, as follows:

Scenario 1:

Replication all depends on-COPY_TABLE_DEPENDENTS (index + constraint + statistics). Without triggers and permissions, there is no replication.

Advantages: easy to operate, the script directly copies all the dependencies of the original table, the modified table is used directly, does not require additional processing, is suitable for millions or tens of millions of tables, and does not require efficiency to be available.

Cons: hundreds of millions of table tests are found to be very inefficient.

Scenario 2:

Tables with primary keys only copy constraints-COPY_TABLE_DEPENDENTS (replicate primary keys and unique indexes), and then start parallel reconstruction and collection after other indexes and statistics have been redefined. Here, we need to add why it is necessary to copy constraints, because the creation of primary keys cannot be operated in parallel. After data conversion is completed, redefinition is equivalent to creating primary keys on ordinary large tables, which is very inefficient.

Advantages: at present, more than 1 billion of the meters are found to be the most efficient, and 1.4 billion of the meters are finished in about 2 hours.

Disadvantages: the operation process is a little more troublesome, and everything else is fine.

Copy the rules as follows:

Copy_indexes = > 0 copywriters = > FALSE,copy_constraints = > TRUE,copy_privileges = > FALSE,ignore_errors = > FALSE,num_errors = > num_errors,copy_statistics = > FALSE). As the efficiency of scenario 1 is relatively low, I will only list the test process of scenario 2, and the subsequent actual business changes are also carried out in scenario 2. The following is the whole change process:

The hardware and software configurations are general, as follows:

CPU:Intel ®Xeon ®CPU E7-4820 v3 @ 1.90GHz (physical 4, 10 cores, 80 logical cpu)

Memory: 500 GB

Storage: a certain model of Huawei

Database software: Oracle 12.2 Nocdb RAC, unpatched.

Let's first take a look at the number of rows in the original table, which is close to 1.4 billion, artificial, and the actual size of the table is more than twice as large as that of production.

SQL > select / * + parallel (40) * / count (*) from OM_OFFERING_INST_TEST; COUNT (*)-1399999996Elapsed: 00lv 0017.39

Create a temporary table with 35 partitions, some of which are omitted, and do not build primary keys, indexes, etc.

CREATE TABLE "CUSTINFO". "INT_OM_OFFERING_INST_TEST" ("BUSINESS_SEQ" VARCHAR2 (20), "PROD_ID" NUMBER (20,0), "OFFERING_INST_ID" NUMBER (20,0), "OFFERING_ID" NUMBER (20,0), "OFFERING_NAME" VARCHAR2 (256) "OFFERING_CODE" VARCHAR2 (50), "CUST_TYPE" VARCHAR2 (20), "CUST_ID" NUMBER (20), "BRAND" VARCHAR2 (50). "RECORD_STATUS" NUMBER (3,0) DEFAULT 1) PARTITION BY LIST ("BE_ID") (PARTITION "Prun000" VALUES ('000'), PARTITION "Prun001" VALUES (' 001'), PARTITION "Prun002" VALUES ('002'), PARTITION "Prun100" VALUES (' 100'), PARTITION "Prun200" VALUES ('200') .

Define parameters, set parallelism and row migration

Define USERNAME = 'CUSTINFO';-- user name define SOURCE_TAB =' OM_OFFERING_INST_TEST';-- original table name define INT_TAB = 'INT_OM_OFFERING_INST_TEST';-- temporary table name, which needs to be created manually in advance define PARALLELS = 35;-- parallel number, the number of partitions set here alter session enable parallel dml; alter session force parallel dml parallel & PARALLELS;alter session force parallel query parallel & PARALLELS;alter table & INT_TAB enable row movement;-- temporary table enables row migration

It takes less than 1 second to check whether the original table supports online redefinition.

SQL > begin 2 dbms_redefinition.can_redef_table (uname = >'& USERNAME', 3 tname = >'& SOURCE_TAB', 4 options_flag = > DBMS_REDEFINITION.CONS_USE_PK); 5 end; 6 / PL/SQL procedure successfully completedExecuted in 0.027 seconds

It took nearly 10 minutes to map the field type and start the redefinition process, which is a little slow. From here to the end, if there is a mistake, you need to call the abort_redef_table process to cancel the task if you want to start all over again.

SQL > set timing on SQL > begin 2 DBMS_REDEFINITION.START_REDEF_TABLE (uname = >'& USERNAME', 3 orig_table = >'& SOURCE_TAB', 4 int_table = >'& INT_TAB' 5 col_mapping = > 'to_number (owner_party_role_id) owner_party_role_id, 7 to_number (offering_inst_id) offering_inst_id, 8 to_number (subs_id) subs_id 9 to_number (group_id) group_id, 10 to_number (apply_obj_id) apply_obj_id' -- only the field types that need to be changed are listed here 11 options_flag = > DBMS_REDEFINITION.CONS_USE_PK) 12 end; 13 / PL/SQL procedure successfully completedExecuted in 576.565 seconds

Copy dependent objects, here only copy the primary key constraint, take 54 minutes, if all copy, I ran in the test for 3 hours with no results, only Kill.

SQL > DECLARE 2 num_errors PLS_INTEGER 3 BEGIN 4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname = >'& USERNAME', 5 orig_table = >'& SOURCE_TAB', 6 int_table = >'& INT_TAB' 7 copy_indexes = > 0, 8 copy_triggers = > FALSE, 9 copy_constraints = > TRUE, 10 copy_privileges = > FALSE 11 ignore_errors = > FALSE, 12 num_errors = > num_errors, 13 copy_statistics = > FALSE) 14 END; 15 / PL/SQL procedure successfully completedExecuted in 3230.441 seconds

Synchronous data asynchronously takes 28 seconds, which is relatively fast.

SQL > begin 2 dbms_redefinition.sync_interim_table (uname = >'& USERNAME', 3 orig_table = >'& SOURCE_TAB', 4 int_table = >'& INT_TAB'); 5 end; 6 / PL/SQL procedure successfully completedExecuted in 27.908 seconds

It takes 73 seconds to complete the online redefinition and finish the task, which is also faster.

SQL > begin 2 dbms_redefinition.finish_redef_table (uname = >'& USERNAME', 3 orig_table = >'& SOURCE_TAB', 4 int_table = >'& INT_TAB'); 5 end; 6 / PL/SQL procedure successfully completedExecuted in 72.302 seconds

To create an index, there are not many indexes on this partitioned table, just 3 ordinary indexes and 53 parallel indexes, each taking about 4 minutes on average, for a total of 13 minutes.

SQL > CREATE INDEX "CUSTINFO". "INX_OM_OFFERING_INST_TEST_CUSTID" ON "CUSTINFO". "OM_OFFERING_INST_TEST" ("CUST_ID") online parallel 35 ten index createdExecuted in 257.138 secondsSQL > CREATE INDEX "CUSTINFO". "INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO". "OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35 Index createdExecuted in 244.853 secondsSQL > CREATE INDEX "CUSTINFO". "INX_OM_OFFERING_INST_TEST_SUBSID" ON "CUSTINFO". "OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35 witch Index createdExecuted in 261.665 seconds

Collect statistics, also open 35 parallelism, take about 4 minutes. CASCADE = > true means to collect tables, columns, indexes, and so on.

SQL > exec dbms_stats.gather_table_stats (ownname = > 'CUSTINFO',tabname = >' OM_OFFERING_INST_TEST',CASCADE = > true,degree = > 35)

Cancel the parallelism on the table and index, check whether the field has been modified successfully, and delete the temporary table. At this point, the whole modification process is over, which takes about 10 minutes.

-- cancel the parallel alter table on the table & SOURCE_TAB noparallel;-- cancel the parallel alter index INX_OM_OFFERING_INST_TEST_CUSTID noparallel;alter index INX_OM_OFFERING_INST_TEST_GROUPID noparallel;alter index INX_OM_OFFERING_INST_TEST_SUBSID noparallel;-- delete temporary table drop table & INT_TAB on the index At this point, the study on "analyzing whether Oracle can complete the field type change of table structure online within 2 hours" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.