[20210113]給PB開發人員的一個建議.txt

lfree發表於2021-01-13

[20210113]給PB開發人員的一個建議.txt

--//PB 表示PowerBuild開發工具,目前至少許多企業依舊在使用它.
--//許多開發在拼接sql語句時使用\r來連線字串,這在dba診斷問題時出現一些奇怪的現象,透過遇到的例子來說明.
--//想檢視那個會話執行某個語句發現.輸出怪異:

1.環境:
SYS> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

2.測試:
SYS> column CURSOR_TYPE format a20
SYS> select * from v$open_cursor where sql_id='fagcu20tqqc7x';
SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT               LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- ---------------------- ------------------- ----------- --------------------
                                        OPEN               00000000AB7718C0  862662909 fagcu20tqqc7x   SELECT MS_CF01.CFHM,
                                        OPEN               00000000AB7718C0  862662909 fagcu20tqqc7x   SELECT MS_CF01.CFHM,
                                        OPEN               00000000AB7718C0  862662909 fagcu20tqqc7x   SELECT MS_CF01.CFHM,

--//奇怪看不見SID,而且OPEN跑到user_NAME來了.看見sql_text才明白開發使用\r換行.
--//整個開發團隊的管理存在問題,實際上N久以前就建議開發不要這樣使用.

SYS> select * from v$open_cursor where sql_id='fagcu20tqqc7x'
  2  @ prxx
==============================
SADDR                         : 00000000BC329A58
SID                           : 448
USER_NAME                     : XXXYYY_HIS
ADDRESS                       : 00000000AB7718C0
HASH_VALUE                    : 862662909
SQL_ID                        : fagcu20tqqc7x
          MS_CF01.CFSB,       :   SELECT MS_CF01.CFHM,
LAST_SQL_ACTIVE_TIME          :
SQL_EXEC_ID                   :
CURSOR_TYPE                   : OPEN
==============================
SADDR                         : 00000000BC80C9F8
SID                           : 856
USER_NAME                     : XXXYYY_HIS
ADDRESS                       : 00000000AB7718C0
HASH_VALUE                    : 862662909
SQL_ID                        : fagcu20tqqc7x
          MS_CF01.CFSB,       :   SELECT MS_CF01.CFHM,
LAST_SQL_ACTIVE_TIME          :
SQL_EXEC_ID                   :
CURSOR_TYPE                   : OPEN
==============================
SADDR                         : 00000000BDDB7FE0
SID                           : 19
USER_NAME                     : XXXYYY_HIS
ADDRESS                       : 00000000AB7718C0
HASH_VALUE                    : 862662909
SQL_ID                        : fagcu20tqqc7x
          MS_CF01.CFSB,       :   SELECT MS_CF01.CFHM,
LAST_SQL_ACTIVE_TIME          :
SQL_EXEC_ID                   :
CURSOR_TYPE                   : OPEN

PL/SQL procedure successfully completed.

SYS> column SQL_TEXT noprint
SYS> select * from v$open_cursor where sql_id='fagcu20tqqc7x';
SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE SQL_ID        LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------- ----------- --------------------
00000000BC329A58        448 XXXYYY_HIS                     00000000AB7718C0  862662909 fagcu20tqqc7x                                 OPEN
00000000BC80C9F8        856 XXXYYY_HIS                     00000000AB7718C0  862662909 fagcu20tqqc7x                                 OPEN
00000000BDDB7FE0         19 XXXYYY_HIS                     00000000AB7718C0  862662909 fagcu20tqqc7x                                 OPEN
--//這樣顯示就正常了.看看是什麼字元作怪.

