Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 9.2.0.1 to 10.2.0.4
Purpose
Purpose
This Notes explains what diagnostics are required when troubleshooting the Streams processes
SCOPE & APPLICATION
To be used by DBAs as a reference when troubleshooting the Streams
This article is applicable for any platform
Version:9.2.x -10.2.x.x.x
Last Review Date
March 11, 2009
Instructions for the Reader
Troubleshooting Details
Memory
To determine number of messages in buffered queue
9.2 only:
AS SYS
select bufqm_ qid, bufq_nmsg from x$bufqm; 10g:
select queue_id, queue_schema, queue_owner, num_msgs from v$buffered_queues;
To determine number of messages in spill-over.
As Streams administrator, determine the spill-over table name owned by the streams administrator: AQ$_<queue_table_name>_P
9.2:
select count(*) from AQ$_<queue_table_name>_P; 10g:
select queue_id, queue_schema, queue_owner, spill_msgs from v$buffered_queues; To determine memory usage
As SYS on the instance running the Streams process:
select ksmchcom, sum ( ksmchsiz ) from x$ksmsp group by ksmchcom order by 2;
select sum(ksmchsiz) from x$ksmsp where ksmchcom='Queue heap';
Propagation
To determine if queue has not recovered normally
As SYS
select queue_schema,queue_name from x$buffered_queues where flags=1; To determine acknowledgement status for queue
9.2
As SYS
-- run on source
Select addr, kwqpsqid,kwqpsdbn,kwqpshwm,kwqpsack from x$kwqps;
-- run on target
Select addr, kwqpdsqn,kwqpddbn,kwqpddqn,kwqpdhwm,kwqpdack from x$kwqpd;
10g Onwards
-- on source
Select * from gv$propagation_sender ;
-- on target
Select * from gv$propagation_receiver;
To determine queue flow control settings
As SYS
Select * from x$kwqbpmt; As of 10.2.0.4 , flow control will be enabled when there are 15000 unbrowsed captured messages in the queue. For buffered messages, this value is 5000.
Flow control will end at 80% of this value: For captured messages: FLCP_KWQBPMT. For buffered messages: FLBP_KWQBPMT
The flow control value can be set with the init.ora parameter _capture_publisher_flow_control_threshold (for CAPTURED messages) or _buffered_publisher_flow_control_threshold. This parameter is a database wide setting and can be set dynamically via
alter system set "_capture_publisher_flow_control_threshold"=15000; The flow control value can be modified in the one-off backports of this bug ( 10.2.0.3 or 10.1.0.5 with patch for bug 5093060) by setting the event 10865. The level setting of the event specifies the flow_control threshold:
alter system set events '10865 trace name context forever, level 15000'; Verify process rulesets:
All releases:
select queue_owner||'.'||queue_name queue,capture_name,capture#,ruleset_owner||'.'||ruleset_name ruleset from streams$_capture_process;
select queue_owner||'.'||queue_name queue,apply_name,apply#,ruleset_owner||'.'||ruleset_name ruleset from streams$_apply_process;
Latency Queries
The Latency is the total time taken by a process to change from one state to another.
NOTE: The execution of the queries to determining the latency information are valid only for an enabled capture, propagation and apply process.
Determining Redo Log Scanning Latency for Each Capture Process
The redo log scanning latency, which specifies the number of seconds between the creation time of the most recent redo log entry scanned by a capture process and the current time. This number might be relatively large immediately after you start a capture process.
COLUMN CAPTURE_NAME HEADING ‘Capture|Process|Name’ FORMAT A10
COLUMN LATENCY_SECONDS HEADING ‘Latency|in|Seconds’ FORMAT 999999
COLUMN LAST_STATUS HEADING ‘Seconds Since|Last Status’ FORMAT 999999
COLUMN CAPTURE_TIME HEADING ‘Current|Process|Time’
COLUMN CREATE_TIME HEADING ‘Message|Creation Time’ FORMAT 999999
SELECT CAPTURE_NAME,
((SYSDATE – CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,
((SYSDATE – CAPTURE_TIME)*86400) LAST_STATUS,
TO_CHAR(CAPTURE_TIME, ‘HH24:MI:SS MM/DD/YY’) CAPTURE_TIME,
TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, ‘HH24:MI:SS MM/DD/YY’) CREATE_TIME
FROM V$STREAMS_CAPTURE;
Determining Message Enqueuing Latency for Each Capture Process
The message enqueuing latency, which specifies the number of seconds between when an entry was recorded in the redo log and when the message was enqueued by the capture process
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATE_TIME HEADING 'Message Creation|Time' FORMAT A20
COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20
COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999
SELECT CAPTURE_NAME,
(ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS,
TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,
TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,
ENQUEUE_MESSAGE_NUMBER
FROM V$STREAMS_CAPTURE;
Determining Capture to Dequeue Latency for a Message
The Capture to Dequeue Latency is the amount of time between when the message was created at a source database and when the message was dequeued by the apply process. For user-enqueued messages, the latency is the amount of time between when the message enqueued at the local database and when the message was dequeued by the apply process.
COLUMN APPLY_NAME HEADING ‘Apply Process|Name’ FORMAT A17
COLUMN LATENCY HEADING ‘Latency|in|Seconds’ FORMAT 9999
COLUMN CREATION HEADING ‘Message Creation’ FORMAT A17
COLUMN LAST_DEQUEUE HEADING ‘Last Dequeue Time’ FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING ‘Dequeued|Message Number’ FORMAT 999999
SELECT APPLY_NAME,
(DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,’HH24:MI:SS MM/DD/YY’) CREATION,
TO_CHAR(DEQUEUE_TIME,’HH24:MI:SS MM/DD/YY’) LAST_DEQUEUE,
DEQUEUED_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_READER;
Determining latency of the propagation job
The latency of the propagation job, which is the maximum wait time to propagate a new message during the duration, when all other messages in the queue to the relevant destination have been propagated.
COLUMN START_DATE HEADING 'Start Date'
COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999
COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8
COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999
COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8
COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99
SELECT DISTINCT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,
s.PROPAGATION_WINDOW,
s.NEXT_TIME,
s.LATENCY,
DECODE(s.SCHEDULE_DISABLED,
'Y', 'Disabled',
'N', 'Enabled') SCHEDULE_DISABLED,
s.PROCESS_NAME,
s.FAILURES
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2'
AND p.DESTINATION_DBLINK = s.DESTINATION
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME;
Determining the Capture to Apply Latency for a Message for Each Apply Process
The latency is the amount of time between when the message was created at a source database and when the message was dequeued by the apply process. For user-enqueued messages, the latency is the amount of time between when the message enqueued at the local database and when the message was dequeued by the apply process.
The following queries show the amount of time between when the message was created at a source database and when the message was applied by the apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view. The other uses the DBA_APPLY_PROGRESS static data dictionary view.
V$STREAMS_APPLY_COORDINATOR Query for Latency
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999
SELECT APPLY_NAME,
(HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
HWM_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_COORDINATOR;
DBA_APPLY_PROGRESS Query for Latency
COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999
SELECT APPLY_NAME,
(APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
APPLIED_MESSAGE_NUMBER
FROM DBA_APPLY_PROGRESS;
You can query SCN_TO_TIMESTAMP function to get the more accurate latency time value.
SQL> select * from scn_to_timestamp(scn) from dual;This function takes the SCN as an Oracle number datatype and returns the corresponding TIMESTAMP.
© 2009 – 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Oracle Streams Performance Advisor
- Troubleshooting Streams Capture when status is Paused For Flow Control
- How To Troubleshoot the Streams Apply Process
- Oracle Streams Topology
- Streams Combined Capture and Apply in 11g
- Troubleshooting Streams Apply Error ORA-1403, ORA-26787 or ORA-26786
- TROUBLESHOOTING: Tuning Queries That Cannot be Modified
- Streams Message Tracking in 11g
- 11g R1 Streams New Features
- Scripts for Oracle Streams Performance Tuning Best Practice: Oracle 10g Release 10.2
最新评论