One of the new Database 12c features is the SQL Translation Framework. Developed primarily to help customer migrate their 3rd party applications and databases to the Oracle stack, it also offers application DBAs an opportunity to substitute poorly performing SQL with their own custom SQL – without changing the application itself!
Do I have your attention now?
Kerry Osborne wrote an excellent post on the topic using a closer-to-real-world example and shows the pl/sql package calls to implement this solution. At KScope14 in Seattle yesterday, I quickly showed how to do this using the GUI in Oracle SQL Developer.
Here are those slides…
The ‘Overhead Question’
Of course you’re wondering about how ‘expensive’ this solution is. Since the translator itself isn’t being invoked, the cost is much like any other SQL statement – it must be compiled by the database engine, and then running it after that is very cheap.
This is a very dumbed-down and poorly paraphrased summary of an excellent answer provided by the PM who oversaw the development of this feature – perhaps you’ve heard of him?
