Conference orarep::nomahs::dec_data_distributor

234.0. "Slow replication update: something wrong or correct behavior?" by itvms1.it.oracle.com::ITOTA () Tue Jul 30 1996 06:49

	Hi,
	
	we experienced a very long update replication transfer.
	The transfer logfile follows.
	DDAL need 2789 transaction to know that no changes 
	need to be applyed.
	As you can see, with customer slow network the UPDATE REPLICATION	
	had been 3 hours long.

	Maybe this transfer had been created some months ago and never
	re-executed.
	Maybe also that RDB$Changhes had never been purged and 
	has about 14000 records.
	Other transfers from the same source database to the same target
	take only few minutes , with 20/30 transaction applied.
	Why we experienced this different behavior?

	Do you need other infos?


				Thanks a lot,


						Ivo Tota

						Oracle Turin, 	Italy
	


$ set nover
%SET-W-NOTSET, error modifying MBA7027:
-SET-E-INVDEV, device is invalid for requested operation
$ SHOW = "SHOW"
$ RUN = "RUN"
$ LOGOUT = "LOGOUT"
$ SHOW PROCESS 

26-JUL-1996 02:01:09.26   User: DISTRSWS         Process ID:   2020FEAD
                          Node: V92511           Process name: "DDAL_COPY_01   "

Terminal:           MBA7027:
User Identifier:    [1,4]
Base priority:      4
Default file spec:  DSA1:[DISTRIBUZIONE]
$ DEFINE DDAL$CP_CONTINUE "SUCCESS"
$ DEFINE/TRANS=TERMINAL DDAL$TRANSFER_NAME T_024_CREELD
$ DDAL$CP_ACTION :== U
$ DDAL$CP_RETRY_ITERATION == 00
$ RUN DDAL$COPY_PROCESS

Product version:	DEC Data Distributor V6.0-0

----- 26-JUL-1996 02:01:10.26 ----- Process         -------------------------
Process name:  DDAL_COPY_01                  Priority:  4                       
Username:  DISTRSWS                          UIC:  [1,4]                        
Nodename:  V92511                            PID:  2020FEAD                     
Privileges currently enabled:
    CMKRNL, CMEXEC, SYSNAM, GRPNAM, ALLSPOOL, DETACH, DIAGNOSE, LOG_IO, GROUP, 
    ACNT, PRMCEB, PRMMBX, PSWAPM, ALTPRI, SETPRV, TMPMBX, WORLD, MOUNT, OPER, 
    EXQUOTA, NETMBX, VOLPRO, PHY_IO, BUGCHK, PRMGBL, SYSGBL, PFNMAP, SHMEM, 
    SYSPRV, SYSLCK, SHARE, UPGRADE, DOWNGRADE, GRPPRV, READALL, SECURITY

Image privileges:
    SYSPRV, BYPASS, SYSLCK, SECURITY

Image name:
    DSA0:[SYS1.SYSCOMMON.][SYSEXE]DDAL$COPY_PROCESS.EXE

Transfer database = DSA0:[DDAL_11.DBASE]DDAL$TR_DB
Transfer name     = T_024_CREELD                   
Transfer option   = U (Replication update transfer)

02:01:10  %DDAL-I-READTRDEF, reading the transfer definition from the transfer database
02:01:14  %DDAL-I-ATTACHSDB, attaching to source database DSA2:[DBASE.TRANS40]DB_TRANS.RDB
02:01:20  %DDAL-I-ATTACHTDB, attaching to target database V02401"DISTRSWS SOFTWARE"::ODS3$DB:ODS3.RDB
02:01:28  %DDAL-I-STADATTRM, starting data transfer/modification
05:12:38  %DDAL-I-PURGING_1, starting purge of RDB$CHANGES records because ...
          %DDAL-I-PURGING_3, ... DDAL$PURGE is defined to be "Y"
05:12:54  %DDAL-I-ENDDATTRM, ending data transfer/modification

05:12:57  %DDAL-I-REPUPSTAT,    REPLICATION UPDATE STATISTICS

          %DDAL-I-NUMRECSTO, number of records stored    =          0
          %DDAL-I-NUMRECMOD, number of records modified  =          0
          %DDAL-I-NUMRECERA, number of records erased    =          0
          %DDAL-I-TOTAL_SME, total stores, mods & erases =          0

          %DDAL-I-TOTALSRCT, source transactions applied =       2789

