SQL Translation框架是 12c 中的一项新功能，使开发人员能够在不更改底层代码的情况下替换SQL代码。这个特性是sql profile baseline的增强，原来是可以不动SQL文本替换执行计划，现在是连sql文本都可以“隐式”替换。这功能可用于在异构数据库向oracle迁移时，替换代码。
方法：
1， 创建SQL translation profile
2， 注册SQL statement到profile，做替换
3， 配置event 10601 用户profile或SQL literal replacement
Event 10601: turn on debugging for cursor_sharing (literal replacement)
demo
SQL> @desc test1
Name Null? Type
------------------------------- -------- ----------------------------
1 ID NUMBER(38)
SQL> select * from test1;
ID NAME
---------- ----------
2 anbob.com
SQL> @x2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST1 | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
12 rows selected.
SQL> select * from test1 t1;
ID
----------
1
Note:
oops! test1表使用dest看只有1列，但第一个查询返回2列数据，我们修改sql text增加了个alias name(主要是与原来不同)，返回又是1列 数据，dbms_stat.display执行计划确实是test1, 当然这个过程没人做DDL重建表。 下面看这个魔术是如何实现的。
SQL> create table test1(id int);
Table created.
SQL> insert into test1 values (1);
1 row created.
SQL> create table test2 (id int,name varchar2(10));
Table created.
SQL> insert into test2 values (2,'anbob.com');
1 row created.
SQL> commit;
Commit complete.
SQL> exec DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name => 'DEMO_PROFILE'); exception when others then null;
PL/SQL procedure successfully completed.
SQL> exec dbms_sql_translator.create_profile('ANBOB_PROFILE');
PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
profile_name => 'ANBOB_PROFILE',
sql_text => 'select * from test1',
translated_text => 'select * from test2');
END;
/
PL/SQL procedure successfully completed.
SQL> @printtab "select * from user_sql_translation_profiles"
PROFILE_NAME : ANBOB_PROFILE
TRANSLATOR :
FOREIGN_SQL_SYNTAX : TRUE
TRANSLATE_NEW_SQL : TRUE
RAISE_TRANSLATION_ERROR : FALSE
LOG_TRANSLATION_ERROR : FALSE
TRACE_TRANSLATION : FALSE
LOG_ERRORS : FALSE
-----------------
SQL> @printtab "select * from user_sql_translations";
PROFILE_NAME : ANBOB_PROFILE
SQL_TEXT : select * from test1
TRANSLATED_TEXT : select * from test2
SQL_ID : 0zkpy7fsa27fw
HASH_VALUE : 2963348956
ENABLED : TRUE
REGISTRATION_TIME : 10-OCT-23 05.19.12.129592 PM
CLIENT_INFO :
MODULE :
ACTION :
PARSING_USER_ID :
PARSING_SCHEMA_ID :
COMMENTS :
ERROR_CODE :
ERROR_SOURCE :
TRANSLATION_METHOD :
DICTIONARY_SQL_ID :
-----------------
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set sql_translation_profile =ANBOB_PROFILE;
Session altered.
SQL> alter session set events = '10601 trace name context forever, level 32';
Session altered.
SQL> select * from test1;
ID NAME
---------- ----------
2 anbob.com
NOTE:
这里查询test1，sql translation转换文本实际是查询的test2, 如果做10046 trace可以验证, 但是做10053 并没有找到关于test2的信息
# 10046
===================== PARSING IN CURSOR #139849413777704 len=19 dep=0 uid=0 oct=3 lid=0 tim=7505541637169 hv=1553580882 ad='9df331d0' sqlid='317mx1pf9mhuk' select * from test2 END OF STMT
# 10053
$grep -i test /u01/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob_ora_18035.trc
fro(0): flg=4 objn=164725 hint_alias="TEST1"@"SEL$1"
select * from test1
SELECT "TEST1"."ID" "ID" FROM "SYS"."TEST1" "TEST1"
TEST1[TEST1] 164725, type = 1
SELECT "TEST1"."ID" "ID" FROM "SYS"."TEST1" "TEST1"
select * from test1
fro(0): flg=0 objn=164725 hint_alias="TEST1"@"SEL$1"
Table: TEST1 Alias: TEST1 (NOT ANALYZED)
Access path analysis for TEST1
Single Table Cardinality Estimation for TEST1[TEST1]
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("TEST1") FULL("TEST1") NO_PARALLEL_INDEX("TEST1") */ 1 AS C1, 1 AS C2 FROM "SYS"."TEST1" "TEST1") SAMPLESUB
Table: TEST1 Alias: TEST1
Join order[1]: TEST1[TEST1]#0
AutoDOP: Consider caching for TEST1[TEST1](obj#164725)
Transfer optimizer annotations for TEST1[TEST1]
CBRID: TEST1 @ SEL$1 - no blocking operation found
select * from test1
sql=select * from test1
| 1 | TABLE ACCESS FULL | TEST1 | 1 | 13 | 2 | 00:00:01 |
1 - SEL$1 / "TEST1"@"SEL$1"
FULL(@"SEL$1" "TEST1"@"SEL$1")
不过这里有条限制，前提转换的SQL要是有效的（如何判断暂不确认），如我们想把mysql的now() 函数在oracle换成sysdate，是不允许的.
SQL> exec dbms_sql_translator.create_profile('DEMO_PROFILE');
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
profile_name => 'DEMO_PROFILE',
sql_text => 'SELECT now() from dual',
translated_text => 'SELECT sysdate from dual');
END;
/
SQL> @printtab "select * from user_sql_translations";
PROFILE_NAME : DEMO_PROFILE
SQL_TEXT : SELECT now() from dual
TRANSLATED_TEXT : SELECT sysdate from dual
SQL_ID : 9xgbdnrtcfnvc
HASH_VALUE : 4073149292
ENABLED : TRUE
REGISTRATION_TIME : 10-OCT-23 05.10.45.893209 PM
CLIENT_INFO :
MODULE :
ACTION :
PARSING_USER_ID :
PARSING_SCHEMA_ID :
COMMENTS :
ERROR_CODE :
ERROR_SOURCE :
TRANSLATION_METHOD :
DICTIONARY_SQL_ID :
SQL> select now() from dual;
select now() from dual
*
ERROR at line 1:
ORA-00904: "NOW": invalid identifier
但是把sysbase或sql server的select top * from xxx,转换为oracle 的select * from xx where rownum<=xx 又是可以的。
SQL> exec DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name => 'ANBOB_PROFILE'); exception when others then null;
PL/SQL procedure successfully completed.
SQL> exec dbms_sql_translator.create_profile('ANBOB_PROFILE');
PL/SQL procedure successfully completed.
BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
profile_name => 'ANBOB_PROFILE',
sql_text => 'select top 10 * from all_tables',
translated_text => 'select * from all_tables where rownum<=10');
END;
/
PL/SQL procedure successfully completed.
SQL> alter session set sql_translation_profile = ANBOB_PROFILE;
Session altered.
SQL> alter session set events = '10601 trace name context forever, level 32';
Session altered.
SQL> select top 10 * from all_tables;
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
CLUSTER_NAME
好吧，oracle的问题诊断又复杂了。如果sql里select换成了delete或drop table那就更危险了。 这里如果不想让应用有感知，或alter session执行，可以单独创建service， service配置sql translation profile， 注册在listener上，默认通过该service创建的连接就启用了sql translation profile.
— enjoy —