[20160113]不要亂用國際化函式.txt

[20160113]不要亂用國際化函式.txt

--生產系統上午使用perf檢查發現:

#  perf top -k /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle

PerfTop:    6443 irqs/sec  kernel:47.0%  exact:  0.0% [1000Hz cycles],  (all, 24 CPUs)
------------------------------------------------------------------------------------------------------------------------

             samples  pcnt function               DSO
             _______ _____ ______________________ ______________________________________________________________________

             2125.00  4.8% lxpcget                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             2113.00  4.8% lxoBinCmpMutl          /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1608.00  3.6% lxgcvp                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1340.00  3.0% lxoCmpStr              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1203.00  2.7% lxmcpen                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1196.00  2.7% lxsCmpStr              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1158.00  2.6% evastr                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1078.00  2.4% kcbgtcr                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1025.00  2.3% kpofcr                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              807.00  1.8% lxgcnvb                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle

--從來沒有看到lx開頭的函式排在前面.我們一般kcbgtcr排在前面,這個主要是邏輯讀.而且現在剛剛上班9點多,沒有到業務高峰.

SELECT sql_id, COUNT (*)
    FROM V$ACTIVE_SESSION_HISTORY
   WHERE sample_time > SYSDATE - 10 / 1400
GROUP BY sql_id
ORDER BY 2 DESC;

SQL_ID          COUNT(*)
------------- ----------
                     310
7xtuxjbw6x9qq        162
gc292abmax9gd         51
g7k9fgfzdb8j4         37
....

--檢查發現開發的sql語句使用錯誤的函式to_nchar,這樣無法找到滿足條件的結果.由於生產系統語句複雜,透過例子來說明:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select dump(to_nchar(deptno)) c30 ,dump(to_char(deptno)) c20 ,t.* from dept  t;
C30                            C20                      DEPTNO DNAME          LOC
------------------------------ -------------------- ---------- -------------- -------------
Typ=1 Len=4: 0,49,0,48         Typ=1 Len=2: 49,48           10 ACCOUNTING     NEW YORK
Typ=1 Len=4: 0,50,0,48         Typ=1 Len=2: 50,48           20 RESEARCH       DALLAS
Typ=1 Len=4: 0,51,0,48         Typ=1 Len=2: 51,48           30 SALES          CHICAGO
Typ=1 Len=4: 0,52,0,48         Typ=1 Len=2: 52,48           40 OPERATIONS     BOSTON

--可以發現dump(to_nchar(deptno)) ,dump(to_char(deptno))兩者是不等的,所以講沒有國際化需求不要亂用這些函式.
--我們應用一些欄位存在隱式轉換,或者型別不一致,導致開發使用錯誤的函式或者不理解為什麼,導致後臺cpu忙.

--開啟2個視窗:

# perf top -k  /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle

--另外一個執行:
SCOTT@book> set autot traceonly
SCOTT@book> select to_nchar(rownum) c10,to_nchar(rownum+1),to_nchar(rownum-1) from dual connect by 1=1;
ERROR:
ORA-30009: Not enough memory for CONNECT BY operation

--我的條件1=1,這樣無限輸出,不斷的呼叫to_nchar函式.直到沒有足夠的記憶體,可以執行多次.回到另外一個視窗觀察,可以發現都是lx開頭的函式.
--基本與我前面生產系統遇到的問題一樣.

   PerfTop:    3332 irqs/sec  kernel:62.4%  exact:  0.0% [1000Hz cycles],  (all, 24 CPUs)
-----------------------------------------------------------------------------------------------------------------

             samples  pcnt function               DSO
             _______ _____ ______________________ _______________________________________________________________

              802.00 10.4% lxpcget                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              581.00  7.6% lxgcvp                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              504.00  6.6% lxoBinCmpMutl          /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              478.00  6.2% lxoCmpStr              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              429.00  5.6% evastr                 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              379.00  4.9% lxmcpen                /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              369.00  4.8% lxsCmpStr              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              289.00  3.8% lxoSkip                /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libclntsh.so.11.1

--可以發現一個簡單to_nchar函式,後臺oracle呼叫的內部函式有多少.