[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调用的内部函数有多少.