Shaun's Wiki

User Tools

Site Tools

Trace: • oracle_goldengate • solaris
oracle_goldengate

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
oracle_goldengate [2018/11/13 10:24]
ss_wiki_admin [Network Stats] 		oracle_goldengate [2021/04/23 07:57] (current)
ss_wiki_admin [Process Memory Usage]
Line 37: Line 37:
 SELECT r.apply_name, 86400 *(r.dequeue_time - c.lwm_message_create_time) latency_in_seconds FROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c WHERE r.apply# = c.apply# and r.apply_name = c.apply_name; SELECT r.apply_name, 86400 *(r.dequeue_time - c.lwm_message_create_time) latency_in_seconds FROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c WHERE r.apply# = c.apply# and r.apply_name = c.apply_name;
 </code> </code>
 +
 +==== Heartbeat Table ====
 +Show heartbeat for all extracts and replicats
 +<code sql>
 +select * from ggadmin.gg_heartbeat;
 +</code>
 +Show lag history 
 +<code sql>
 +set pagesize 200 linesize 200
 +col heartbeat_received_ts format a30
 +col incoming_path format a24
 +col incoming_lag format 999,999.999
 +ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
 +SELECT heartbeat_received_ts, incoming_path, incoming_lag FROM ggadmin.gg_lag_history;
 +</code>
 +
 +==== Tuning STREAMS pool ====
 +Set the STREAMS_POOL_SIZE initialization parameter for the database to the following value:
 +**(MAX_SGA_SIZE * # of integrated Extracts) + 25% head room**
 +
 +<code sql>
 +select current_size from v$sga_dynamic_components where component = 'streams pool';
 +
 +/* Streams pool advice */
 +COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE  HEADING 'Streams Pool|Size for Estimate'
 +  FORMAT 999999999999
 +COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Streams Pool|Size|Factor' FORMAT 99.9
 +COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
 +COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
 +COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
 +COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99
 + 
 +SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
 +       STREAMS_POOL_SIZE_FACTOR, 
 +       ESTD_SPILL_COUNT, 
 +       ESTD_SPILL_TIME, 
 +       ESTD_UNSPILL_COUNT,
 +       ESTD_UNSPILL_TIME
 +  FROM V$STREAMS_POOL_ADVICE;
 +
 +</code>
 +
 +==== Monitor Dependency Waits ====
 +If there are many wait dependencies when using integrated Replicat, try reducing the value of BATCHTRANSOPS.
 +<code sql>
 +select APPLY_NAME, STATE, ACTIVE_SERVER_COUNT, TOTAL_WAIT_DEPS, TOTAL_WAIT_COMMITS from V$GG_APPLY_COORDINATOR;
 +</code>
 +
 +
 +
 ==== Process Memory Usage ==== ==== Process Memory Usage ====
 <code bash gg_memory_usage.sh> <code bash gg_memory_usage.sh>
Line 139: Line 189:
 # Individual extract Process Usage # # Individual extract Process Usage #
 ##################################### #####################################
-41.7109 MB DP_CLI +41.7109 MB DP_C 
-31.9805 MB DP_FMS +31.9805 MB DP_F 
-29.9297 MB DP_MR +29.9297 MB DP_M 
-37.3672 MB DP_MR_O +37.3672 MB DP_M_O 
-37.8438 MB DP_SDE +37.8438 MB DP_S 
-29.1562 MB DP_SERV +29.1562 MB DP_SV 
-1516.69 MB EXT_CLI +1516.69 MB EXT_C 
-74.8516 MB EXT_FMS +74.8516 MB EXT_F 
-556.352 MB EXT_MR +556.352 MB EXT_M 
-246.926 MB EXT_MR_O +246.926 MB EXT_M_O 
-68.7773 MB EXT_SDE +68.7773 MB EXT_S 
-91.5234 MB EXT_SERV+91.5234 MB EXT_SV
  
 ##################################### #####################################
Line 161: Line 211:
 </code> </code>
  
 +==== Apply Server Details ====
 +<code sql>
 +select APPLY_NAME, APPLY#, SERVER_ID, STATE, TOTAL_MESSAGES_APPLIED from V$GG_APPLY_SERVER ORDER BY APPLY_NAME, SERVER_ID;
 +</code>
  
 +==== Apply Coordinator Detail ====
 +<code sql>
 +SELECT APPLY_NAME, STATE, TOTAL_APPLIED, TOTAL_ASSIGNED, TOTAL_ROLLBACKS, TOTAL_ERRORS, ACTIVE_SERVER_COUNT, TOTAL_WAIT_DEPS, TOTAL_WAIT_COMMITS FROM V$GG_APPLY_COORDINATOR;
 +</code>
  
 +==== Queue Status ====
 +<code sql>
 +SELECT DST_QUEUE_SCHEMA, DST_QUEUE_NAME, TOTAL_MSGS, SPID, STATE from V$PROPAGATION_RECEIVER;
 +</code>
 +
 +==== Apply Latency ====
 +<code sql>
 +SELECT r.apply_name, 86400 *(r.dequeue_time -c.lwm_message_create_time) latency_in_seconds FROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c WHERE r.apply# = c.apply# and r.apply_name = c.apply_name;
 +</code>
 +
 +==== Trace Monitoring ====
 +under OGG installation home directory, create an xml file named gglog-<EXT_OR_REP_NAME>.xml
 +gglog-<EXT_OR_REP_NAME>.xml contents:
 +<code xml>
 +<?xml version="1.0"?>
 +<configuration reset="true">
 +<appender name="myTraceFile" class="FileAppender">
 +<param name="File" value="myTrace-%I.log"/>
 +<param name="Append" value="true"/>
 +<layout class="PatternLayout"/>
 +</appender>
 +<logger name="ggstd.ptrace">
 +<level value="all"/>
 +</logger>
 +</configuration>
 +</code>
oracle_goldengate.1542104693.txt.gz · Last modified: 2019/09/16 16:10 (external edit)

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki