Archive for the ‘12c’ Category.

SQL Translation Framework

My favorite new Oracle Database 12c feature is the SQL Translation Framework. The feature grew out of SQL Developer’s ability to translate SQL from non-Oracle RDBMS’s. For example, there is a pre-built Sybase ASE translation package that is designed to translate the Sybase dialect of SQL into Oracle SQL dialect. So that’s what the feature is designed for. But the developers decided to move it to the database and to allow us to write our own translations which opens up a whole world of possibilities.

The first thought that occurred to me when I saw this feature listed in the 12c New Features doc, was that I might be able to use it to fix badly written SQL behind the scenes. I’ve written and talked quite a bit about using hint based mechanisms (Outlines, SQL Profiles, Baselines, and SQL Patches) to alter execution plans without having to change the code. Those technique work great most of the time, but there are cases where hints alone can’t fix the problem. In some cases it is necessary to change the SQL statement text to get the desired results. And the SQL Translation Framework gives us the tool kit we need to do just that. And by the way, although I do have a tendency to use Oracle features for purposes for which they were not originally intended, in this case, I think the developers knew full well that the features could be used to address performance issues by re-writing SQL. As proof, here is a snippet from the 12c Release 1 Migration guide.

In addition to translating non-Oracle SQL statements, the SQL Translation Framework can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue. In this way, you can address an application issue without patching the client application.

So let’s dive in. There are two main components to the framework. The first is a pl/sql package to programmatically translate code (also called the Translator in the docs). The second is a set of maps for individual SQL statements (this is called a SQL Translation Profile). There are a couple of requirements to use this feature.

1. You must create a SQL Translation Profile (using dbms_sql_translator.create_profile)
2. You must assign a session to use the Translation Profile (generally with an alter session command)
3. You must set the 10601 system event

While the Translator Profile is required, it does not have to be assigned a translator. In other words, you can map individual statements without writing a PL/SQL package. Of course if you have a system that has a lot of problems caused by the same coding pattern, you could potentially use the framework to rewrite those statements on the fly.

Here’s a quick example for a simple case of mapping individual statements.

SYS@LAB1211> exec dbms_sql_translator.create_profile('FOO');

PL/SQL procedure successfully completed.

SYS@LAB1211> select object_name, object_type from dba_objects where object_name like 'FOO';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
FOO                            SQL TRANSLATION PROFILE

SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select count(*) from hr.countries','select count(*) from hr.jobs');

PL/SQL procedure successfully completed.

SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select count(*) from countries','select count(*) from jobs');

PL/SQL procedure successfully completed.

SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select 1 from hr.countries','select count(*) from hr.countries');

PL/SQL procedure successfully completed.

SYS@LAB1211> grant all on sql translation profile foo to hr;

Grant succeeded.

SYS@LAB1211> alter session set sql_translation_profile = FOO;

Session altered.

SYS@LAB1211> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SYS@LAB1211> set echo on
SYS@LAB1211> select count(*) from hr.countries;

  COUNT(*)
----------
        19

SYS@LAB1211> select /*+ fix_wrong_results */ count(*) from hr.countries;

  COUNT(*)
----------
        25

SYS@LAB1211> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aaajpnhn25nza, child number 0
-------------------------------------
select /*+ fix_wrong_results */ count(*) from hr.countries

Plan hash value: 1399856367

----------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |                 |     1 |            |          |
|   2 |   INDEX FULL SCAN| COUNTRY_C_ID_PK |    25 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------


14 rows selected.

SYS@LAB1211> select count(*) from hr.countries;

  COUNT(*)
----------
        19

SYS@LAB1211> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c95vwg4jwqqfd, child number 0
-------------------------------------
select count(*) from hr.jobs

Plan hash value: 3870222678

-----------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |     1 (100)|
|   1 |  SORT AGGREGATE  |           |     1 |            |
|   2 |   INDEX FULL SCAN| JOB_ID_PK |    19 |     0   (0)|
-----------------------------------------------------------


14 rows selected.

Continue reading ‘SQL Translation Framework’ »

12c Adaptive Optimization

Since everyone seems to be all twitterpated about Oracle Database 12c this week, I thought I’d post a quick note to let you know that the slides from the presentation on 12c Adaptive Optimization I did at the Hotsos Symposium 2013 (with a lot of help from Maria) are now available in the Whitepapers / Presentations section of this blog.

While I’m on the topic, I found this little blurb in the Oracle Database 12c Release 1 New Features Guide:

Zero Effort Performance

That’s the section that talks about the Adaptive Optimization stuff. I think the documentation folks meant that they were describing performance features that didn’t require any manual intervention, but it sort of reads like the features are really easy to describe, or maybe that the writers weren’t going to work very hard on describing them. At any rate, the wording struck me as humorous. 🙂

Hotsos Symposium 2013

Just a quick note to let you know I’ll be speaking at the Hotsos Symposium in March in Dallas. I’ve attended every year for the past 6 or 7 years and spoken at several of them. It has consistently been the best performance oriented Oracle conference I’ve attended. This year will be no different with the likes of Maria Colgan, Karen Morton, Tom Kyte, Tim Gorman, Cary Millsap, Frits Hoogland, Gwen Shapira, Alex Gorbachev, Kyle Haily, Carlos Sierra, Kellyn Pot’Vin, Mark Farnham, Toon Koppelaars, Andy Zitelli, Neil Gunther, Stephan Haisley, Marco Gralike, Steven Feuerstein and a host of others. Looks like several of the speakers (including me) plan to be talking about new performance oriented features of the soon to be released Oracle 12c database, so it should be very interesting. One of the best things about the conference is the chance to talk to people (including the speakers) at the breaks. And by the way, while the speaker list is impressive, there are always a large number of highly talented people in attendance that are not speaking. I routinely learn as much from conversations between the sessions as I do listening to the presentations. I highly recommend the symposium to anyone that is interested in Oracle performance. Here’s a link to the main page where you can find the complete list of speakers and their topics and register for the conference.

Hope to see you there!