The Oracle Lite Blog

When you start designing an Oracle Lite solution, the very first thing you should think about after you determine that Oracle Lite is the path for your company, start thinking about performance. You are probably thinking, performance? This is Oracle Lite. I shouldn’t have to worry about performance tuning. Well, the truth of the matter is Oracle Lite, without the proper care for the SQL in the publication items, size of the tables being synchronized, and number of users your application is synchronizing, you could quickly find yourself in a heap of trouble and you will quickly be looking for an experienced DBA or an Oracle Lite expert to save you. When an Oracle Lite publication item is published, 5 tables are created on the back end database. They are:

• CVR$ - This table is the version table. This table maps back to your base table and indicates what DML operation and version has occurred on a record.
• CLG$ - This table is the log table. The first pass of the MGP compose process dirty rows in the version table are copied to log table. The version table rows are then cleared as being dirty.
• CEQ$ - This the the error queue table. Any rows that encounter errors during the apply phase are placed in the error table. The error message corresponding to the error is placed in the mobileadmin schema C$EQ table.
• CFM$ - This is the in queue. Rows that are dirty (new/updated/deleted) on the client are placed in this table during a replication session. They are stamped with the version number of the corresponding row on the CVR$PublicationItemName table. Once the have been applied to the base table, they are deleted.
• CMP$ - This is the out queue. You may also refer to these tables as the mapped tables. Rows that need to be sent to the client are placed in this queue. The rows in this table persist and provide a record of what has been sent to the client.

The culprit of your performance issues will be in your out queue. Tables in the out queue can grow to be quite large. 10 Million records. 100 Million records. All depends on how big your application is and how many users you have. With each user you add to your application, the map tables will grow by the number of records that user is associate to by the snapshot predicate. When your application has added enough users that these tables become large, you can start thinking about partition mapping.  No, partition mapping isn’t the same as partitioning a table.  What happens is a process that takes your CMP$PublicationItem table and divides it by a number you specify.  The partition mapping process then goes out and starts adding users to these new map tables.  So, if you have 100 users and you create a partition map to the 10th degree, you will have 10 Mapped tables with 10 users per mapped table.  So now each table is 1/10th (approximately) the original size of the publication mapped table.

If you have an experienced DBA on hand, he should have already pointed out to you that the SYNCSERVER table space was created on $ORACLE_HOME and you are quickly running out of disk space.  Once you have straightened out that mess, (Can be avoided by creating the SYNCSERVER table space prior to running the repwizard), you will notice that all tables and indexes are under the same table space.  If you are running ASM as your volume manager, this is not an issue as ASM will move your indexes and tables around your disk array evenly.  If you do not have a volume manager in place, ensure that you go through an exercise where you distribute your tables space, tables, and indexes around your disk array.  As mentioned, DBAs have probably already noticed this and have come knocking on your door asking you what you are doing.

Have you used CONSPERF to analyze your timing and explain plans for your MGP and SYNC?   Consperf will choose the best query with the best timing and not the best cost.  Don’t get hung up on the cost of queries, it is all about timing.  Have you analyzed your base queries for your publication items as well?  If these queries take more then a few seconds to start returning data, then you will have issues with MGP and SYNC.  Tune these by adding appropriate indexes and query rewriting.  Make sure, if possible to remove any sorting or grouping from your select statements.

The last item I will talk about is shared maps.  Shared maps are publication items where a group of users are sharing the exact same data.  This helps by MGP only having to go through those publication items by the number of groups associate to your shared map.  These snapshots have to be read-only and if there is a bound subset parameter, it has to be the same for every member of the group.  Shared maps are often used for look-up tables.

 This concludes part 1 and part 2 will be about performance tuning on the client.