05:12:57  %DDAL-I-COPY_EXIT, normal copy process termination

$ @DDAL$EPILOGUE DSA2:[DBASE.ODS3.COM]DDAL_EPILOGUE_NOW.COM 
$ GOTO Skip_Comments
$ Skip_Comments:
$
$!  Save the return status from the DDAL$COPY_PROCESS image in DDAL$SAVE_STATUS.
$
$	DDAL$SAVE_STATUS :== %X012683AB
$	ON SEVERE_ERROR THEN GOTO Cleanup
$
$!  DEFINE DDAL$CP_CONTINUE based on the status value received from the
$!  DDAL$COPY_PROCESS image.
$!
$!  SUCCESS   = DDAL$COPY_PROCESS succeeded
$!  RETRY     = DDAL$COPY_PROCESS failed but should be retried
$!  PRO_RETRY = The prologue failed.  The transfer should be retried.
$!  PRO_FAIL  = The prologue failed.  The transfer should not be retried.
$!  FAIL      = Anything else should be a copy process failure that should not
$!		be retried.
$
$	IF DDAL$SAVE_STATUS .EQ. %X012683AB
$	THEN
$	    DEFINE DDAL$CP_CONTINUE "SUCCESS"
%DCL-I-SUPERSEDE, previous value of DDAL$CP_CONTINUE has been superseded
$	ELSE
$	ENDIF
$
$!  Now execute the user's epilogue procedure named in the transfer definition.
$
$	@DSA2:[DBASE.ODS3.COM]DDAL_EPILOGUE_NOW.COM
$ set noon
$ set ver
$!
$ tname = f$trnlnm("DDAL$TRANSFER_NAME")
$ node=f$getsyi("nodename")
$ que_name="'node'"+"_batch"
$ start_file = "ods3$log:"+"start_file_"+"T_024_CREELD"+".TXT"
$ purge/log/noconf ods3$log:start_file_T_024_CREELD.TXT
%PURGE-W-SEARCHFAIL, error searching for DSA2:[DBASE.ODS3.LOG]START_FILE_T_024_CREELD.TXT;*
-RMS-E-FNF, file not found
%PURGE-I-NOFILPURG, no files purged
$!
$ cp_cont = f$trnlnm("ddal$cp_continue")
$ if cp_cont .eqs. "FAIL" then goto trans_fail
$ set nover
  DISTRSWS     job terminated at 26-JUL-1996 05:12:58.86

  Accounting information:
  Buffered I/O count:           39307         Peak working set size:  14048
  Direct I/O count:              1857         Peak page file size:    42128
  Page faults:                   1031         Mounted volumes:            0
  Charged CPU time:           0 00:00:18.19   Elapsed time:     0 03:11:50.79

Greetings, Ivo,

>	we experienced a very long update replication transfer.
>	The transfer logfile follows.

>	DDAL need 2789 transaction to know that no changes 
>	need to be applyed.

>	As you can see, with customer slow network the UPDATE REPLICATION	
>	had been 3 hours long.
>
>	Maybe this transfer had been created some months ago and never
>	re-executed.

    This is almost certainly the reason.  If a transfer is not run on a
    regular basis, the rows in the RDB$CHANGES table continue to accumulate.
    When the transfer is finally run, naturally it has to read through all
    the saved transactions to see which, if any, apply to the given transfer.

>	Maybe also that RDB$Changhes had never been purged and 
>	has about 14000 records.

    That is what would be expected if you have one transfer that is run very
    infrequently.

>	Other transfers from the same source database to the same target
>	take only few minutes , with 20/30 transaction applied.
>	Why we experienced this different behavior?

    These other transfers have been run on a regular basis and do not have
    to re-process the older transactions.  The transfer that is run very
    seldom has a lot of catching up to do.

    Item number 2531 on our product wish list for future improvements is to
    find a different way to purge transactions out of the RDB$CHANGES
    table.  For now, the behavior they are seeing is what one would expect
    if there is a transfer that is infrequently run.
    
    I suggest that they run that transfer more often, even if they know
    that there is nothing new to be sent to the target system.  Doing so
    will keep the number of transactions in RDB$CHANGES to a smaller number
    and will keep performance from getting worse as RDB$CHANGES grows in
    size.
    
    Claude
    
Claude