Golengate的基本工作原理是通过挖掘重做日志以获取数据库中的数据变化;而如果我们在数据库中使用并行DML去插入数据的话会因为直接路径插入而产生少量的redo重做日志。那么OGG的日志挖掘是否能正确捕获这些并行DML所产生的数据变化呢?接着我们来实际地测试一下:
SQL> select le.leseq "Current log sequence No", 2 100 * cp.cpodr_bno / le.lesiz "Percent Full", 3 (cpodr_bno - 1) * 512 "Current Offset", 4 le.lesiz * 512 - cpodr_bno * 512 "Left space" 5 from x$kcccp cp, x$kccle le 6 where LE.leseq = CP.cpodr_seq 7 and bitand(le.leflg, 24) = 8; Current log sequence No Percent Full Current Offset Left space ----------------------- ------------ -------------- ---------- 177 78.5112305 82324480 22532608 /* 通过以上查询我们可以了解实际的redo写出情况: Current Offset说明了当前日志文件所写到的位置, 而Left Space说明了当前日志文件所剩余的空间 82324480(Current Offset)+22532608(Left space)+512(redo header)=logfile size=le.lesiz* redo block size */ SQL> alter system switch logfile; System altered. SQL> select le.leseq "Current log sequence No", 2 100 * cp.cpodr_bno / le.lesiz "Percent Full", 3 (cpodr_bno - 1) * 512 "Current Offset", 4 le.lesiz * 512 - cpodr_bno * 512 "Left space" 5 from x$kcccp cp, x$kccle le 6 where LE.leseq = CP.cpodr_seq 7 and bitand(le.leflg, 24) = 8; Current log sequence No Percent Full Current Offset Left space ----------------------- ------------ -------------- ---------- 179 .002441406 2048 104855040 /* 初始位置为No 179的日志文件 */ SQL> select le.leseq "Current log sequence No", 2 100 * cp.cpodr_bno / le.lesiz "Percent Full", 3 (cpodr_bno - 1) * 512 "Current Offset", 4 le.lesiz * 512 - cpodr_bno * 512 "Left space" 5 from x$kcccp cp, x$kccle le 6 where LE.leseq = CP.cpodr_seq 7 and bitand(le.leflg, 24) = 8; Current log sequence No Percent Full Current Offset Left space ----------------------- ------------ -------------- ---------- 180 58.277832 61108224 43748864 /* 使用普通非并行DML插入产生了104855040+61108224=158M的redo */ SQL> truncate table tv; Table truncated. SQL> select le.leseq "Current log sequence No", 2 100 * cp.cpodr_bno / le.lesiz "Percent Full", 3 (cpodr_bno - 1) * 512 "Current Offset", 4 le.lesiz * 512 - cpodr_bno * 512 "Left space" 5 from x$kcccp cp, x$kccle le 6 where LE.leseq = CP.cpodr_seq 7 and bitand(le.leflg, 24) = 8; Current log sequence No Percent Full Current Offset Left space ----------------------- ------------ -------------- ---------- 180 60.6469727 63592448 41264640 /* 初始为No 180日志文件的63592448 */ SQL> alter session enable parallel dml; Session altered. /* 在session级别启用并行DML */ SQL> set autotrace on; SQL> insert /*+ parallel(tv,4) */ into tv select * from sample; 3640772 rows created. Execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify an object after modifying it in parallel SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ---------------------------------------------------------- 111 recursive calls 1168 db block gets 17850 consistent gets 17745 physical reads 97944 redo size 815 bytes sent via SQL*Net to client 750 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3640772 rows processed /* autotrace statistics显示并行插入仅产生了97944字节的redo */ SQL> commit; Commit complete. SQL> select le.leseq "Current log sequence No", 2 100 * cp.cpodr_bno / le.lesiz "Percent Full", 3 (cpodr_bno - 1) * 512 "Current Offset", 4 le.lesiz * 512 - cpodr_bno * 512 "Left space" 5 from x$kcccp cp, x$kccle le 6 where LE.leseq = CP.cpodr_seq 7 and bitand(le.leflg, 24) = 8; Current log sequence No Percent Full Current Offset Left space ----------------------- ------------ -------------- ---------- 182 10.4882813 10997248 93859840 /* 而实际上日志由180切换到了182,实际的redo产生大约是41264640+104857600+10997248=150M */ /* 换而言之autotrace对并行DML语句所产生的实际redo统计远少于实际值, 这也就保证了extract能够捕获到所有这些并行DML所引起的数据变化 */ GGSCI (rh2.oracle.com) 59> stats load2 Sending STATS request to EXTRACT LOAD2 ... Start of Statistics at 2010-12-16 20:17:35. Output to /s01/new/ze: Extracting from CLINIC.TV to CLINIC.TV: *** Total statistics since 2010-12-16 20:17:24 *** Total inserts 923555.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 923555.00 *** Daily statistics since 2010-12-16 20:17:24 *** Total inserts 923555.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 923555.00 *** Hourly statistics since 2010-12-16 20:17:24 *** Total inserts 923555.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 923555.00 *** Latest statistics since 2010-12-16 20:17:24 *** Total inserts 923555.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 923555.00 End of Statistics. GGSCI (rh2.oracle.com) 60> ! stats load2 Sending STATS request to EXTRACT LOAD2 ... Start of Statistics at 2010-12-16 20:17:37. Output to /s01/new/ze: Extracting from CLINIC.TV to CLINIC.TV: *** Total statistics since 2010-12-16 20:17:24 *** Total inserts 1090336.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1090336.00 *** Daily statistics since 2010-12-16 20:17:24 *** Total inserts 1090336.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1090336.00 *** Hourly statistics since 2010-12-16 20:17:24 *** Total inserts 1090336.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1090336.00 *** Latest statistics since 2010-12-16 20:17:24 *** Total inserts 1090336.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1090336.00 End of Statistics. GGSCI (rh2.oracle.com) 61> ! stats load2 Sending STATS request to EXTRACT LOAD2 ... Start of Statistics at 2010-12-16 20:17:39. Output to /s01/new/ze: Extracting from CLINIC.TV to CLINIC.TV: *** Total statistics since 2010-12-16 20:17:24 *** Total inserts 1249284.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1249284.00 *** Daily statistics since 2010-12-16 20:17:24 *** Total inserts 1249284.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1249284.00 *** Hourly statistics since 2010-12-16 20:17:24 *** Total inserts 1249284.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1249284.00 *** Latest statistics since 2010-12-16 20:17:24 *** Total inserts 1249284.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1249284.00 End of Statistics. /* 可以看到extract的统计信息中Total inserts不断递增,说明extract正确捕获了 所有由并行INSERT引发的直接路径插入操作 */
Does Oracle Goldengate support Parallel DML?
结论显然是: Yes。
Leave a Reply