How To Troubleshoot Oracle Redo Log Reading Extract Slow Performance Issue using TESTMAPPINGSPEED (文

How To Troubleshoot Oracle Redo Log Reading Extract Slow Performance Issue using TESTMAPPINGSPEED (文档 ID 1273285.1) 转到底部 In this Document Goal   Solution APPLIES TO: Oracle GoldenGate - Version 4.0.0 and later Information in this document applies to any platform. ***Checked for relevance on 10-OCT-2014***  GOAL How To Troubleshoot Extract Slow Performance Issue SOLUTION Issue: How To Troubleshoot Classic Extract Slow Performance  Below are the steps to follow: 1.Collect below stats on the original Extract which is performing slow GGSCI> stats extract <extract_name>, totalsonly *, reportrate sec GGSCI> stats extract <extract_name>, totalsonly *, reportrate min 2. Make copy of the existing extract parameter file. OS>cp <extract_name>.prm ETEST.prm 3. Change the name to ETEST and trail file to ET. 4. ADD TESTMAPPINGSPEED parameter to the test Extract parameter file. TESTMAPPINGSPEED REPORTCOUNT EVERY 5000 RECORDS 5.Add the test extract with begin time stamp GGSCI> add extract etest, tranlog, begin now 6. Alter the test extract to read from specific archive log file available in the log file path which original extract was reading slow. GGSCI> alter extract etest, extseqno <arch_seq_no>, extrba 0 7. Start the extract and check the speed. This will show you the speed of Extract after eliminating the trail writing part. GGSCI> start extract etest Wait for 3 minutes and then check for stats GGSCI> stats extract etest, totalsonly *, reportrate sec GGSCI> stats extract etest, totalsonly *, reportrate min Now compare the stats between original Extract and test Extract. If you see significant speed increase by adding TESTMAPPINGSPEED, that means the bottleneck could be the Exttrail writing (assuming Extract writes locally) or network socket writing (assuming Extract writes across to the network).  If there is not any significant difference after adding TESTMAPPINGSPEED, go to next step. 8.Comment out all the table statement, add in one dummy table that you know sure there is none or very little transaction that happens to it.If you see significant speed increase after this, the bottleneck is in the log record processing. Normally the archived log record parsing is composed by two parts A. Record parsing in Extract B. Record fetching if needed 9. To determine if Extract is doing any fetch, please enable TRACE/TRACE2 in Extract, (please make sure to comment out TESTMAPINGSPEED, get back to original tables), run it against the same log file for couple minutes)  --TESTMAPPINGSPEED TRACE ./dirtmp/ext.trc TRACE2 ./dirtmp/ext.trc2 10.Check the trace files if there is any SELECT statement in the trace and check how much time those select statement consumes. If majority of the time Extract spent are on the SELECT statements, then this is a bottleneck the DBA needs to address.  11.If you see any snapshot related errors(Oracle database only) in the trace file then verify if enough UNDO retention period and tablespace is specified. Try using FETCHOPTIONS NOUSESNAPSHOT parameter in the Extract to fetch the column data directly from database rather than avoiding failure fetching from snapshot 12.If by commenting out all tables and add only one dummy table, still you don't see any significant speed increase, at the same time, CPU is not busy, then it is most likely an I/O bottleneck issue, proceed to next step  13. Testing the speed of the disk by doing dd command to the archived log file we are reading. Example: shell>time dd if=<archived log file name with full path> of=/dev/null bs=1M Then test the dd speed on another disk, such as an internal disk where we know sure there is no I/O bottleneck, compare the time, to see if the archived log or redo log disk speed is significantly slower.