SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT ORDER BY ADVISOR_RUN_ID;
ADVISOR_RUN_ID
--------------
1
SQL> COLUMN PATH_ID HEADING 'Path ID' FORMAT 999
SQL> COLUMN COMPONENT_ID HEADING 'Component ID' FORMAT 999
SQL> COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A20
SQL> COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A20
SQL> COLUMN COMPONENT_DB HEADING 'Database' FORMAT A15
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=1 ORDER BY PATH_ID, COMPONENT_ID;
no rows selected
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT ORDER BY ADVISOR_RUN_ID;
ADVISOR_RUN_ID
--------------
1
2
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=2 ORDER BY PATH_ID, COMPONENT_ID;
no rows selected
With a small load
Here we can see that with a small load, the configuration is working fine without any bottlenecks
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT ORDER BY ADVISOR_RUN_ID;
ADVISOR_RUN_ID
--------------
1
2
3
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=3 ORDER BY PATH_ID, COMPONENT_ID;
no rows selected
With high load
In this example, we see a large run away transaction. The transaction causes apply to spill at the destination database. This ultimately forces the capture to go to Flow control state and remains in that state most of the time.
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT ORDER BY ADVISOR_RUN_ID;
ADVISOR_RUN_ID
--------------
1
2
3
4
5
6
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=4 ORDER BY PATH_ID, COMPONENT_ID;
no rows selected
Here from the 5th run of the advisor we can see APPLY as a bottleneck.
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=5 ORDER BY PATH_ID, COMPONENT_ID;
Path ID Component ID Name Type Database
------- ------------ -------------------- -------------------- ---------------
2 6 STRM01_APPLY APPLY EURO.ORACLE.COM
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=6 ORDER BY PATH_ID, COMPONENT_ID;
Path ID Component ID Name Type Database
------- ------------ -------------------- -------------------- ---------------
2 6 STRM01_APPLY APPLY EURO.ORACLE.COM
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT ORDER BY ADVISOR_RUN_ID;
ADVISOR_RUN_ID
--------------
1
2
3
4
5
6
7
8
8 rows selected.
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=7 ORDER BY PATH_ID, COMPONENT_ID;
Path ID Component ID Name Type Database
------- ------------ -------------------- -------------------- ---------------
2 6 STRM01_APPLY APPLY EURO.ORACLE.COM
SQL> SELECT PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB FROM DBA_STREAMS_TP_PATH_BOTTLENECK WHERE BOTTLENECK_IDENTIFIED='YES' AND ADVISOR_RUN_ID=8 ORDER BY PATH_ID, COMPONENT_ID;
Path ID Component ID Name Type Database
------- ------------ -------------------- -------------------- ---------------
2 6 STRM01_APPLY APPLY EURO.ORACLE.COM
Viewing Component-Level Statistics
SQL> COLUMN PATH_ID HEADING 'Path|ID' FORMAT 999
SQL> COLUMN COMPONENT_ID HEADING 'Componenet|ID' FORMAT 999
SQL> COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A20
SQL> COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A12
SQL> COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A25
SQL> COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999999.99
SQL> COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A15
SQL> SELECT DISTINCT
2 cp.PATH_ID,
3 cs.COMPONENT_ID,
4 cs.COMPONENT_NAME,
5 cs.COMPONENT_TYPE,
6 cs.STATISTIC_NAME,
7 cs.STATISTIC_VALUE,
8 cs.STATISTIC_UNIT
9 FROM DBA_STREAMS_TP_COMPONENT_STAT cs,
10 (SELECT PATH_ID, SOURCE_COMPONENT_ID AS COMPONENT_ID
11 FROM DBA_STREAMS_TP_COMPONENT_LINK
12 UNION
13 SELECT PATH_ID, DESTINATION_COMPONENT_ID AS COMPONENT_ID
14 FROM DBA_STREAMS_TP_COMPONENT_LINK) cp
15 WHERE cs.ADVISOR_RUN_ID = 2 AND
16 cs.SESSION_ID IS NULL AND
17 cs.SESSION_SERIAL# IS NULL AND
18 cs.COMPONENT_ID = cp.COMPONENT_ID
19 ORDER BY PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, STATISTIC_NAME;
Path Componenet
ID ID Name Type Statistic Value Unit
---- ---------- -------------------- ------------ ------------------------- --------------- ---------------
1 3 STRM01_CAPTURE CAPTURE BYTES SENT VIA SQL*NET TO 88946.00 BYTES
DBLINK
1 3 STRM01_CAPTURE CAPTURE CAPTURE RATE 2.10 MESSAGES PER SE
COND
1 3 STRM01_CAPTURE CAPTURE ENQUEUE RATE .04 MESSAGES PER SE
COND
1 3 STRM01_CAPTURE CAPTURE EVENT: ARCH wait on c/f t 3.49 PERCENT
x acquire 2
1 3 STRM01_CAPTURE CAPTURE EVENT: CPU + Wait for CPU 1.16 PERCENT
1 3 STRM01_CAPTURE CAPTURE LATENCY .00 SECONDS
1 3 STRM01_CAPTURE CAPTURE SEND RATE TO APPLY 10.95 BYTES PER SECON
D
1 6 STRM01_APPLY APPLY LATENCY 35.00 SECONDS
1 6 STRM01_APPLY APPLY MESSAGE APPLY RATE .00 MESSAGES PER SE
COND
1 6 STRM01_APPLY APPLY TRANSACTION APPLY RATE .00 TRANSACTIONS PE
R SECOND
2 3 STRM01_CAPTURE CAPTURE BYTES SENT VIA SQL*NET TO 88946.00 BYTES
DBLINK
2 3 STRM01_CAPTURE CAPTURE CAPTURE RATE 2.10 MESSAGES PER SE
COND
2 3 STRM01_CAPTURE CAPTURE ENQUEUE RATE .04 MESSAGES PER SE
COND
2 3 STRM01_CAPTURE CAPTURE EVENT: ARCH wait on c/f t 3.49 PERCENT
x acquire 2
2 3 STRM01_CAPTURE CAPTURE EVENT: CPU + Wait for CPU 1.16 PERCENT
2 3 STRM01_CAPTURE CAPTURE LATENCY .00 SECONDS
2 3 STRM01_CAPTURE CAPTURE SEND RATE TO APPLY 10.95 BYTES PER SECON
D
2 6 STRM01_APPLY APPLY LATENCY 35.00 SECONDS
2 6 STRM01_APPLY APPLY MESSAGE APPLY RATE .00 MESSAGES PER SE
COND
2 6 STRM01_APPLY APPLY TRANSACTION APPLY RATE .00 TRANSACTIONS PE
R SECOND
20 rows selected.
Checking the Capture and Apply Processes
SQL> select apply_name,state from v$streams_apply_reader;
APPLY_NAME STATE
------------------------------ -----------------
STRM01_APPLY SPILLING
SQL> select apply_name,state from v$streams_apply_coordinator;
APPLY_NAME STATE
------------------------------ ---------------------
STRM01_APPLY IDLE
SQL> select APPLY_NAME,STATE from v$streams_apply_server;
APPLY_NAME STATE
------------------------------ --------------------
STRM01_APPLY IDLE
SQL> select capture_name,state from v$streams_capture;
CAPTURE_NAME STATE
------------------------------ ------------------------------
STRM01_CAPTURE PAUSED FOR FLOW CONTROL
Viewing Session-Level Statistics
SQL> COLUMN PATH_ID HEADING 'Path|ID' FORMAT 999
SQL> COLUMN COMPONENT_ID HEADING 'Component|ID' FORMAT 999
SQL> COLUMN COMPONENT_NAME HEADING 'Component|Name' FORMAT A20
SQL> COLUMN COMPONENT_TYPE HEADING 'Component|Type' FORMAT A10
SQL> COLUMN SUB_COMPONENT_TYPE HEADING 'Subcomponent|Type' FORMAT A17
SQL> COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A15
SQL> COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 999.99
SQL> COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A7
SQL> SELECT DISTINCT
2 cp.PATH_ID,
cs.COMPONENT_ID,
3 4 cs.COMPONENT_NAME,
5 cs.COMPONENT_TYPE,
6 cs.SUB_COMPONENT_TYPE,
7 cs.STATISTIC_NAME,
8 cs.STATISTIC_VALUE,
9 cs.STATISTIC_UNIT
10 FROM DBA_STREAMS_TP_COMPONENT_STAT cs,
11 (SELECT PATH_ID, SOURCE_COMPONENT_ID AS COMPONENT_ID
12 FROM DBA_STREAMS_TP_COMPONENT_LINK
13 UNION
14 SELECT PATH_ID, DESTINATION_COMPONENT_ID AS COMPONENT_ID
15 FROM DBA_STREAMS_TP_COMPONENT_LINK) cp
16 WHERE cs.ADVISOR_RUN_ID=2 AND
17 cs.SESSION_ID IS NOT NULL AND
18 cs.SESSION_SERIAL# IS NOT NULL AND
19 cs.COMPONENT_ID = cp.COMPONENT_ID
20 ORDER BY PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, STATISTIC_NAME;
Path Component Component Component Subcomponent
ID ID Name Type Type Statistic Value Unit
---- --------- -------------------- ---------- ----------------- --------------- ------- -------
1 3 STRM01_CAPTURE CAPTURE LOGMINER READER EVENT: ARCH wai 3.49 PERCENT
t on c/f tx acq
uire 2
1 3 STRM01_CAPTURE CAPTURE CAPTURE SESSION EVENT: CPU + Wa 1.16 PERCENT
it for CPU
1 3 STRM01_CAPTURE CAPTURE LOGMINER READER EVENT: CPU + Wa 1.16 PERCENT
it for CPU
1 3 STRM01_CAPTURE CAPTURE CAPTURE SESSION FLOW CONTROL .00 PERCENT
1 3 STRM01_CAPTURE CAPTURE LOGMINER BUILDER FLOW CONTROL .00 PERCENT
1 3 STRM01_CAPTURE CAPTURE LOGMINER PREPARER FLOW CONTROL .00 PERCENT
1 3 STRM01_CAPTURE CAPTURE LOGMINER READER FLOW CONTROL .00 PERCENT
1 3 STRM01_CAPTURE CAPTURE CAPTURE SESSION IDLE 98.84 PERCENT
1 3 STRM01_CAPTURE CAPTURE LOGMINER BUILDER IDLE 100.00 PERCENT
1 3 STRM01_CAPTURE CAPTURE LOGMINER PREPARER IDLE 100.00 PERCENT
1 3 STRM01_CAPTURE CAPTURE LOGMINER READER IDLE 95.35 PERCENT
1 6 STRM01_APPLY APPLY APPLY COORDINATOR FLOW CONTROL .00 PERCENT
1 6 STRM01_APPLY APPLY APPLY NETWORK REC FLOW CONTROL .00 PERCENT
EIVER
1 6 STRM01_APPLY APPLY APPLY READER FLOW CONTROL .00 PERCENT
1 6 STRM01_APPLY APPLY APPLY SERVER FLOW CONTROL .00 PERCENT
1 6 STRM01_APPLY APPLY APPLY COORDINATOR IDLE 100.00 PERCENT
1 6 STRM01_APPLY APPLY APPLY NETWORK REC IDLE 100.00 PERCENT
EIVER
1 6 STRM01_APPLY APPLY APPLY READER IDLE 100.00 PERCENT
1 6 STRM01_APPLY APPLY APPLY SERVER IDLE 100.00 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER READER EVENT: ARCH wai 3.49 PERCENT
t on c/f tx acq
uire 2
2 3 STRM01_CAPTURE CAPTURE CAPTURE SESSION EVENT: CPU + Wa 1.16 PERCENT
it for CPU
2 3 STRM01_CAPTURE CAPTURE LOGMINER READER EVENT: CPU + Wa 1.16 PERCENT
it for CPU
2 3 STRM01_CAPTURE CAPTURE CAPTURE SESSION FLOW CONTROL .00 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER BUILDER FLOW CONTROL .00 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER PREPARER FLOW CONTROL .00 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER READER FLOW CONTROL .00 PERCENT
2 3 STRM01_CAPTURE CAPTURE CAPTURE SESSION IDLE 98.84 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER BUILDER IDLE 100.00 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER PREPARER IDLE 100.00 PERCENT
2 3 STRM01_CAPTURE CAPTURE LOGMINER READER IDLE 95.35 PERCENT
2 6 STRM01_APPLY APPLY APPLY COORDINATOR FLOW CONTROL .00 PERCENT
2 6 STRM01_APPLY APPLY APPLY NETWORK REC FLOW CONTROL .00 PERCENT
EIVER
2 6 STRM01_APPLY APPLY APPLY READER FLOW CONTROL .00 PERCENT
2 6 STRM01_APPLY APPLY APPLY SERVER FLOW CONTROL .00 PERCENT
2 6 STRM01_APPLY APPLY APPLY COORDINATOR IDLE 100.00 PERCENT
2 6 STRM01_APPLY APPLY APPLY NETWORK REC IDLE 100.00 PERCENT
EIVER
2 6 STRM01_APPLY APPLY APPLY READER IDLE 100.00 PERCENT
2 6 STRM01_APPLY APPLY APPLY SERVER IDLE 100.00 PERCENT
38 rows selected.
Viewing Statistics for the Stream Paths in an Oracle Streams Environment
SQL> COLUMN PATH_ID HEADING 'Path ID' FORMAT 999
SQL> COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A25
SQL> COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999.99
SQL> COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A25
SQL> SELECT PATH_ID,
STATISTIC_NAME,
STATISTIC_VALUE,
STATISTIC_UNIT
FROM DBA_STREAMS_TP_PATH_STAT
WHERE ADVISOR_RUN_ID=8
ORDER BY PATH_ID, STATISTIC_NAME;
Path ID Statistic Value Unit
------- ------------------------- ------------ -------------------------
2 MESSAGE RATE .00 MESSAGES PER SECOND
2 TRANSACTION RATE .00 TRANSACTIONS PER SECOND