12C SQL Translation Framework.txt

[20181013]12C SQL Translation Framework.txt

--//12c提供一個dba改寫sql語句的可能性,實際上10g,11g之前也有一個包DBMS_ADVANCED_REWRITE能實現類似的功能.
--//這種功能實在是一種旁門左道,還是測試看看.

--//不過如果程式存在大量的執行錯誤,一樣會影響效能,導致出現SQL*Net break/reset to client.
--//參考連線:0624使用10035事件跟蹤無法執行的sql語句 =>http://blog.itpub.net/267265/viewspace-2120884/
--//http://www.itpub.net/thread-2061952-1-1.html

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:

begin
   dbms_sql_translator.create_profile('test_profile');
   dbms_sql_translator.register_sql_translation( profile_name => 'test_profile',
                                                 sql_text => 'select sysdate',
                                                 translated_text => 'SELECT SYSDATE FROM DUAL');
end;
/
--//注sql_text可以寫不對,但是前面一定開始是select,不然sqlplus無法識別是sql語句.

SCOTT@test01p> alter session set sql_translation_profile=test_profile;
Session altered.

SCOTT@test01p> alter session set events = '10601 trace name context forever, level 32';
Session altered.

D:\tools\rlwrap>oerr ora 10601
10601, 00000, "turn on debugging for cursor_sharing (literal replacement)"
// *Cause:
// *Action:

SCOTT@test01p> select sysdate;
SYSDATE
-------------------
2018-10-15 20:24:21

SCOTT@test01p> Select sysdate;
Select sysdate
             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
--//一定要與原來文字一樣.

--//重新整理共享池問題:

SCOTT@test01p> alter system flush shared_pool;
System altered.

SCOTT@test01p> select sysdate;
SYSDATE
-------------------
2018-10-15 20:27:07

SCOTT@test01p> SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE;
D
-
X

SYS@test> alter system flush shared_pool;
System altered.

SCOTT@test01p> select sysdate;
SYSDATE
-------------------
2018-10-15 20:27:50

--//我記憶裡早期12.1.0.1版本重新整理共享池後執行會報錯.12cR2版本修復這個錯誤.

3.看看記錄在那些表中,如何刪除等等操作.

--//涉及檢視:
DBA_ERROR_TRANSLATIONS
DBA_SQL_TRANSLATION_PROFILES
DBA_SQL_TRANSLATIONS

SCOTT@test01p> @ pt2 'select * from DBA_SQL_TRANSLATIONS where PROFILE_NAME=''TEST_PROFILE''';
   ROW_NUM    COL_NUM COL_NAME             COL_VALUE
---------- ---------- -------------------- -------------------------------------------------------------
         1          1 OWNER                SCOTT
                    2 PROFILE_NAME         TEST_PROFILE
                    3 SQL_TEXT             select sysdate
                    4 TRANSLATED_TEXT      SELECT SYSDATE FROM DUAL
                    5 SQL_ID               bw2c1d6sqyjpy
                    6 HASH_VALUE           2976859838
                    7 ENABLED              TRUE
                    8 REGISTRATION_TIME    2018-10-15 20:23:15.415000
8 rows selected.

SYS@test> @ sharepool/shp4 bw2c1d6sqyjpy 0
TEXT           KGLHDADR         KGLHDPAR         C40            KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- -------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父遊標控制程式碼地址 000007FF130DCBC8 000007FF130DCBC8 select sysdate        1          0          0 00               00                        0          0          0         0          0 2327677740 bw2c1d6sqyjpy          0
--//看到一個很奇怪的父遊標控制程式碼,沒有子游標,而且父遊標的堆0是0.

--//如果還有一些語句還可以加入:
BEGIN
   dbms_sql_translator.register_sql_translation
   (
      profile_name      => 'test_profile'
     ,sql_text          => 'select user'
     ,translated_text   => 'SELECT usera FROM DUAL'
   );
END;
/

SCOTT@test01p> select user;
USER
--------------------
SCOTT

--//刪除執行如下:
SCOTT@test01p> exec dbms_sql_translator.drop_profile(profile_name => 'test_profile');
PL/SQL procedure successfully completed.

SCOTT@test01p> select sysdate;
select sysdate
             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SCOTT@test01p> @ pt2 'select * from DBA_SQL_TRANSLATIONS where PROFILE_NAME=''TEST_PROFILE''';
no rows selected

4.順便看看這個包dbms_sql_translator的其它功能:

--//可以使用它計算sql_id:
SCOTT@test01p> select dbms_sql_translator.SQL_ID('select sysdate') c20  from dual ;
C20
--------------------
bw2c1d6sqyjpy

--//和前面的能對上.