Skip Headers
Oracle® Database Lite Tools and Utilities Guide
10g (10.0.0)
Part No. B12263-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

5 Consolidator Performance (Consperf) Utility

This document provides a reference to the Consolidator Performance (Consperf) utility used to profile Consolidator publications. Each section of this document presents a different topic. Topics include:

5.1 Overview

The Consperf utility is used to profile Consolidator publications. Application developers and administrators can use this utility to analyze performance of publications and identify potential bottlenecks during publication. This tool enables users to perform four primary functions:

During the Synchronization and MGP process, the Consolidator wraps publication item queries in templates to determine incremental changes. With complex snapshot queries, these templates can confuse the Oracle optimizer frequently and may result in poor execution plans. The Consperf utility exposes such templates and profiles their performance in conjunction with actual publication item queries. The Consperf utility generates SQL explain plans for each query.

Consperf Query Types

Query type codes are used in Consperf output files to identify various templates.

Table 5-1 lists Consperf query types used to identify templates and customized callouts.

Table 5-1 Consperf Query Types

Query Type Description
BASE Publication Item Query
NS Null Sync Callout
BS Before Sync Callout
SYNC_1 Sync template using outer-join for inserts, updates, and deletes
SYNC_2 Sync template for inserts and updates
SYNC_21 Sync templates for deletes
SYNC_22 Sync template for deletes using HASH_AJ
AS After Sync Callout
BC Before Compose Callout
LDEL_1 MGP template for logical deletes using EXISTS
LDEL_2 MGP template for logical deletes using correlated IN
LDEL_3 MGP template for logical deletes using HASH_AJ
LDEL_4 MGP template for logical deletes using IN
LINS_1 MGP template for logical inserts using EXISTS
LINS_2 MGP template for logical inserts using correlated IN
LINS_3 MGP template for logical inserts using IN
LG_1 MGP template for log updates using correlated IN
LG_2 MGP template for log updates using EXISTS
LG_3 MGP template for log updates using IN
LGN_1 MGP template for log updates with multiple table dependencies
AC After compose callout
MAXLOG MGP template to simulate log updates
DSYNC Sync template to simulate records in the outbound client queue (used only by Consperf)
DMGP MGP template to simulate records in the outbound client queue (used only by Consperf)
BASEVIEW Indicates that a publication item can use an optimized base view. This template is generated by using the AUTOTUNE parameter and is referenced during synchronization.

5.2 Generating Timing Statistics for Publications

The Consperf utility enables application developers and administrators to generate timing statistics for publications. Users must specify the output file where the timing results are to be written. The timing statistics output is formatted into the following four distinct sections:

Table 5-2 lists subscription properties and their corresponding description.

Table 5-2 Subscription Properties

Property Description
Profiled Indicates whether the publication item has been profiled with the Auto-Tune parameter enabled.
Base View Indicates whether the publication item can use an optimized base view during synchronization. Both "Profiled" and "Base View" should be true before the base view is enabled.
Sub # Indicates the number of records subscribed by the specified client.
Dirty # Indicates the number of records currently marked as dirty for the specified client.
POPLOG Indicates the number of records generated in the transaction log.
DSYNC Indicates the number of records simulated in the outbound queue for the specified client in the Sync phase.
DMGP Indicates the number of records simulated in the outbound queue for the specified client in the MGP phase.

Timing statistics for all publications are displayed in milliseconds. All the default query template types used during the Synchronization and MGP process are listed in brackets. For example, <10> represents a default query with a duration of 10 milliseconds.

5.3 Generating Explain Plans for Publications

Using the Consperf utility, application developers and administrators can generate explain plans. Users must specify an output file to store plan results. This output is divided by publication item and a plan is generated for all the relevant query templates.

5.4 Automatically Tuning Publication Properties

The Consperf utility is used to fine-tune a given publication's properties. Automatically tuned results generated for a profile can be used to complete a publication's properties. This function enables application developers and the administrator to choose the best performing query templates. In addition, the Consperf utility determines whether a publication item query can be replaced with an optimized base view. This replacement further improves synchronization performance when the publication item query is complex.

To tune publications, application developers and administrators must execute the Consperf utility with the AUTOTUNE parameter enabled.

For more information on running the Consperf utility with this option enabled, see Section 5.6, "Usage Model".

Table 5-3 displays Tuning results that are stored in the Mobile Server repository:

Table 5-3 C$CONSPERF TUNING RESULTS

Name Null? Type
PUBLICATION_ITEM NOT NULL VARCHAR(30)
QUERY TEMPLATE NOT NULL VARCHAR(10)

Configuring Query Templates

Using the AUTOTUNE feature of the Consperf utility, application developers and administrators can configure query templates.

Table 5-4 lists query templates that can be configured using the AUTOTUNE feature of the Consperf utility:

Table 5-4 Query Template Types

Query Template Type Description
BASEVIEW Use base view during synchronization
LDEL_1, LDEL_2, LDEL_3, LDEL_4 MGP templates for logical deletes
LINS_1, LINS_2, LINS_3 MGP templates for logical inserts
LG_1, LG_2, LG_3 MGP templates for log updates

5.5 Analyzing Mobile Server Objects for Cost Based Optimizer

The Consperf utility enables users to analyze Mobile Server objects so that the cost based optimizer can determine the appropriate query plans. Inaccurate statistics could be gathered because certain objects such as map tables and log tables may not contain any data. To counter this problem, the Consperf utility automatically simulates the appropriate data load before generating statistics.

5.6 Usage Model

Using the command line, the administrator and application developers can execute the Consperf utility. Users can call this utility as a win32 executable or as an executable java class. To call the Consperf utility, users must perform the following:

