0

I'm trying to refactor a slow Oracle database, where the developers naively chose a VARCHAR2 UUD4 as the primary key for what is really an append only time series dataset. I've found that the uniqueness check for the primary key (frustratingly I can't quite convince the senior developer to drop that uniqueness constraint) and foreign key checks for UUID4s are the cause of poor performance.

This is the first time I've touched Oracle in my career, so I'm unfamiliar with how it works. In MySQL the primary key absolutely effects how the table is allocated physically, but I am getting mixed messages about how it affects things in Oracle. Obviously there are index-optimised-tables, but those have to explicitly set.

Looking at the index statistics for the primary key, the clustering factor is nearly as high as the row count. Did the random primary key have anything to do with that?

If I have a random primary key, does it affect how the row is physically allocated? For instance, would it mean a similarly random allocation of the row to a block?


If so, how can I change that so the database will try to cluster the writes to a smaller set of blocks (to reduce random IO)? Would moving to a 'clustered' identifier like a UUID1 improve insert performance?

3
  • "and foreign key checks are the cause of poor performance" - more often than not, this means you are missing an index Mar 7 at 14:31
  • There's indexes on all of the foreign keys. However, all of the primary keys in this database are UUID4s stored as a VARCHAR2. Which definitely effects performance, because indexes values are clustered, meaning that random blocks get written to. Also, VARCHAR2 is a UTF-8 string, meaning that the database doesn't know the byte length of the string ahead of time; if it knew it was 32bytes, it would be able to SIMD operations to make searching a lot faster.
    – Rol
    Mar 7 at 14:34
  • "Looking at the index statistics for the primary key, the clustering factor is nearly as high as the row count. Did the random primary key have anything to do with that?" Yes. Absolutely. But that does not mean anything wrt your performance issue. It would be if the PK was some sequential/range data, like dates, and you had queries selecting rows in a range of dates: those queries would probably not use the index. But in your case, given the PK is essentially a random distributed value, that will never happen: only direct accesses will happen. Mar 8 at 14:39
1

No, the values of a row have no implications for where Oracle will store it in a heap table. The only real exception is that if your row is going to be huge, Oracle will probably have to put it on a new block.

What makes you think the foreign key check is the cause for your poor performance? What’s the process that is too slow and how slow is it? A foreign key check on an insert requires very little IO as it’s just an existence check against an indexed column. There is usually much more significant low hanging fruit.

10
  • I found that with just process of elimination. Removing the FK constraint causes a 8-10x speedup.
    – Rol
    Mar 7 at 14:36
  • An insert takes about 35ms with foreign keys. And about 3.5 without.
    – Rol
    Mar 7 at 14:41
  • It's a pretty big table (50gb) that's really only written to.
    – Rol
    Mar 7 at 14:42
  • 31.5ms is pretty long to do a simple index lookup. There is going to be caching implications depending on your insert patterns but even if it’s 100% physical IO, something is wrong. 3.5ms is also pretty slow for an insert. What storage are you using? It sounds like you’re on slow spinning disks which will tend to cause serialization when multiple threads want to read data. Mar 7 at 15:38
  • 1
    I would definitely look at your storage hardware, you should be easily managing 1ms inserts of single values even with multiple foreign key checks on modern hardware. It’s a massive leap of faith to say you don’t need foreign keys. You would be in for a large headache when you find out that some DML was able to logically corrupt your data - and you don’t trust your developers already... Mar 7 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.