Skip to main content

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?

I’m also considering hash partitioning the remaining indexes. This article has some really good diagrams to explain why:

https://uhesse.com/2016/12/02/how-to-reduce-buffer-busy-waits-with-hash-partitioned-indexes-in-oracle/

Normally as you’re writing new history log entries, you’ll fill in values on the very right side of the B+tree, and concurrent transactions are going to fight for the same leaf blocks as they write the history entries with almost identical values…

1000001

1000002

1000003

1000004...

 

(Copying the image from that article)

Hashing the index key lets us pseudo-randomize a bucket in which to partition the index, reducing contention for the hot blocks:

 

Another common solution is to reverse the key, but that would make the indexes useless for sorts and range scans. At least with partitioning, the optimizer stands a good chance reading the necessary records off of each partition.

If there is no custom fileds on HistoryLogAttribute LU I think You can drop this index temporary. I am also thinking about disabling rowkey on this LU because on IFS9 it was possible to do that.

Reply


Terms & ConditionsCookie settings