Use SharePlex Analyze to Find Out How Your Application REALLY Works

As a DBA, how many times have you asked yourself, your application team or your vendor, “What tables does this application update?” or, “How many changes does the application make to this table?” or, “What’s the normal transaction volume for this application?”

SharePlex can help you answer these and other questions about your database applications. In addition to replicating changes from one database to another, or providing metadata on changes for auditing, SharePlex has an “analyze” feature that will generate a report showing which tables were changed and how many bytes were processed during the analysis period. If there are foreign keys involved in a transaction, analyze will even show you which tables were involved.

As with everything in SharePlex, we start with a configuration file. If you’re already replicating data, or planning to do so, you can use the same configuration file for both analyze and actual replication, although you can’t analyze and replicate data simultaneously in the same replication stream.

Your configuration file identifies the tables you’d like analyzed. Since you want to see how your application interacts with the database, it makes sense to use the wildcard feature to get all of the tables in the schemas touched by the application of interest. Here’s an example of a simple configuration file that looks at one entire schema.

Once you have a config file, and you’ve run verify (always run verify), you can start the analysis with the analyze command. Here’s the syntax for that command:

analyze config filename n {minutes | hours | days} [ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber   

You’ll want to select run time that includes any special workloads. For example, if you have weekly or monthly batch processing, you may want to be sure analyze is running during those times to capture characteristics of those workloads.

Once analyze is running, the usual SharePlex status commands will show you what’s happening.

Here’s the status command, which shows us that analyze is running, against an active configuration file.

Show Analyze gives us information about the current status of just the Analyze process.

And here, Show Analyze Detail gives us the specifics on how many operations and transactions Analyze has processed. This command also shows us the Activation ID, which we’ll need to select the correct log file when analyze completes.

 

When Analyze completes, the results will be stored in a file in the SharePlex variable data directory, named o.datasource-analysis.activationid. Here's an example analysis file.

The Group listings show us which tables were involved in discrete transactions. This information can be used in tuning SharePlex, as we want to keep related tables in the same post queues.

 

The tablename listing includes all tables and shows us how many operations of each type were performed against each table.

The last part of the analysis file shows us how many bytes the SharePlex capture read, and how many bytes would have been processed (replicated) by SharePlex.

As you can see in this example, SharePlex read 28,014 bytes from the redo log. Note that only 523 of those actually represent changes that SharePlex would have needed to pass from the source to the target.

I hope this will help you understand how analyze can be used to discover what tables are being accessed by your applications and how this can be used as another tool in your database tuning toolset. For more information visit SharePlex.

About the Author
Clay.Jackson
Clay Jackson is a Database Systems Consultant for Quest, specializing in Database Performance Management and Replication Tools. Prior to joining Quest, Jackson was the DBA Manager at Darigold. He also...
  • When would be the best time to run Analyze?  Before you start your production replication process so you can anticipate the load or once you have started replication?