Streams Performance Data Collection Tools for 10g & 11g

There are diferent ways to collect performance related data

• Install Application Workload Repository (AWR) /Active Session History (ASH). Take AWR snapshots an hour or for desired interval
• Install and Configure STRMMON utility if you have 10gR2(Not available in 11g). Use Metalink Article “290605.1″
• Use UTL_SPADV as a alternative to STRMMON for > 11gR1
• Run Streams Healthcheckat each database: before, during , and after performance period of interest, available from Metalink:Article273674.1

Use AWR with STRMMON (10g)/UTL_SPADV(11g) during performance period of interest

10g : STRMMON Utility

• Install and Configure STRMMON (10gR2) using “290605.1″
• Collect STRMMON information regularly, at least once every hour to align with AWR snapshots.

Here is a STRMMON Command line example
- To collect 10 minutes of output from source and target databases in a single line

60 seconds X 10 Iterations= 600 seconds or 10 minutes
$ORACLE_HOME/denmo$strmmon -interval 10 -count 60 -user sys -passw <sys_password> -dbname<source database> -sysdba \
-user sys -passw<sys_password> -dbname<target database> -sysdba  > strmmon.log

11g : Using Streams performance advisor i.e. UTL_SPADV package (Replacement for STRMMON)

Install this Optional packge inside STREAMS admin user by compiling Sql> @?/rdbms/admin/utlspadv.sql

How to Run UTL_SPADV?
Run collect_stats Procedure will wait until interval and count satisfied , Default interval= 60 sec; Default count=10 iterations