SharePlex and Index Criticality

May. 30th 2014

Shareplex Index Criticality

 

The Dell Shareplex database replication software performs best when the tables in the databases have proper indexes. Every table in replication should have a primary key or unique index. A table having a primary key or unique index will post more quickly than one without.

Oracle supplemental logging must be enabled for Shareplex to work. Though minimal supplemental logging is adequate, enabling the primary key and unique index logging is best. This is done with the following statement: alter database add supplemental log data (primary key, unique index) columns. It increases the amount of information written to the online and archive redo logs, but it is well worth it. It prevents extra hits to the database by the Shareplex READ process, reducing overhead on the primary or source database.

On the secondary or target database, Shareplex posts the INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK statements. Each statement logged in the redo log files on the source database is posted to the target database to keep them in synch. The two statements which need indexes are the UPDATE and DELETE. Shareplex must find each individual row before performing the UPDATE or DELETE operation. With a primary key or unique index, the UPDATE or DELETE statements execute very quickly. If a table does not have a primary key or unique index, Shareplex finds the row by putting all the columns of the table in the WHERE clause of the UPDATE or DELETE statement. On a small table this can be OK, but on a large table, it will usually cause a full table scan, slowing down the POST process and causing large backlogs. If a table has too many indexes, the INSERT command can be slow, but this usually does not cause a backlog to occur.

The Shareplex COMPARE/REPAIR process compares a table on the source system with a table on the target system. Having a primary key or unique index is good as Shareplex must sort the tables in the same order to do the COMPARE or REPAIR. Recently, I ran into a rather unusual case where a table had 580 columns and no primary key or unique index. As there were so many columns, the programmer created the table with the columns alphabetized for ease of use. Unfortunately, most of the columns were NULL. Additionally, Shareplex only sorted on the first 256 columns as the COMPARE/REPAIR process was originally written for Oracle 7. Each time I ran the REPAIR process, the NULL columns sorted in a different order on the source and target databases. It would INSERT and DELETE rows to make them in synch. I would immediately run it again and the same thing would happen. Shareplex development patched the COMPARE/REPAIR process to sort on over 1000 columns and it finally worked. The table was never out of synch but it appeared to be due to poor indexing and a sub-optimal feature in Shareplex.

Another way a primary key can be used in the Shareplex COMPARE/REPAIR process is by using the WHERE clause option. Tables which are very large, 100s of millions of rows, often take a very long time as they need to sort in the TEMP tablespace on source and target before starting the actual comparison of the rows. Smaller tables often sort in memory if the PGA is set large enough. Using the WHERE clause option (especially easy if the primary key is based on a sequence) the table can be broken into manageable chunks which ran sort in memory. The individual compare commands can be put in to a file and processed with a script if desired.

Sometimes a table gets corrupted, truncated, or horribly out of synch on the target database. In these cases, the Shareplex COPY command or a manual Oracle datapump export/import in tandem with the FLUSH command can be used to re-synch the table. Before starting, check the number of indexes on the table. If there are more than a couple, it will probably be faster if you drop all the indexes on the target table except the primary key, then rebuild them after the COPY or datapump export/import is completed.

 

Author: Mark Bochinski, LeadThem Consulting Senior SharePlex DBA

Posted by LeadThem Consulting | in SharePlex | Comments Off on SharePlex and Index Criticality