SQL> select * from v$version;


BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


create table t11 as select * from dba_objects where rownun<1000;
create table t12 as select * from dba_objects where rownun<1000;


exec dbms_sql_translator.create_profile('DEMO_PROFILE');


BEGIN
    DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
      profile_name    => 'DEMO_PROFILE',
      sql_text        => 'SELECT COUNT(*) FROM t11', 
      translated_text => 'SELECT COUNT(*) FROM t12');
END;
/




alter session set sql_translation_profile = DEMO_PROFILE;

-- For testing make the sqlplus look like a foreign tool
alter session set events = '10601 trace name context forever, level 32';




SQL> SELECT COUNT(*) FROM t11;


  COUNT(*)
----------
      1999


SQL> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  59bs5sr947rn1, child number 0
-------------------------------------
SELECT COUNT(*) FROM t12


Plan hash value: 4279859672


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |    12 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T12  |  1999 |    12   (0)| 00:00:01 |
-------------------------------------------------------------------


已选择 14 行。

明明查询的是t11, 却被转换成了查t12, 神奇的功能。
如果现在有个应用很慢,其中有个sql就是类似大表的count(*), 而count(*)的结果又不是很重要，
在不修改代码的情况下, SQL Translator Profiles可以救急