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号-2

沪公网安备 31010802001379号