The following messages are observed in target event log when Post slows down on one table:
Error 2018-06-08 17:34:37.662005 3418 7 Poster: DML operation in session 3 has taken 3601 secondsTable "OWNER"."TABLE_NAME" has a bitmap index. Bitmap indexes are not recommended in OLTP. Please convert bitmap index (INDEX_NAME) to a B-Tree index. Poster Exiting. (posting from SID1, queue queue_name, to SID2) [module opo]
Info 2018-06-08 17:34:37.982599 957 1 Poster exited with code=1, pid = 3418 (posting from SID1, queue queue_name, to SID2)
Info 2018-06-08 17:34:37.986241 797 1 Poster launched, pid = 797 (posting from SID1, queue queue_name, to SID2)
The messages aptly remind the avoidance of bitmap indexes in an OLTP environment wherever possible. If possible, one may want to convert the bitmap index(es) to a B-Tree index. There are trade offs involved when so one may want to weigh those. The following excerpt from the manual SharePlex Installation and Setup for Oracle Source explains it:
Bitmap indexes
For performance purposes, avoid the use of bitmap indexes while the Post process is applying the data. These indexes can adversely affect the performance of the Post process.
If you must use bitmap indexes on target tables, weigh their benefit for queries against the their impact on the transactions applied by Post:
* When Oracle adds, updates or deletes a bitmap entry, it effectively locks all the rows associated with the bitmap segment.
* A bitmap segment can contain references to hundreds of rows. As a result, changes made by different Post sessions (there is a Post session for every session on the source system) can block each other if their work updates bitmap entries in the same bitmap segment.
* To proceed, Post must detect and resolve the blocking, which delays posting significantly if the number of locks is high.
* In general, frequent inserts by multiple concurrent sessions into a table with bitmap indexes will incur lock conflicts, but random update and delete activity on such a table will not. SharePlex follows the Oracle recommendation to have a bitmap index on a more static table.
NOTE: Replicating bitmap indexes is not recommended. Every time you change a table with a bitmap index, the index is rebuilt. This associated cost (Oracle time and resources) to rebuild is added to your SQL UPDATE statement.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center