Setup Streams Performance Advisor (UTL_SPADV) for #GoldenGate

With Oracle “merging” Oracle GoldenGate into Oracle Streams (or vise-versa), capturing statitics on the intergrated extract (capture) or integrated replicat (happy) will be needed.  In order to do this, the Streams Performance Advisor (UTL_SPADV) can be used.  Before using the Stream Performance Advisor, it needs to be configured under the Streams Administrator, i.e. Oracle GoldenGate user.  In my test lab, I use a user named GGATE for all my Oracle GoldenGate work.

Configure user for UTL_SPADV:

The Oracle user (GGATE) needs to be granted priviliges to run the performance advisor.  This is done by granting permissions through DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE.

SQL> conn ggate/test123
SQL> exec dbms_streams_auth.grant_admin_privilege(‘GGATE’);

Install performance advisor:

After granting the requried permissions to the Oracle user, then the UTL_SPADV package can be installed.

SQL> conn ggate/test123
SQL> @?/rdbms/admin/utlspadv.sql

Gather statistics:

Now that the UTL_SPADV package has been installed, the package can be used from sql*plus to gather statistics on the integrated extract/replicat.

SQL> conn ggate/test123
SQL> exec utl_spadv.collect_stats;

Note: This will take some time to run.  From my tests, it appears to complete as my test sessions disconnect.  

Display statistics:

Once the statistics have been gathered, they can be displayed using the SHOW_STATS option.

SQL> conn ggate/test123
SQL> set serveroutput size 50000
SQL> exec utl_spadv.show_stats;

Statistics Output:

The output will be displayed through sql*plus and will be displayed in intervals of one minute.  Before the display of the statistics start it the advisor provides a legend at the top to help dechiper the output.


<statistics>= <capture> [ <queue> <psender> <preceiver> <queue> ] <apply>

<capture>   = ‘|<C>’ <name> <msgs captured/sec> <msgs enqueued/sec> <latency>

   ‘LMR’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘LMP’ (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>

   ‘LMB’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘CAP’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘CAP+PS’ <msgs sent/sec> <bytes sent/sec> <latency> <idl%>

<flwctrl%> <topevt%> <topevt>

<apply>     = ‘|<A>’ <name> <msgs applied/sec> <txns applied/sec> <latency>

   ‘PS+PR’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘APR’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘APC’ <idl%> <flwctrl%> <topevt%> <topevt>

   ‘APS’ (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>

<queue>     = ‘|<Q>’ <name> <msgs enqueued/sec> <msgs spilled/sec> <msgs in


<psender>   = ‘|<PS>’ <name> <msgs sent/sec> <bytes sent/sec> <latency> <idl%>

<flwctrl%> <topevt%> <topevt>

<preceiver> = ‘|<PR>’ <name> <idl%> <flwctrl%> <topevt%> <topevt>

<bottleneck>= ‘|<B>’ <name> <sub_name> <sessionid> <serial#> <topevt%> <topevt>

<msgs in
PATH 1 RUN_ID 1 RUN_TIME 2015-JAN-12 15:17:31 CCA Y
| OGG$CAP_EXTI 31 31 0 LMR 99.7% 0% 0.3% “” LMP (2) 199.7% 0% 0.3% “” LMB
99.3% 0% 0.3% “”  CAP 99.7% 0% 0.3% “” | “GGATE”.”OGG$Q_EXTI” 0.01 0.01 0

PATH 1 RUN_ID 2 RUN_TIME 2015-JAN-12 15:18:32 CCA Y
| OGG$CAP_EXTI 37 33 1 LMR 98.4% 0% 1.6% “” LMP (2) 198.4% 0% 1.6% “” LMB
98.4% 0% 1.6% “” CAP 100% 0% 0% “” | “GGATE”.”OGG$Q_EXTI” 0.01 0.01 0 |

If you want to find out more on how to decipher these statistics, the legend is located



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs


Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

Stuff that interests me, if not you!

Uwe Hesse

about Database Technology

%d bloggers like this: