Skip to Main Content
  • Questions

  • SQL and/or PL/SQL in ODI vs in the Database

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library