Diagnosing a slow Extract

Until now, we have discussed the performance of Replicat because this generally requires tuning, whether it be adjusting the integrated parameters or adding additional processes. Occasionally, however, the Extract process may be slow, but before we tune, we must diagnose. Typically, slow performance lies with I/O operations, but let's perform the following steps to find the root cause for our Extract running in the classic capture mode:

  1. Firstly, collect the Extract's statistics using the STATS command:
    GGSCI (db12server01) 1>  stats EOLTP01, totalsonly *, reportrate sec
GGSCI (db12server01) 2>  stats EOLTP01, totalsonly *, reportrate min
  2. Copy the Extract's parameter file to a test parameter file as follows:
    $ cp ./dirprm/EOLTP01.prm ./dirprm/ETEST.prm
  3. Add the TESTMAPPINGSPEED parameter to the test Extract parameter file, as shown in the following code:
    TESTMAPPINGSPEED
REPORTCOUNT EVERY 5000 RECORDS
  4. Add the test Extract process group ETEST with begin now timestamp with the following command:
    GGSCI (db12server01) 3>  add extract ETEST, tranlog, begin now
  5. Alter the test Extract to read from a specific archive log file available in the log file path. This is done by setting the EXTSEQNO and EXTRBA for the archived log, as shown here:
    GGSCI (db12server01) 4>  alter extract ETEST, extseqno <arch_seq_no>, extrba 0

    The current list of database archived logs with thread and sequence numbers may be obtained from the following SQL command

    SQL> select name, thread#, sequence# from v$archived_log;
  6. Start the test Extract and wait for 5 minutes. This is an approximate time that should be long enough to enable sufficient data. Then, check the statistics again.
    GGSCI (db12server01) 5>  stats EOLTP01, totalsonly *, reportrate sec
GGSCI (db12server01) 6>  stats EOLTP01, totalsonly *, reportrate min
  7. Compare the statistics between the original Extract and the test Extract. If you see significant speed increase after adding TESTMAPPINGSPEED, then the bottleneck could be the Extract writing to the trail file.
  8. If there is a little difference, remove TESTMAPPINGSPEED from the test Extract parameter file and add the TRACE2 parameter in its place to determine whether the Extract is performing any fetch operation.
  9. Restart the test Extract and for wait 5 minutes (as in step 6).
  10. Stop the test Extract, check the trace file for any SELECT statements, and see how long they take to execute.
  11. If there are significant avg and max wait times against a given SELECT statement, the source database has a performance problem; a lack of fresh table statistics or a missing index could be the cause.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.17.23.130