Data rollback （MySQL -》 Oracle）

 

 

<!--[if !supportLists]-->1.       <!--[endif]-->For the SQL  DEL_ORACLE_DATA. I think we can use truncate to replace delete. Because truncate is faster than delete.

We can update it to be:

public final static String DEL_ORACLE_DATA = "begin for cur in (select owner, table_name from all_tables where owner = ''{0}'') loop execute immediate ''truncate table ''||cur.owner||''.''||cur.table_name || '' drop storage '';end loop; end;";

 

<!--[if !supportLists]-->2.       <!--[endif]--> For the exception:

 at java.lang.OutOfMemoryError.<init>()V (OutOfMemoryError.java:25)

 at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(II)V (OraclePreparedStatement.java:2764)

 at oracle.jdbc.driver.OraclePreparedStatement.executeBatch()[I (OraclePreparedStatement.java:9314)

 at oracle.jdbc.driver.OracleStatementWrapper.executeBatch()[I (OracleStatementWrapper.java:211)

 

I thread dump when the tool throw out of  memory exception, and use the tool eclipse memory analyzer to analyze, found the inputStream use 73.51% memory and  lastboundChars use 25.18% memory. (you can see the bellow picture)

Inputstream is used to store the blob column, lastboundChars is used to bound chars. I found the size for array inputStream is 32768. It is a little strange, because my batch size is 20000.

 

I watch the source code, found  when the numberof bind rows allocated is less than the rows need,   numberOfBindRowsAllocated will be double, and growBinds.  For example, when the size is bigger than 16384, then numberOfBindRowsAllocated will be 32768.

So it is the reason why my batch size is 20000, but the inputstream array size is 32768.  In this case, I think we can set the size to be 16380 to fully use the memory.

 

      if (i2 >= this.numberOfBindRowsAllocated)

      {

        int i3 = this.numberOfBindRowsAllocated << 1;

 

        if (i3 <= i2) {

          i3 = i2 + 1;

        }

        growBinds(i3);

 

        this.currentBatchNeedToPrepareBinds = true;

      }

<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter" /> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0" /> <v:f eqn="sum @0 1 0" /> <v:f eqn="sum 0 0 @1" /> <v:f eqn="prod @2 1 2" /> <v:f eqn="prod @3 21600 pixelWidth" /> <v:f eqn="prod @3 21600 pixelHeight" /> <v:f eqn="sum @0 0 1" /> <v:f eqn="prod @6 1 2" /> <v:f eqn="prod @7 21600 pixelWidth" /> <v:f eqn="sum @8 21600 0" /> <v:f eqn="prod @7 21600 pixelHeight" /> <v:f eqn="sum @10 21600 0" /> </v:formulas> <v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect" /> <o:lock v:ext="edit" aspectratio="t" /> </v:shapetype><v:shape id="Picture_x0020_2" o:spid="_x0000_i1026" type="#_x0000_t75" alt="Description: Description: C:\Users\ffan\AppData\Roaming\Tencent\Users\407178982\QQ\WinTemp\RichOle\OQ}H])NEU)QVR$42E{EQ{8W.jpg" style='width:501.75pt;height:334.5pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///C:\Users\ffan\AppData\Local\Temp\msohtmlclip1\01\clip_image001.jpg" o:title="OQ}H])NEU)QVR$42E{EQ{8W" /> </v:shape><![endif]--><!--[if !vml]--><!--[endif]-->

<!--[if gte vml 1]><v:shape id="Picture_x0020_1" o:spid="_x0000_i1025" type="#_x0000_t75" alt="Description: Description: C:\Users\ffan\AppData\Roaming\Tencent\Users\407178982\QQ\WinTemp\RichOle\8EJ8VC(Z2K5)63UNKC]K3EX.jpg" style='width:546pt;height:379.5pt;visibility:visible;mso-wrap-style:square'> <v:imagedata src="file:///C:\Users\ffan\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg" o:title="8EJ8VC(Z2K5)63UNKC]K3EX" /> </v:shape><![endif]--><!--[if !vml]--><!--[endif]-->

<!--[if !supportLists]-->3.      <!--[endif]-->  I found in our tool, we do not disable the log. Also do not drop the index, after disable log and drop the index, the speed improved a lot.

 

public final static String DISABLE_ORACLE_LOG = "begin for cur in (select owner, table_name from all_tables where owner = ''{0}'') loop execute immediate ''ALTER table ''||cur.owner||''.''||cur.table_name || '' nologging '';end loop; end;";

public final static String DROP_ORACLE_INDEX = "begin for cur in (select owner, object_name from all_objects  where object_type=''INDEX'' and owner = ''{0}'') loop execute immediate '' drop index ''||cur.owner||''.''||cur.object_name;end loop; end;";

 

 

                Line 81: 2012-09-10 09:46:46,984 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] Dynamic SQL template in TABLE endpoint is: insert into endpoint (model,locale,hardware_id,brandid,trigger_port,type,num_triggers,version,codec6,id,noc_id,codec7,codec8,codec9,created,description,trigger_prefix,last_trigger_ts,os,nf_delivery_type,last_connection_date,vendor,msisdn,msisdn_validated,codec1,codec0,codec5,codec4,codec3,modified,device_uid,os_version,codec2,ip,extra_codecs_id,csc,provisioning_id,imsi,device_group,public_key,ekey) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

                Line 81: 2012-09-10 09:46:46,984 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] Dynamic SQL template in TABLE endpoint is: insert into endpoint (model,locale,hardware_id,brandid,trigger_port,type,num_triggers,version,codec6,id,noc_id,codec7,codec8,codec9,created,description,trigger_prefix,last_trigger_ts,os,nf_delivery_type,last_connection_date,vendor,msisdn,msisdn_validated,codec1,codec0,codec5,codec4,codec3,modified,device_uid,os_version,codec2,ip,extra_codecs_id,csc,provisioning_id,imsi,device_group,public_key,ekey) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

                Line 125: 2012-09-10 09:46:51,815 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 16380 start commit time:Mon Sep 10 09:46:51 CST 2012

                Line 128: 2012-09-10 09:46:55,406 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 16380 end commit time:Mon Sep 10 09:46:55 CST 2012

                Line 143: 2012-09-10 09:47:03,123 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 32760 start commit time:Mon Sep 10 09:47:03 CST 2012

                Line 146: 2012-09-10 09:47:04,306 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 32760 end commit time:Mon Sep 10 09:47:04 CST 2012

                Line 159: 2012-09-10 09:47:12,439 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 49140 start commit time:Mon Sep 10 09:47:12 CST 2012

                Line 160: 2012-09-10 09:47:12,730 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 49140 end commit time:Mon Sep 10 09:47:12 CST 2012

                Line 175: 2012-09-10 09:47:22,810 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 65520 start commit time:Mon Sep 10 09:47:22 CST 2012

                Line 176: 2012-09-10 09:47:23,250 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 65520 end commit time:Mon Sep 10 09:47:23 CST 2012

                Line 191: 2012-09-10 09:47:33,913 [pool-4-thread-1] [INFO  com.seven.migrate.job.RollbackJobForBlobTable] ========== Table Name : endpoint, Now counter = 81900 start commit time:Mon Sep 10 09:47:33 CST 2012

 

 

<!--[if !supportLists]-->4.       <!--[endif]-->If the speed still not perfect. For oracle we also can consider to set a table with a table space for oracle,  and use oracle parallel execution. For MySQL we can consider change the environment property of MySQL to improve the read speed of MySQL.

               http://blog.csdn.net/tianlesoftware/article/details/5854583