I recently raised the issue with my client about how they were using APEX. I'm not here as an APEX consultant, but they didn't have a lot of experience in-house and the DBA who got it implemented had been trying for years. So I wrote up my thoughts and passed them along to my supervisor who then shared them with others.
The current setup was a stand-alone instance that was only used for APEX applications using database links and synonyms to access the source tables. I was arguing to have APEX installed on the database where the data lives.
When I got on the call, I listened to their arguments and concerns.
Then I began to think...what do you do if you have, say, 100 databases? Do you install APEX on each and every one? That's just another piece that has to be supported and maintained right?
After the call, I began to do a little research on the subject...and couldn't find a whole lot of information out there. Perhaps I wasn't searching on the right terms...not really sure.
Thinking about it further and talking with some friends, I came up with a short list of potential architectures.
- Install it Locally - This is my preferred method. The APEX metadata (application) lives in the same database as the data it manipulates.
- Database Links - Target Tables Remote, Source Code Local - This is the configuration I spoke of above. There is a single database where APEX is installed which accesses tables across database links. Synonyms would be used for tables and views to "hide" the long names and make them visible. Any source code (PL/SQL) would be local.
- Database Links - Target Tables Remote, Source Code Remote - Same as #2, but the source code lives in the remote database. Again, synonyms would need to be used to make database objects visible to APEX.
For #2 and #3, the synonyms add additional maintenance and complexity to your application(s).
Also with #2 and #3, you have additional network traffic, which, depending on the environment and/or the number of users, could be significant.
Like I said, my first thought was to install it locally. In addition, just put it on the databases that you will use it, not every single one. I still believe that, but am trying to give a balanced and reasoned answer.
Ultimately, maybe it just depends on how you use it. If this is going to be a "professionally" written application (as opposed to something a power user might build), I think having it installed on the databases where your data lives. If it's going to be driven by those power users as a replacement for the Excel and Access databases, I think the best approach might just be the single instance accessing tables remotely would be the way to go.
Anyway, just my thoughts. Please scream (write) loudly if you have thoughts on the matter.
Worked at a place where they had a dedicated server for Apex apps funded by IT. If an app grew too big you had to spawn off the app to a dedicated server funded from your own budget (but pretty much cloned from the same architecture).
ReplyDeleteI suppose what I'm getting at is that, if you have 100 databases, ask yourself why and whether there may be cost/management benefits in consolidating to fewer shared databases.
You'll get no argument from me there.
ReplyDeleteUnfortunately and somewhat understandably, that kind of thing won't happen any time soon.
I'm just trying to help them utilize it to its fullest potential. How many manual processes do they run that could be quickly and easily turned into an APEX app? Pretty much all the low hanging fruit is what I am trying to convince them can be handled by APEX. I don't like writing a package without some sort of "other" interface on top of it...you know, a GUI. :)
That's one of the reasons we setup the ADF Enterprise Methodology Group, to promote the higher level architectural questions and discussions. An APEX EMG surely would be a good idea too :-)
ReplyDeleteCM.
Chris,
ReplyDeleteI agree...there should be a group like that.
Did you know you inspired an OBIEE group along the same lines?
chet
Back to Gary's point. Get rid of the DB Links and consolidate the data. Oracle is certainly capable of handling large amounts of data/schemas and the value of having all that data "close" together can definitely pay offin cost savings and / or performance improvements (depending on how the data is organized and how it hammers a system).
ReplyDeleteHi Chet,
ReplyDeleteFirst off. I quite enjoy your blog and the technical information you supply. And I admire your community building efforts!
Regarding links with Apex. One place where I feel one has to use them is for reporting on data sources where it makes little sense to get the remote data into the local instance. For example, I've been using Apex to do reports on remote SQL Servers via hsodbc. Works fairly well and all access to the remote SQL Servers is read-only. These SQL Servers are already reporting repositories of data collected by agents from various other nodes. Doesn't seem worth it, at least at this stage in the project, to bring over the data.
Currently the numbers of users of the Apex reporting app will be low.
On the negative side, I worry about all the obvious things that might occur if the app draws in more users: load on the remote databases, managing the connections created by the database links, managing the OPEN_LINKS parameter in the Apex instance, etc.
Architecturally, it's interesting to think of Apex functioning as middleware in this sense. The documentation is pretty thin on just how Apex manages database connection pooling, and even thinner on what the implications are when using database links.
Ben