Win32 Executable

c:\consperf>consperf.exe [args...]

Java Class

c:\consperf>java oracle.lite.sync.profiler.consperf [args...]

Two different types of arguments are passed to the Consperf utility. They are:

  1. Assign: <argN>=<valueN> - specified argument with an associated value.

  2. Switch: <argN> - specified argument turns on functionality.

Figure 5-1 displays a sample output after running the Consperf utility at the command line.

Figure 5-1 Sample Output Using Consperf at the Command Line

Sample output using consperf at the command line

Note:

The Consperf utility requires the Oracle JDBC Driver 8.1.7 or a later version to be available in the classpath. The classpath must also include the webtogo.jar file.

Analyzing and Using Consperf Results

As an application developer, you must use the Consperf utility to analyze every publication defined by you. Based on the information contained in the timing and explain plan output files, you must identify potential trouble areas, and make the necessary modifications to ensure acceptable performance levels of the publication. If acceptable performance cannot be achieved through the available query templates, you must implement a "Customized Compose" or "MyCompose" feature. This is an advanced feature, and enables you to implement your own application logic to determine server changes that should be downloaded to each client.

Troubleshooting Publication Performance Issues

As an administrator, you must use the Consperf utility to troubleshoot performance issues that may arise during day to day usage of the Consolidator. As the amount of data and the number of clients increases, unforeseen performance problems may arise. You can use the AUTOTUNE functionality to overcome some of these issues. However, if an acceptable query template is not available, it may be necessary for the application developer to implement a "Customized Compose" or "MyCompose" for publication items with poor performance levels. This is an advanced feature, and enables the application developer to implement his own application logic to determine server changes that should be downloaded to each client.

For more information on the Customized Compose or MyCompose feature, see the Mobile Server Synchronization Guide.

5.7 Consperf Parameters

The Consperf utility accepts many parameters. Some of these parameters are required while the others are optional.

Table 5-5 lists valid Consperf parameters and their corresponding descriptions:

Table 5-5 Consperf Parameters

Name Type Required Default UOM Description Sample format and Usage Notes
MOBILESCHEMA Assign Yes N/A N/A Specifies the Mobile Server schema owner. MOBILESCHEMA=mobileadmin
MOBILEPASSWD Assign Yes N/A N/A Specifies the Mobile Server schema password. MOBILEPASSWD=manager
SERVICENAME

or

CONNECTSTRING

Assign Yes N/A N/A TNS Service Name - this specifies the Oracle sql*net service name and uses the thick JDBC Driver

or

Connect String - this specifies the Oracle connect string and uses the thin JDBC Driver.

SERVICENAME=webtogo.world

or

CONNECTSTRING=host:1521:sid

PUBLICATION

or

APPLICATION

Assign Yes N/A N/A Specifies the name of the publication to be profiled.

or

Specifies the name of the application to be profiled.

PUBLICATION=t_sample11

or

APPLICATION=orders

ITERATIONS Assign No 2 N/A Specifies the number of times to execute template queries for average. ITERATIONS=5
CLIENTID Assign Yes N/A N/A Specifies the name of the client to be profiled. CLIENTID=s11u1
TOUTFILE Assign No <none> N/A Specifies the name of the output file for timing data. Must be specified for timing to be executed. TOUTFILE=tout.log
POUTFILE Assign No <none> N/A Specifies the name of the output file for explain plan data. Must be specified for explain plans to be executed. POUTFILE=pout.log
PUBITEMLIST Assign No <all> N/A Specifies list of publication items to process. The default is all publication items in the publication. PUBITEMLIST=pi1,pi2,pi3,…,piN
SKIPPUBITEMLIST Assign No <none> N/A Specifies list of publication items to skip. SKIPPUBITEMLIST=pi1,pi2,pi3,…,piN
TIMEOUT Assign No 10 Seconds Specifies the query timeout value. This is the amount of time Consperf will wait before it cancels a query. TIMEOUT=20
OPTIMIZER Assign No db CHOOSE

RULE

COST

Specifies the optimizer mode to use within Oracle. OPTIMIZER=rule
UPDATECOUNT Assign No 5 # records Specifies the number of records to mark as dirty during synchronization. UPDATECOUNT=100
MAXLOG Assign No 5000 # records Specifies the number of records to put in the log table. Simulates the transaction log. MAXLOG=10000
AUTOTUNE Switch No <off> N/A Enables auto-tune. AUTOTUNE
CLEARTUNE Switch No <off> N/A Clears existing auto-tune results. AUTOTUNE
TOLERANCE Assign No 20 Seconds Specifies the auto-tune tolerance. A template must be faster by this amount before it replaces the default template. TOLERANCE=60
ORDERBYPUBITEM Switch No <off> N/A Orders all output by publication item name. ORDERBYPUBITEM
GATHERSTATS Switch No <off> N/A Gathers optimizer statistics on all mobile server objects. GATHERSTATS

MGP compose MUST be disabled while Consperf analyzes objects. Consperf blocks this automatically, but the safest approach is to manually stop the MGP process before running Consperf with the GATHERSTATS option. If Consperf fails while gathering statistics, users must re-run CLEARSTATS before starting the MGP process again.

CLEARSTATS Switch No <off> N/A Removes optimizer statistics on mobile server objects. CLEARSTATS
SQLTRACE Switch No <off> N/A Enables Oracle sql trace. TKPROF can be used to analyze the resulting trace file. SQLTRACE
TRACE Switch No <off> N/A Enables trace level 0. TRACE
TRACEALL Switch No <off> N/A Enables trace level 0 and 1. TRACE_ALL