“Science is magic that works” Kurt Vonnegut
Have a look at the following series of commands.
A query on the SALES table takes normally more than 2 minutes but setting the database_performance parameter to SUPER_FAST makes it … as expected super fast: less than 1 second. Setting the database_performance parameter to SUPER_SLOW makes the query hang. Again “as expected”.
So, how come all this is possible?
Before blogging it, I showed this “magical trick” to 100s of people: at Oracle OpenWorld, at some Oracle User Group events and conferences and to many DBAs. Here is the explanation.
Behind the curtains, I am using the Oracle SQL Translation Framework.
exec dbms_sql_translator.create_profile('OOW'); select object_name, object_type from dba_objects where object_name like 'OOW'; exec dbms_sql_translator.register_sql_translation('OOW', 'SELECT max(price) most_expensive_order from sales', 'SELECT max(price) most_expensive_order from julian.sales') / exec dbms_sql_translator.register_sql_translation('OOW', 'alter session set database_performance="SUPER_FAST"', 'alter session set inmemory_query="ENABLE"') / exec dbms_sql_translator.register_sql_translation('OOW', 'alter session set database_performance="RATHER_SLOW"', 'alter session set inmemory_query="DISABLE"') / exec dbms_sql_translator.register_sql_translation('OOW', 'alter session set database_performance="SUPER_SLOW"', 'begin uups; end;') /
The procedure uups is just helping us mimic a never ending loop:
create or replace procedure uups as x date; BEGIN LOOP BEGIN SELECT null INTO x FROM dual WHERE sysdate = sysdate; EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; END; /
Then, once connected to the database as SYS you run the following commands:
set timing on alter session set sql_translation_profile = OOW / alter session set events = '10601 trace name context forever, level 32' /
The last question now is probably what changes the performance? But this should be clear from the usage of the inmemory_query parameter. I am simply keeping the SALES table in memory. So yes, the in-memory option can be 137 times faster! 133.22/0.97 ~ 137
Here is something more to read if you find the topic interesting:
SQL Translation Framework in Oracle Database 12c by Okcan Yasin Saygili
SQL Translation Framework by Kerry Osborne
Oracle 12c Security – SQL Translation and Last Logins by Pete Finnigan