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。
Comment