For option 2, how do they plan to sync the UUIDs across all the
sites? How do they expect to handle network latency? Is there going to be a
key master?
It may be functionally fine, but I see an operational nightmare.
Liz
Elizabeth Reen
CPB Database Group Manager
Service Now Group: CPB-ORACLE-DB-SUPPORT
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Chris Stephens
Sent: Thursday, September 13, 2018 5:17 PM
To: oracle-l
Subject: experience w/ UUID's for surrogate keys across entire schema?
we have an application that will be deployed at an unknown number of locations.
the application requires the ability to ingest data from every location into a
central repository. the application will also store data in non-oracle
databases at some locations. all locations will have the same schema. all
surrogate keys for all tables across all locations have to be unique. some of
these locations running Oracle will be "small" and some will require beefy RAC
systems to support the workload.
2 methods of satisfying this requirement are being considered:
1) use a multi-column or concatenated column PK with some sort of "site id" +
sequence based values for oracle systems and figure out the equivalent in the
other database products. To allow for scalability on busy RAC systems I
currently plan to recomment a site_id + inst_id + mod(session_id, <some
number>) + sequence value.
2) use UUID's generated in the application code for all surrogate keys
everywhere.
I'm being asked to weigh in on the advantages/disadvantages of both. Option 1
is ideal from an Oracle perspective if performance is primary concern (there
will be locations that will stress any hardware we throw at it). Option 2 is
preferred by the developers and I think is functionally fine.
I think the primary concern for option 2 is the completely random nature of
UUIDs and the fact that all these PK/Unq indexes will need to be fully cached
to maintain performance which will stress oracle's buffer management and result
in far more physical I/O for inserts as well as for other database activity
that gets aged out of buffer cache. I have explained that concern as best I can
but the developers are (justifiably asking for more concrete answers other than
"this will likely become a problem at some point if the application activity
exceeds the ability of Oracle, on the current hardware, to maintain active
blocks in the buffer cache". I am going to try and come up with a test to
demonstrate the degraded performance on our current 3-node 12.2 RAC system
(child's play for what eventual production and test systems will be). Does
anyone have suggestions on how to best set up that test and demonstrate the
downfalls of UUID approach at some currently unknown scale? Also, does anyone
have any experience using externally generated UUID's as surrogate keys in
Oracle? Good/bad/indifferent?
==
==
I'm most certainly reaching out to oracle-l more than ever before as these are
problems I've never had to deal with and truly appreciate all the people that
take the time to chime in.
Thanks!
Chris
