Does Oracle Goldengate support Parallel DML?

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

*

沪ICP备14014813号

沪公网安备 31010802001379号