What purpose is served by the index HISTORY_LOG_ATTRIBUTE_RK? Can I drop this index and/or its constraint? What specifically will happen if I do?
I’m working on an upgrade from 9 to Cloud that includes a large data migration from Sales and Marketing to Embedded CRM, and I’m writing a lot of history logging when I insert the new records. Our whole business will be down while this runs, so it’s important to make this migration as fast as possible. Most of my experience in tuning Oracle databases has been with read-heavy systems, so it’s a new and interesting problem for me to optimize writes.
I set a couple of Oracle parameters to temporarily improve commit performance (COMMIT_LOGGING=BATCH and COMMIT_WAIT=NOWAIT). I plan to change these back before handing the system off to end users.
I’m running my migrations in parallel threads, so to avoid transaction contention, I set INITRANS to 4 on the history and history attributes tables and indexes, up from the default of 1 for the tables and 2 for the indexes. (On my next run, I’m going to try INITRANS values of 8 and 9, for the history tables and indexes, respectively, to further pare down the waits for block splits, setting the index one higher than the table for Oracle’s own internal locks.)
I’m still getting hit with a lot of “log buffer space” waits on HISTORY_LOG_ATTRIBUTE_RK, but then I realized…. Is this an unnnecessary index? You can’t put custom fields on this entity, and it’s not going to be referenced anywhere, so what’s the point?
