首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle 12c feature: SQL Translation Framework(文本替换) & event 10601

Oracle 12c feature: SQL Translation Framework(文本替换) & event 10601

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 —

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：