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.

Example:
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.

Example:
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.

Example:
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.

Example:
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.

Example:

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

<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

queue>

<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
_{OUTPUT
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
|}OGG$EXTI 0.01 0.01 0 | NO BOTTLENECK IDENTIFIED

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 |
OGG$EXTI 0.01 0.01 0 | NO BOTTLENECK IDENTIFIED

If you want to find out more on how to decipher these statistics, the legend is located http://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_topology.htm#BIHJAGGJ.

Enjoy!

about.me: http://about.me/dbasolved

Please follow and like: