Thanks for the question, Mark.
Asked: October 04, 2016 - 4:53 pm UTC
Last updated: October 05, 2016 - 5:59 am UTC
Version: 12.2.1
Viewed 1000+ times
You Asked
This is sort of a #ThickDB question. I can put functions, procedures, etc. in ODI for it to execute on the database or I can put the functions, procedures, etc. in the database and have ODI execute them when appropriate. I've tried this both ways and haven't seen a performance difference but I wondering what "best practices" are. It is possible I've simply not stressed the system enough to see a performance difference.
Having everything in ODI makes promotion (Dev to Test to Prod) easier since all the code comes along with the scenarios and load plans and we don't have to worry about migrating objects between databases in a separate process. Is there any consensus on where this code should reside?
Oh, all the databases are on Exadata and ODI (and it's Agent) is running on a series of VMs (one per environment), all on the same internal network.
Thanks,
Mark
and Connor said...
To my knowledge, the plsql in odi gets shipped over to the database for execution (ie, I dont believe it has a local plsql engine like Oracle Forms etc), so that would explain the performance similarity.
For me, if you are getting administrative and manageability benefits from having it on ODI, then it makes sense to keep it there. Adopting something that is harder simply based on "philosophical" grounds is a mistake we make all too often in the IT industry.
If you have *other* plsql code that resides in the database - that might be a differnet story, because now you have two repositories - but ultimately, decide what works best *for you*, where performance is a *component* of that decision, not the sole arbiter.
Is this answer out of date? If it is, please let us know via a Comment