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.
Loading ...
6 responses so far ↓
1 Santiago wrote on Feb 13, 2009 at 5:08 am
Greg,
For Oracle Lite R3:
In order to user shared maps, It’s necessary to use AddPublicationItem/SetPublicationItemMetadata API……….. or I can only configure grouping function in Mobile Workbench??????
Santiago,
2 rekounas wrote on Feb 13, 2009 at 6:09 am
When you add your publication item, if you are using the APIs, all you have to do is set a parameter to true:
addPublicationItem(
PUBLICATION,
WTGPI_SHARED_ITEM,
null,
false,
"S"
null,
1,
null,
true,
true,
null);
3 Santiago wrote on Feb 13, 2009 at 6:36 am
I already used Mobile Workbench to define everything.
So, I need to know if I have to drop everything of Mobile Workbench, and work only with API’s in order to use shared maps….or……
Is it possible to use Mobile Workbench to define publications and publication items. Then use API’s to configure shared maps over the publication and publication items already created in Workbench???
4 rekounas wrote on Feb 13, 2009 at 7:09 am
No. Mobile Workbench is just a GUI for the APIs to work with. You can run removePublicationItem method before the addPublicationItem method and it will make the publication item shared. You should also tun the reInstantiatePublicationItem method for the publication item after you run the methods.
5 Santiago wrote on Feb 13, 2009 at 8:33 am
Thanks Greg.
Now I see that I have to work hard with API’s.
I have a final question.
If I should use API’s in order to use shared maps…Why there is an entry for grouping function in Mobile Workbench (publication item association with publication)???
6 rekounas wrote on Feb 13, 2009 at 9:36 am
I was told that if a publication item was read only and if there was no data subsetting, then the item would automatically become a shared map. You can also use shared maps with groups that share the same data subset parameter as well. I would think that it is for this functionality as well.
Personally, I only use the Workbench to get the a quick application up and running. Any production system I work on, I implement the APIs because of enhanced flexibility that you have by writing your own code.
Leave a Comment