[20261105]测试21c SQL Translation Framework.txt
--//参考链接：
https://piotrsajda.com/oracle-blog/sql-translation-framework-tackling-ora-04031-part-1/
https://piotrsajda.com/oracle-blog/sql-translation-framework-tackling-ora-04031-part-2/
https://piotrsajda.com/oracle-blog/sql-translation-framework-tackling-ora-04031-part-3/
--//测试21c使用SQL Translation Framework，以前11g,12c,18c都测试过，在21c重复验证看看。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试环境：
create table t1 ( id number,vc varchar2(32),cr_date date);
create unique index pk_t1 on t1 (id);
alter table t1 add constraint pk_t1 primary key (id);
create index i_t1_cr_date on t1(cr_date);
SCOTT@book01p> insert into t1 select rownum,'test'||rownum,sysdate-rownum/100 from dual connect by level<=1e5;
100000 rows created.
SCOTT@book01p> commit ;
Commit complete.
3.简单测试：
SCOTT@book01p> select vc from t1 where cr_date = timestamp '2026-10-31 12:16:01';
VC
--------------------------------
test589
SCOTT@book01p> alter session set cursor_sharing = force;
Session altered.
SCOTT@book01p> select vc from t1 where cr_date = timestamp '2026-10-31 12:16:02';
no rows selected
SCOTT@book01p> @ dpc '' '-note' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g035npxsfg1af, child number 0
-------------------------------------
select vc from t1 where cr_date = timestamp :"SYS_B_0"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1000 | 18000 | 139 (3)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("CR_DATE")=TIMESTAMP' 2026-10-31
12:16:02.000000000')
--//这类情况以前测试过，虽然实现光标共享，但是每次产生不同的子光标。更换不同日期常量执行多次查看。
--//而且因为采用timestamp类型存在隐身转换，不能使用索引。
SCOTT@book01p> @ gunshare g035npxsfg1af
REASON_NOT_SHARED CURSORS SQL_IDS
----------------------------- ---------- ----------
HASH_MATCH_FAILED 4 1
--//原因使HASH_MATCH_FAILED。
4.采用SQL Translation Framework修正sql语句：
begin
dbms_sql_translator.create_profile('test_profile');
/* This attribute indicates if the profile is for traslation
of foreign SQL syntax only. if True only foreign sql
will be translated, if false, all sql from client
application will be translated */
dbms_sql_translator.set_attribute(
profile_name=>'test_profile',
attribute_name=> dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
attribute_value=> dbms_sql_translator.ATTR_VALUE_FALSE );
dbms_sql_translator.register_sql_translation(
profile_name=>'test_profile',
sql_text=>q'[select vc from t1 where cr_date = timestamp :"SYS_B_0"]',
translated_text=>q'[select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')]');
end;
/
SCOTT@book01p> alter session set cursor_sharing = force;
Session altered.
SCOTT@book01p> alter session set sql_translation_profile = test_profile;
Session altered.
SCOTT@book01p> select vc from t1 where cr_date = timestamp '2026-10-31 12:16:06';
no rows selected
SCOTT@book01p> @ dpc '' '-note' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 82pn8n6aappuq, child number 0
-------------------------------------
select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD
hh24:mi:ss')
Plan hash value: 1695313357
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_CR_DATE | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T1"@"SEL$1"
2 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CR_DATE"=TO_DATE(:SYS_B_0,'YYYY-MM-DD hh24:mi:ss'))
26 rows selected.
--//现在sql语句转化为select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')。
--//因为timestamp类型变成date类型，可以使用建立的I_T1_CR_DATE索引。
--//sql_id也发生了变化，变为82pn8n6aappuq，原来使g035npxsfg1af。
SCOTT@book01p> @ sql_id 82pn8n6aappuq
-- SQL_ID = 82pn8n6aappuq come from shared pool
select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss');
--//更换日期常量执行多次，发现不再出现大量子光标。
SCOTT@book01p> @ gunshare 82pn8n6aappuq
no rows selected
--//顺便提一下，至少从12c开始，如果执行sql语句如下：
select vc from t1 where cr_date = :b1 ;
--//即使绑定变量b1是timestmp类型也可以使用I_T1_CR_DATE索引。
--//注意如果sql语句做一点点修改，不会转换新的执行sql语句。
SCOTT@book01p> Select vc from t1 where cr_date = timestamp '2026-10-31 12:16:01';
VC
--------------------------------
test589
--//select换成开头大写Select，
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 51zhdvsp9kh9s, child number 0
-------------------------------------
Select vc from t1 where cr_date = timestamp :"SYS_B_0"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 139 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1000 | 18000 | 139 (3)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("CR_DATE")=TIMESTAMP' 2026-10-31
12:16:01.000000000')
5.涉及视图:
DBA_ERROR_TRANSLATIONS
DBA_SQL_TRANSLATION_PROFILES
DBA_SQL_TRANSLATIONS
SCOTT@book01p> select * from DBA_ERROR_TRANSLATIONS;
no rows selected
SCOTT@book01p> select * from DBA_SQL_TRANSLATION_PROFILES
2 @ pr
==============================
OWNER : SCOTT
PROFILE_NAME : TEST_PROFILE
TRANSLATOR :
FOREIGN_SQL_SYNTAX : FALSE
TRANSLATE_NEW_SQL : TRUE
RAISE_TRANSLATION_ERROR : FALSE
LOG_TRANSLATION_ERROR : FALSE
TRACE_TRANSLATION : FALSE
LOG_ERRORS : FALSE
PL/SQL procedure successfully completed.
SCOTT@book01p> select * from DBA_SQL_TRANSLATIONS
2 @ pr
==============================
OWNER : SCOTT
PROFILE_NAME : TEST_PROFILE
SQL_TEXT : select vc from t1 where cr_date = timestamp :"SYS_B_0"
TRANSLATED_TEXT : select vc from t1 where cr_date = to_date(:"SYS_B_0",'YYYY-MM-DD hh24:mi:ss')
SQL_ID : g035npxsfg1af
HASH_VALUE : 1894221134
ENABLED : TRUE
REGISTRATION_TIME : 2026-11-06 10:04:42.078453
CLIENT_INFO :
MODULE :
ACTION :
PARSING_USER_ID :
PARSING_SCHEMA_ID :
COMMENTS :
ERROR_CODE :
ERROR_SOURCE :
TRANSLATION_METHOD :
DICTIONARY_SQL_ID :
PL/SQL procedure successfully completed.
6.收尾:
SCOTT@book01p> exec dbms_sql_translator.drop_profile(profile_name => 'test_profile');
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3188151/ ，如需转载，请注明出处，否则将追究法律责任。