SYS> select dump(sql_text,16) c100,to_char(replace(sql_fulltext,chr(13),'')) c100 from v$sqlarea where sql_id='fagcu20tqqc7x';
C100                                                                                                 C100
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
Typ=1 Len=660: 20,20,53,45,4c,45,43,54,20,4d,53,5f,43,46,30,31,2e,43,46,48,4d,2c,20,20,20,d,20,20,20   SELECT MS_CF01.CFHM,
,20,20,20,20,20,20,20,4d,53,5f,43,46,30,31,2e,43,46,53,42,2c,20,20,20,d,20,20,20,20,20,20,20,20,20,2          MS_CF01.CFSB,
0,4d,53,5f,43,46,30,31,2e,42,52,58,4d,2c,d,20,20,20,20,20,20,20,20,20,20,4d,53,5f,43,46,30,31,2e,46,          MS_CF01.BRXM,
50,48,4d,20,20,d,20,20,20,20,20,46,52,4f,4d,20,4d,53,5f,43,46,30,31,20,20,d,20,20,20,20,57,48,45,52,          MS_CF01.FPHM
45,20,28,20,4d,53,5f,43,46,30,31,2e,46,59,42,5a,20,3d,20,3a,61,69,5f,66,79,62,7a,20,29,20,41,4e,44,2     FROM MS_CF01
0,20,d,20,20,20,20,20,20,20,20,20,20,28,20,4d,53,5f,43,46,30,31,2e,59,46,53,42,20,3d,20,3a,61,69,5f,    WHERE ( MS_CF01.FYBZ = :ai_fybz ) AND
79,66,73,62,20,20,6f,72,20,4d,53,5f,43,46,30,31,2e,59,46,53,42,20,3d,20,3a,61,69,5f,67,6c,79,66,73,6          ( MS_CF01.YFSB = :ai_yfsb  or MS_CF01.YFSB = :ai_glyfsb) AND
2,29,20,41,4e,44,20,20,d,20,20,20,20,20,20,20,20,20,20,28,20,4d,53,5f,43,46,30,31,2e,5a,46,50,42,20,          ( MS_CF01.ZFPB = :"SYS_B_0" ) AND
3d,20,3a,22,53,59,53,5f,42,5f,30,22,20,29,20,41,4e,44,20,20,d,20,20,20,20,20,20,20,20,20,20,28,20,4d          ( MS_CF01.KFRQ >= :ai_cfxq ) AND
,53,5f,43,46,30,31,2e,4b,46,52,51,20,3e,3d,20,3a,61,69,5f,63,66,78,71,20,29,20,41,4e,44,d,20,20,20,2          MS_CF01.CFLX in (:ai_xycf,:ai_zycf,:ai_cycf)  AND
0,20,20,20,20,20,20,4d,53,5f,43,46,30,31,2e,43,46,4c,58,20,69,6e,20,28,3a,61,69,5f,78,79,63,66,2c,3a                    (MS_CF01.XTCFBZ < :"SYS_B_1" OR MS_CF01.XTCFBZ IS NULL)  AND
,61,69,5f,7a,79,63,66,2c,3a,61,69,5f,63,79,63,66,29,20,20,41,4e,44,d,20,20,20,20,28,4d,53,5f,43,46,3          ((MS_CF01.BRID = :al_brid AND :ai_skbz = :"SYS_B_2") OR
0,31,2e,58,54,43,46,42,5a,20,3c,20,3a,22,53,59,53,5f,42,5f,31,22,20,4f,52,20,4d,53,5f,43,46,30,31,2e          ((MS_CF01.FYCK = :"SYS_B_3" OR MS_CF01.FYCK = :ai_ckbh) AND
,58,54,43,46,42,5a,20,49,53,20,4e,55,4c,4c,29,20,20,41,4e,44,d,20,20,20,20,20,20,20,20,20,20,28,28,4           (  MS_CF01.FPHM is not null AND :ai_skbz = :"SYS_B_4" )))
d,53,5f,43,46,30,31,2e,42,52,49,44,20,3d,20,3a,61,6c,5f,62,72,69,64,20,41,4e,44,20,3a,61,69,5f,73,6b
,62,7a,20,3d,20,3a,22,53,59,53,5f,42,5f,32,22,29,20,4f,52,d,20,20,20,20,20,20,20,20,20,20,28,28,4d,5
3,5f,43,46,30,31,2e,46,59,43,4b,20,3d,20,3a,22,53,59,53,5f,42,5f,33,22,20,4f,52,20,4d,53,5f,43,46,30
,31,2e,46,59,43,4b,20,3d,20,3a,61,69,5f,63,6b,62,68,29,20,41,4e,44,20,d,20,20,20,20,20,20,20,20,20,2
0,20,28,20,20,4d,53,5f,43,46,30,31,2e,46,50,48,4d,20,69,73,20,6e,6f,74,20,6e,75,6c,6c,20,41,4e,44,20
,3a,61,69,5f,73,6b,62,7a,20,3d,20,3a,22,53,59,53,5f,42,5f,34,22,20,29,29,29,d,20

20,20,53,45,4c,45,43,54,20,4d,53,5f,43,46,30,31,2e,43,46,48,4d,2c,20,20,20,d,20,20,20
      S  E  L  E  C  T     M  S  _  C  F  0  1  .  C  F  H  M  ,           ~~

--//注意看下劃線0xd編碼(對應的就chr(13)),沒有0xa.難道開發連\r僅僅表示回車不懂嗎?
--//如果開發使用\n或者\r\n作為回車換行,這個問題根本不存在.\r僅僅表示回車並沒有換行.
--//另外我個人認為許多開發連基本的計算機常識都沒有,可悲可嘆.講了N多次,1次全部替換有這麼難嗎?

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2749747/,如需轉載,請註明出處,否則將追究法律責任。

相關文章