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.