RE: Design related question
Date: Mon, 11 Dec 2023 11:40:24 -0500
Message-ID: <b5b524a9-2919-4992-a2fb-1f5a3e28fe04_at_comcast.net>
First off, I agree that shooting from the hip is not optimal. There are items here that we do not have access to that can cause errors in our thinking.
Namely, you say 7-8 inserts per transaction, is that to 1 table of 8 tables? How many integrity constraints are there between these tables? GOD forbid, but is there a pile of stored procedures/functions/packages involved? Believe it of not that can become an issue. I managed a transactional database that one insert could take up to 45 minutes to process through a pile of PL/SQL and some 20 tables being inserted/updated. Interestingly enough the database was named PERFORM. On the other hand another database made 1-1000 inserts without any stored code and also took significant time to complete each transaction, as much as 5 minutes, due to many back and forth queries that were performed. Moving the back and forth into a database stored package reduced this to less than a 1sec. So how your data gets somewhere makes a difference.
Also don't underestimate what people asking questions can do to your database. People bashing the heck out of redo to rebuild a read consistent view is a pain and can become the largest part of your disk and CPU requirements. And yes it does at times become a "chicken & egg" mess to figure out.
You also talk about offloading data. This I assume will be a scheduled process which may have less impact on redo since it accesses historical data, but it does impact CPU, memory, and network resources. Last thing you want people noticing is that monthly process firing off for 2 days.
All in all, this is not a simple design question and you didn't mention what the audience for this data is. The larger it is the bigger the problem, the more you can offload to a reporting system the better. And since I'm writing this in just a few minutes I'm sure there are a lot more issues that you will face. One of which is transitioning the data to PostgreSQL, love that open source database as well as Oracle, but it comes with it's own issues. And you talk about doing this in AWS which is an issue all to itself. I've done Microsoft Cloud, yeah, way too many issues.
Richard Goulet, MSgt USAF (ret)
Oracle DBA in retirement and loving it.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 11 2023 - 17:40:24 CET