Oracle9i Lite Administration and Deployment Guide
Part No. A97376-01
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:
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:
Generate Timing Statistics for Publications
Generate Explain Plans for Publications
Automatically Tune Publication Properties
Analyze Mobile Server Objects for Cost Based Optimizer
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.
Query type codes are used in Consperf output files to identify various templates.
Table J-1 lists Consperf query types used to identify templates and customized callouts.
Table J-1 Consperf Query Types
|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.|
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:
Analyzing... This section displays parameters specified for generating timing statistics data and properties associated with the Mobile Server's installation.
Sync Output... This section displays timing statistics for query types associated with the synchronization process. Based on the total time, publication items can be ordered from slowest to fastest.
MGP Output... This section displays timing statistics for query types associated with the MGP process. Based on the total time, publication items can be ordered from slowest to fastest.
Subscription Properties... This section displays subscription properties associated with each publication item. All publication items in this section can be ordered alphabetically.
Table J-2 lists publishing items that can be ordered alphabetically:
Table J-2 Ordering Publishing Items Alphabetically
|a||Profiled||Indicates whether the publication item has been profiled with the Auto-Tune parameter enabled.|
|b||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.|
|c||Sub #||Indicates the number of records subscribed by the specified client.|
|d||Dirty #||Indicates the number of records currently marked as dirty for the specified client.|
|e||POPLOG||Indicates the number of records generated in the transaction log.|
|f||DSYNC||Indicates the number of records simulated in the outbound queue for the specified client in the Sync phase.|
|g||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.
Figure J-1 displays the sample output defined in the four timing statistics sections.
Figure J-1 Sample Output for Timing Statistics
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.
Figure J-2 displays the sample output that defines the publication item and associated query templates.
Figure J-2 Sample Output for Explain Plans for Publications
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 J.6, "Usage Model".
Table J-3 displays Tuning results that are stored in the Mobile Server repository:
Table J-3 C$CONSPERF Tuning Results
Using the AUTOTUNE feature of the Conspire utility, application developers and administrators can configure query templates.
Table J-4 lists query templates that can be configured using the AUTOTUNE feature of the Consperf utility:
Table J-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|
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.
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:
c:\consperf>java oracle.lite.sync.profiler.consperf [args...]
Two different types of arguments are passed to the Consperf utility. They are:
<argN>=<valueN> - specified argument with an associated value.
<argN> - specified argument turns on functionality.
Figure J-3 displays a sample output after running the Consperf utility at the command line.
Figure J-3 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.
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.
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.
The Consperf utility accepts many parameters. Some of these parameters are required while the others are optional.
Table J-5 lists valid Consperf parameters and their corresponding descriptions:
Table J-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|
TNS Service Name - this specifies the Oracle sql*net service name and uses the thick JDBC Driver
Connect String - this specifies the Oracle connect string and uses the thin JDBC Driver.
|PUBLICATION||Assign||Yes||N/A||N/A||Specifies the name of the publication to be profiled.||PUBLICATION=t_sample11|
|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|
|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|
|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.||
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|