|
Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
 Previous |
 Next |
|
Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
|
Previous |
Next |
The following sections describe the methods you can manage the performance of your use of Oracle Lite Database:
Section 16.2, "Determine Execution of SQL Query With EXPLAIN PLAN"
Section 16.3, "Synchronization Performance Tuning for the Mobile Server"
The following sections describe how you can manipulate the synchronization performance:
During synchronization, files are created within the Mobile Server directories for synchronization management. This may cause space problems on the WinCE device. To counter space constraints for the storage card on the WinCE platform, you can designate the Temp directory for all synchronization temporary files by adding the following entry in the ALL DATABASES section in the POLITE.INI or POLITE.TXT file.
TEMP_DIR=\Storage Card\Temp
It is very common for publications to contain publication items that are used specifically for lookup purposes. In other words, the server may change these snapshots, but the client would never update them directly. Furthermore, many users often share the data in this type of snapshot. For example, there could be a publication item called zip_codes, which is subscribed to by all Mobile users. The main function of Shared Maps is to improve scalability for this type of publication item by allowing users to share record state information and, thus, reduce the size of the resulting replication map tables.
Shared Maps can also be used with updatable snapshots if the developer is willing to implement their own conflict detection and resolution logic.
This section discusses the shared maps feature in terms of concepts and performance attributes.
Shared maps shrink the size of map tables for large lookup publication items and reduce the MGP compose time. Lookup publication items contain "lookup" data that is not updatable on the clients and that is shared by multiple subscribed clients. When multiple users share the same data, their query subsetting parameters are usually identical.
For example, a query could be the following:
SELECT * FROM WHERE EMP WHERE DEPTNO = :dept_id
In the preceding example, all users that share data from the same department have the same value for dept_id. The default sharing method is based on subscription parameter values.
In the following example, the query is:
SELECT * FROM WHERE EMP WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE EMPNO = :emp_id )
In this example, users from the same departments still share data. Their subsetting parameters, however, are not equal because each user has a unique emp_id. To support the sharing of data for these types of queries (as illustrated by the example), a grouping function can be specified. The grouping function returns a unique group id based on the client id.
There is also another possible use for shared maps. It is possible to use shared maps for shared updatable publication items. This type of usage, however, requires implementation of a custom dml procedure that handles conflict resolution.
The performance of the MGP compose cycle is directly proportional to:
NC * NPI
where:
NC = number of clients.
NPI = number of publication items that must be composed.
With shared maps, the length of the MGP cycle is proportional to: NC*(NPI - NSPI) + NG*NSPI
where:
NSPI = number of shared publication items.
NG = number of groups.
Note that if NG = NC, the MGP performance is similar in both cases. However, with fewer groups and more shared publication items, the MGP compose cycle becomes faster.
Also note that map storage requirements are governed by the same factors.
To set up a publication item to be shared, use the AddPublicationItem API and enable the shared flag. It is also possible to toggle the shared property of a publication item once it is added to the publication with the SetPublicationItemMetadata API. Both the AddPublicationItem API and the SetPublicationItemMetadata API allow users to specify a PL/SQL grouping function. The function signature must be the following:
( CLIENT in VARCHAR2, PUBLICATION in VARCHAR2, ITEM in VARCHAR2 ) return VARCHAR2.
The returned value must uniquely identify the client's group. For example, if client A belongs to the group GroupA and client B belongs to the group GroupB, the group function F could return:
F ('A','SUBSCRIPTION','PI_NAME') = 'GroupA'
F ('B','SUBSCRIPTION','PI_NAME') = 'GroupB'
The implicit assumption of the grouping function is that all the members of the GroupA group share the same data, and that all the members of the GroupB group share the same data.. The group function uniquely identifies a group of users with the same data for a particular PUBLICATION ITEM.
For the query example in Section 16.1.2.1, "Concepts", the grouping function could be:
Function get_emp_group_id ( clientid in varchar2, publication in varchar2, item in varchar2 ) return varchar2 is group_val_id varchar2(30); begin select DEPTNO into group_val_id from EMP where EMPNO = clientid ; return group_val_id; end;
NOTE: This function assumes that EMPNO is the Consolidator Manager client id. If the group_fnc is not specified, the default grouping is based on subscription parameters.
Shared maps are not compatible with raw id based clients prior to 5.0.2.
Those clients are supported; however, the map data is private until the clients migrate to 5.0.2 or later.
The migration of the existing mobile server schema to 10g must be done in the following steps to minimize the number of client complete refreshes.
Run one cycle of MGP.
The clients must sync with the server to get the latest changes prepared by the MGP.
Stop the Web server and MGP to migrate the server to 10g. This automatically sets all the nonupdatable publication items to shared items. If any shared publication items need to use grouping functions or any publication items need to change their sharing attribute, execute custom code that calls the appropriate Consolidator Manager API. See the SetPublicationItemMetadata API in Section 16.1.2.3, "Usage".
The ShrinkSharedMaps Consolidator Manager API must be called to set the clients to use shared map data and remove old redundant data from the maps.
Start the Web server and MGP.
With priority-based replication, you can limit the number of rows per snapshot by setting the flag Priority to 1 (the default is 0).
For example, if you have a snapshot with the following statement:
select * from projects where prio_level in (1,2,3,4)
With the Priority flag set to 0 (the default), all projects with prio_level 1,2,3,4 will be replicated.
In a high priority situation, the application can set the flag to 1, which will cause MGP to check for Restricting Predicate. A Restricting Predicate is a conditional expression in SQL. The developer can set Restricting Predicate in the AddPublicationItem() method, as in the following example:
prio_level = 1
MGP appends (AND) the expression to the snapshot definitions when composing data for the client. In this case, the high priority statement would be:
SELECT * FROM projects where prio_level in (1,2,3,4) AND prio_level = 1; // a restricting predicate snapshot
In this case, only projects with level =1 will be replicated to the client.
This advanced feature is available only through the Consolidator Manager API. It is not available through the Packaging Wizard.
To summarize, there are two steps to enable this feature:
Provide a restricting predicate expression in the AddPublicationItem() function.
Set the PRIORITY flag to 1 in the Mobile Sync API.
|
Note: You cannot use fast refresh synchronization with high priority. |
This feature allows complex publication item queries to be cached. This applies to queries that cannot be optimized by the Oracle query engine. By caching the query in a temporary table, the Sync Server template can join to the snapshot more efficiently.
Storing the data in a temporary table does result in additional overhead to MGP operation, and the decision to use it should only be made after first attempting to optimize the publication item query to perform well inside the Sync Server template. If the query cannot be optimized in this way, the caching method should be used.
The following example is a template used by the MGP during the compose phase to identify client records that are no longer valid, and should be deleted from the client:
UPDATE pub_item_map map
SET delete = true
WHERE client = <clientid>
AND NOT EXISTS (SELECT 'EXISTS' FROM
(<publication item query>) snapshot
WHERE map.pk = snapshot.pk);
In this example, when <publication item query> becomes too complex, because it contains multiple nested subqueries, unions, virtual columns, connect by clauses, and other complex functions, the query optimizer is unable to determine an acceptable plan. This can have a significant impact on performance during the MGP compose phase. Storing the publication item query in a temporary table, using the publication item query caching feature, flattens the query structure and enables the template to effectively join to it.
The following API enables publication item query caching.
Syntax
public static void enablePublItemQueryCache(String name)
throws Throwable
The parameters for enablePublItemQueryCache are listed in Table 16-1:
Table 16-1 The enablePubItemQueryCache Parameters
| Parameters | Description |
|---|---|
name
|
A string specifying the name of the publication item. |
Example
consolidatorManager.enablePubItemQueryCache("P_SAMPLE1");
If you are using an input string from the input parameter argv array, cast it to a String, as follows:
consolidatorManager.enablePubItemQueryCache( (String) argv[0]);
The following API disables publication item query caching.
Syntax
public static void disablePubItemQueryCache(String name)
throws Throwable
The name parameter for disablePubItemQueryCache is listed in Table 16-2:
Table 16-2 The disablePubItemQueryCache Parameters
| Parameters | Description |
|---|---|
name
|
A string specifying the name of the publication item. |
Example
consolidatorManager.disablePubItemQueryCache("P_SAMPLE1");
Sync Server database objects called map tables are used to maintain the state for each Mobile client. If there are a large number of clients, and each client subscribes to a large amount of data, the map tables can become very large creating scalability issues. Using the following APIs, map tables can be partitioned by client id, making them more manageable.
The API allows you to create a map table partition, add additional partitions, drop one or all partitions, and merge map table partitions. Map table partitions can be monitored using the ALL_PARTITIONS database catalog view.
|
Note: This form of partitioning is not related to the partition functionality provided by Oracle Server, and is used exclusively by Oracle Database Lite 10g. |
Creates a partition for the referenced publication item map table. If there is data in the map table, it is transferred to the partition being created. After the partition has been successfully created, the map table can be truncated to remove redundant data using the SQL command TRUNCATE TABLE.
Syntax
public static void partitionMap
(String pub_item,
int num_parts,
String storage,
String ind_storage) throws Throwable
The parameters of partitionMap are listed in Table 16-3.
Table 16-3 The partitionMap Parameters
| Parameter | Definition |
|---|---|
pub_item
|
The publication item whose map table is being partitioned. |
num_parts
|
The number of partitions. |
storage
|
A string specifying the storage parameters. This parameter requires the same syntax as the SQL command CREATE TABLE. See the Oracle SQL Reference for more information.
|
ind_storage
|
A string specifying the storage parameters for indexes on the partition. This parameter requires the same syntax as the SQL command CREATE INDEX. See the Oracle SQL Reference for more information.
|
Example
consolidatorManager.partitionMap("P_SAMPLE1", 5, "tablespace mobileadmin", "initrans 10 pctfree 70");
Adds a partition for the referenced publication item's map table. If there is data in the map table, it is transferred to the partition being created. After the partition has been successfully created, the map table can be truncated to remove redundant data using the SQL command TRUNCATE TABLE.
Syntax
public static void addMapPartitions
( String pub_item,
int num_parts,
String storage,
String ind_storage) throws Throwable
The parameters of addMapPartitions are listed in Table 16-4:
Table 16-4 The addMapPartitions Parameters
| Parameter | Definition |
|---|---|
pub_item
|
The publication item whose map table is being partitioned. |
num_parts
|
The number of partitions. |
storage
|
A string specifying the storage parameters. This parameter requires the same syntax as the SQL command CREATE TABLE. See the Oracle Database Lite SQL Reference for more information.
|
ind_storage
|
A string specifying the storage parameters for indexes on the partition. This parameter requires the same syntax as the SQL command CREATE INDEX. See the Oracle Database Lite SQL Reference for more information.
|
Example
consolidatorManager.addMapPartitions("P_SAMEPLE1",5,"tablespace mobileadmin","initrans 10 pctfree 40");
|
Note: Map Partitions are created only for existing users. New users are placed in the original map table. |
Drops the named partition. In the following example, the partition parameter is the name of the partition. Partition names must be retrieved by querying the ALL_PARTITIONS table view CV$ALL_PARTITIONS since partitions are named by Data Synchronization.
Syntax
public static void dropMapPartition( String partition) throws Throwable
Example
consolidatorManager.dropMapPartition("MAP101_1");
Drops all partitions of the map table for the named publication item.
Syntax
public static void dropAllMapPartitions( String pub_item) throws Throwable
Example
consolidatorManager.dropAllMapPartitions("P_SAMPLE1");
Merges the data from one partition into another. Partition names must be retrieved by querying the ALL_PARTITIONS table view CV$ALL_PARTITIONS, since partitions are named by Data Synchronization.
Syntax
public static void mergeMapPartitions
( String from_partition,
String to_partiton) throws Throwable
Example
consolidatorManager.mergeMapPartition(""MAP101_1", "MAP101_2");
If you want to access data on the local client Oracle Lite database, then you can use the EXPLAIN PLAN to determine the performance of your SQL query execution on the Oracle Lite database. To execute a SQL statement, Oracle might need to perform several steps. Each of these steps either physically retrieves rows of data from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan, which includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method. The execution plan shows you exactly how Oracle Database Lite executes your SQL statement.
The components of an execution plan include the following:
An ordering of the tables referenced by the statement.
An access method for each table mentioned in the statement.
A join method for tables affected by join operations in the statement.
The EXPLAIN PLAN command stores the execution plan chosen by the Oracle Database Lite optimizer for SELECT, UPDATE, INSERT, and DELETE statement. See the section "Tuning SQL Statement Execution with the EXPLAIN PLAN" in the Oracle Database Lite SQL Reference for full details on how to manually create an EXPLAIN PLAN.
The following sections provide details on how to tune the synchronization on the Mobile Server for your applications:
Section 16.3.1, "Tuning Queries to Manage Synchronization Performance"
Section 16.3.3, "Configuring Back-End Oracle Database to Enhance Synchronization Performance"
Section 16.3.4, "Configure Your Mobile Server to Increase Synchronization Performance"
Section 16.3.5, "Designing Application Tables and Indexes for Synchronization Performance"
You can increase synchronization performance by monitoring the performance of the SQL queries in your applications. The following sections provide details on how to tune your queries:
You should avoid using database query constructs that prevent a view from being mergable, as publication item queries that use non-mergable views do not perform well. Examples of such constructs are union, minus, and connect by. For more information on mergable views, see the Oracle database documentation.
Once you have defined your application, use the consperf utility to profile the performance of the publication item queries. Mobile Server does not execute your publication item queries directly; instead the query is wrapped into a template query, which is executed by Mobile Server. The template query may have an unexpected query execution plan, resulting in poor performance. The consperf utility generates an EXPLAIN PLAN execution plan for those template queries, allowing you to tune your publication item query for best performance. In addition, consperf generates timing information for the execution of all template queries, so that you can identify bottleneck queries. For more information on the consperf utility, see the "Analyzing Performance of Publications With the Consperf Utility" section in the Synchronization chapter in the Oracle Database Lite Administration and Deployment Guide.
You must make sure that the optimizer picks the correct execution path when you either are using the cost-based optimizer or you have set the optimizer settings to choose. The optimizer can pick the correct execution path only when all of the tables are properly analyzed and statistics have been gathered for these tables.
The Mobile Server uses temporary tables during synchronization. Once a number of users have been created, and they have synchronized with Mobile Server, run consperf with the gatherstats option to generate the statistics information for the temporary tables. For more information on the consperf utility, see the "Analyzing Performance of Publications With the Consperf Utility" section in the Synchronization chapter in the Oracle Database Lite Administration and Deployment Guide.
It is recommended that you run Mobile Server and the Oracle database on different machines. If possible, use multi-CPU machines for both Mobile Server and the Oracle database. Run the Oracle database should in dedicated server mode; use of the multi-threaded server is not recommended.
You can configure the Oracle Database in such a way as to enchance your Mobile Server synchronization performance, as follows:
Proper tablespace layout across multiple disks can significantly improve the performance of Mobile Server data synchronization, as it reduces movement of the disk heads and improves I/O response time.
The SYNCSERVER tablespace is the default tablespace. You can precreate this tablespace yourself and decide where it resides. For more information, see the SYNCSERVER Tablespace Layout section in the Performance chapter in the Oracle Database Lite Administration and Deployment Guide.
During synchronization, map tables are used extensively. Map tables are internal tables, and have table names using the following pattern: CMP$pub_item_name. Each map table has four separate indexes. By default, both map table and indexes are created in the default tablespace SYNCSERVER.
You can improve performance if you move the map table indexes to a different disk than the map table itself. Create a separate tablespace (for example: MAPINDEXES) on a different disk and manually move all indexes. Because the process of moving the indexes requires you to drop and re-create the indexes, you should move the index before many users have synchronized. Otherwise recreating the indexes on the map tables may be very time consuming, as map tables grow with the number of users who have synchronized.
To move the indexes on a map table, do the following:
Identify all indexes on the map table (CMP$pub_item_name). There are three or four indexes. Move all of them.
For each index, record the type of index and column lists.
If the index is a primary key index, then remove the primary key constraint on the map table.
Drop the index.
Recreate the index using the same name, type and column list. Use the storage clause in the create index statement to specify the new tablespace. You may also specify different storage parameters. Refer to the Oracle database documentation for more information on how to create indexes and storage clause parameters.
|
Note: Repeat step 3 through 5 for all other indexes on the map table. |
Tuning the database for Mobile Server is similar to any Oracle database tuning required for any query intensive applications. Configure the SGA to be as large as possible on your system to maximize the caching capabilities and avoid I/O wherever possible.
Tune your Oracle database with the following database parameters:
db_block-buffers
sort_area_size
log_buffers
Refer to the Oracle database tuning guide for more information on database tuning.
There are two parameters in the [CONSOLIDATOR] section of the webtogo.ora file for tuning synchronization.
MAX_THREADS
The MAX_THREADS parameter is used by the MGP and controls the number of concurrent threads. As a rule, do not set this higher than 1.5 times the number of CPUs on the database machine. For example, if your system has four CPUs, theb you should not set it higher than six.
MAX_CONCURRENT
The MAX_CONCURRENT parameter controls how many users can synchronize in parallel. Once the maximum number of concurrent synchronization requests is reached, additional requests block until one or more synchronization requests completes. If you do not set this parameter, then there is no maximum.
Each synchronization request requires a number of system resources, such as creating a database connection, using memory, and so on. If you have too many requests competing for the same resources, then the overall performance can be poor. Limiting the number of parallel requests improves the average response time.
Set this parameter if you notice that the synchronization performance is not linear. For example, if twice the number of parallel requests results in a synchronization time that is five times longer for each client, then you probably have resource contention. The value depends on your environment and should be determined on a trial and error basis.
Your clients may perform a large number of insert and delete operations on snapshots, and then synchronize their data changes with the Mobile Server. If this is the case, then consider placing the application tables and the indexes on those tables on separate disks.
