Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change about the sql. Well, in this case instead of asking to someone else, you can change that sql in the database before it runs!
After 12c, Oracle gave us an API, Sql Translation Framework, to run a different sql statement than the one sent by the application (or any client). We can affect a sql statements by using hints and database parameters but this is a whole new area. instead of “select * from customers” you can force Oracle to run “select count(*) from invoices“! you can change data source and event column list!
Main purpose of Sql Translation Framework to decrease the cost of converting a non Oracle application to an Oracle application. What! Let’s say you have small third party application which is run on Microsoft SQL Server database. Let say this is a small reporting tool and it is running some Select statements. you can easily move tables from sql server to Oracle but changing application could take much more longer time. At this point any basic select statement can run on any database for example “select * from invoices”; so no need to change anything in here but in sql server there is a useful command called TOP which allows you to get top n rows after an order by. So if your small reporting application runs this query: “select top 10 * from invoices order by amount desc” Oracle can not run this because TOP is a sql server command not Oracle.
Sql Translation Framework is involving at this point and allow you to run a different sql than the one issued. Whenever “select top 10 * from invoices order by amount desc” select statement issued we can tell Oracle to run “select * from invoices order by amount desc fetch first 10 rows only” statement. Pretty cool.
So this is the main purpose but since we can change the sql statements, this can let us to change poorly designed sql statements. Of course the main solution should be changing the source (change the sql which is called from the application) but this might not be possible all the time. for example, an application which we can not alter the source code might be running these queries (maybe we don’t have the source code or it is too expansive to ask for a change) or there might be an emergency, let’s say somehow a poor sql statement has been deployed to production system and it causes a very big performance problem. Since it might be fixed right away and deploying whole application all over again can take too much time. In this kind of situations, we can simply use sql translation framework.
I will demonstrate simple sql translation not an application translation (it will require more steps).
Steps to translate a sql to another:
- create a translation profile: main record for your tranlations. there could be more than one sql in a translation profile. you can simply think that as a translations groups to managed easily.
- register an sql to translate: the query you want to translate and the query you want translated to.
- grant necessary privileges to users who will use translations
- set sql_translation_profile parameter and 10601 event
as you can see I run an “invalid” sql statements. top 10 is a structure works on sql server but now I am able to run it in Oracle becuase I translated it into FETCH FIRST clause. please remember only exact sql will be translated. What I mean is if I add a order by or where clause to my translated query it won’t work:
Oracle does not know the version with “order by ” so you must add it too.
Also I mentioned about we can use this to implement a new version for poorly designed statements. Let’s test a union / union all query:
this is our test table. table contains 2 sets of data and LEVEL_ID is unique so same row won’t be listed in both group. Let’s assume our developer has written this sql:
Query takes 1.09 seconds. this query has a logical error. union is unnecessary, union all would return same data (since level_id is unique) and that’s because union all would work faster. if we can not change the source of the query then we can use sql translation framework:
there is one important thing here. consider ENTER character because if you are using it will be carriage return and new line but in linux it is just new line. so while testing you must be careful about enter char.
now let’s test it again:
as you can see performance of the query has increased almost 10 times. that is because it worked as union all not union. unfortunately you can not understand it by checking execution plan because it is the same cursor so you will be seeing old execution plan. That’s also why I compare timings. Of course you can check SQL_TRANSLATION_PROFILE_ID in V$Session while running the query.
This is a great tool but don’t get too involved into this. Creating too many translations can cause too much headache. you might loose in them. So I suggest you to use it wisely.
By the way this also works with bind variables too:
after running select * from hr.employees where employee_id = :emp_id with any emp id, you will get 3 rows from locations table. if you are careful you will see that I added a :emp_id = :emp_id condition to translated query. that is because same bind variables must be listed on the second query otherwise you will get an error.
I hope this helps you. There are too many different things in it so this is just an introduction.
have a nice day.