百事3主管2540437[20201214]再遇SQL*Net break/reset to client.txt
[20201214]再遇SQL*Net break/reset to client.txt
--//生产系统遇到一个问题，很久没遇到这类问题做一个记录。
1.环境：
XXXX> @ 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
XXXX> @ ashtop sql_id "machine<>'IMC' and sql_id not in (select sqlid from sqlid where flag=0)" trunc(sysdate) trunc(sysdate)+1
Total
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
53 .0 5% | b0u9avfd5qydx 2020-12-14 00:09:34 2020-12-14 09:20:23
35 .0 3% | 0x6u28dvh18us 2020-12-14 00:40:52 2020-12-14 00:41:34
31 .0 3% | fs9p5prj0hu06 2020-12-14 00:40:15 2020-12-14 00:40:51
23 .0 2% | cm0qrtprj32qv 2020-12-14 00:55:25 2020-12-14 09:10:53
18 .0 2% | 7y8bs4y43psd2 2020-12-14 01:14:11 2020-12-14 09:20:55
..
--//注其它不能优化我建立sqlid表,放入其中。
XXXX> @ tpt/sqlid b0u9avfd5qydx %
Show SQL text, child cursors and execution stats for SQLID b0u9avfd5qydx child nvl('%','%')
no rows selected
no rows selected
--//居然没有找到，有点奇怪!!
SELECT sql_id, event, COUNT (*)
FROM V$ACTIVE_SESSION_HISTORY
WHERE sql_id = 'b0u9avfd5qydx'
GROUP BY sql_id, event;
SQL_ID EVENT COUNT(*)
------------- ---------------------------------------- ----------
b0u9avfd5qydx row cache lock 1
b0u9avfd5qydx 653
b0u9avfd5qydx SQL*Net break/reset to client 174
b0u9avfd5qydx latch: shared pool 1
--//出现SQL*Net break/reset to client,说明开发写sql语句有错误。只能查询基表x$kglob。
SELECT distinct kglnaobj c200, kglobt03 sql_id
FROM x$kglob
WHERE kglobt03 ='b0u9avfd5qydx';
C120 SQL_ID
----------------------------------------- -------------
PD_LSDL.PDCY b0u9avfd5qydx
PD_LSDL.DDSJ,
PD_LSDL.KLRM,
PD_LSDL.QTID,
FROM PD_LSDL ID,
PD_LSDL.SLGH = :as_slghd ANDND
--//看到的代码有点奇怪，感觉写错了。实际上我们开发很变态的使用\r 而不是\n作为换行，\r只代表回车，显示出现这样的效果。
--//你可以在一些图形界面执行就可以避免上面的情况。
--//改写如下：
SELECT distinct replace(kglnaobj,chr(13),chr(10)) c120, kglobt03 sql_id
FROM x$kglob
WHERE kglobt03 ='b0u9avfd5qydx';
C120 SQL_ID
------------------------------------------- -------------
SELECT PD_LSDL.DLID, b0u9avfd5qydx
PD_LSDL.RDID,
PD_LSDL.RDSJ,
PD_LSDL.PDHM,
PD_LSDL.PDCY,
PD_LSDL.PDZT,
PD_LSDL.KSSJ,
PD_LSDL.JSSJ,
PD_LSDL.DDSJ,
PD_LSDL.WCSJ,
PD_LSDL.JLSJ,
PD_LSDL.SLGH,
PD_LSDL.SLRM,
PD_LSDL.KSID,
PD_LSDL.KSMC,
PD_LSDL.YSID,
PD_LSDL.YSXM,
PD_LSDL.QTID,
PD_LSDL.QTMC,
PD_LSDL.YWLB,
PD_LSDL.YWID,
PD_LSDL.BRID
FROM PD_LSDL
WHERE PD_LSDL.WCSJ >= :adt_Begin AND
PD_LSDL.WCSJ < :adt_End AND
PD_LSDL.SLGH = :as_slgh
XXXX> @ desc portal_his.PD_LSDL
Name Null? Type
------ -------- ----------------------------
1 RDID NOT NULL NUMBER(18)
2 RDSJ NOT NULL DATE
3 PDHM NOT NULL NUMBER(4)
4 PDCY VARCHAR2(40)
5 PDZT NOT NULL NUMBER(1)
6 KSSJ NOT NULL DATE
7 JSSJ DATE
8 DDSJ NOT NULL NUMBER(10)
9 WCSJ DATE
10 JLSJ NOT NULL NUMBER(10)
11 SLGH VARCHAR2(10)
12 SLRM VARCHAR2(10)
13 KSID VARCHAR2(10)
14 KSMC VARCHAR2(50)
15 YSID VARCHAR2(10)
16 YSXM VARCHAR2(30)
17 QTID VARCHAR2(20)
18 QTMC VARCHAR2(40)
19 YWLB VARCHAR2(10)
20 YWID VARCHAR2(20)
21 BRID VARCHAR2(20)
22 ZSID NUMBER(18)
23 ZSMC VARCHAR2(40)
24 MZHM VARCHAR2(20)
25 ZHBZ NUMBER(1)
26 TSBZ NUMBER(1)
27 JZYX NUMBER(1)
--//你可以发现没有DLID字段。这条语句一定很频繁调用，我很奇怪开发怎么测试软件的，这样的错误竟然没有测试出来。
--//我手工执行报如下错误：
ORA-00904: "PD_LSDL"."DLID": invalid identifier
XXXX> ALTER SYSTEM SET EVENTS '904 TRACE NAME ERRORSTACK LEVEL 12';
System altered.
--//等20秒上下，执行如下：
XXXX> ALTER SYSTEM SET EVENTS '904 TRACE NAME ERRORSTACK off';
System altered.
$ grep -l -i pd_lsdl *
alert_XXXX1.log
XXXX1_ora_12024.trc
XXXX1_ora_1285.trc
XXXX1_ora_13499.trc
XXXX1_ora_15495.trc
XXXX1_ora_16252.trc
XXXX1_ora_17345.trc
XXXX1_ora_17523.trc
XXXX1_ora_18962.trc
XXXX1_ora_20266.trc
XXXX1_ora_20895.trc
XXXX1_ora_23503.trc
XXXX1_ora_23559.trc
XXXX1_ora_24481.trc
XXXX1_ora_27712.trc
XXXX1_ora_27897.trc
XXXX1_ora_29415.trc
XXXX1_ora_5775.trc
XXXX1_ora_603.trc
XXXX1_ora_7354.trc
XXXX1_ora_8018.trc
XXXX1_ora_8646.trc
XXXX1_ora_9662.trc
XXXX1_ora_9764.trc
--//顺便打开一个看看：
----- Error Stack Dump -----
ORA-00904: "PD_LSDL"."DLID": 标识符无效
----- Current SQL Statement for this session (sql_id=b0u9avfd5qydx) -----
SELECT PD_LSDL.DLID,^M
PD_LSDL.RDID, ^M
PD_LSDL.RDSJ, ^M
PD_LSDL.PDHM, ^M
PD_LSDL.PDCY, ^M
PD_LSDL.PDZT, ^M
PD_LSDL.KSSJ, ^M
PD_LSDL.JSSJ, ^M
PD_LSDL.DDSJ, ^M
PD_LSDL.WCSJ, ^M
PD_LSDL.JLSJ, ^M
PD_LSDL.SLGH, ^M
PD_LSDL.SLRM, ^M
PD_LSDL.KSID, ^M
PD_LSDL.KSMC, ^M
PD_LSDL.YSID, ^M
PD_LSDL.YSXM, ^M
PD_LSDL.QTID, ^M
PD_LSDL.QTMC, ^M
PD_LSDL.YWLB, ^M
PD_LSDL.YWID,^M
PD_LSDL.BRID^M
FROM PD_LSDL ^M
WHERE PD_LSDL.WCSJ >= :adt_Begin AND^M
PD_LSDL.WCSJ < :adt_End AND^M
PD_LSDL.SLGH = :as_slgh
$ grep ORA-00904 *.trc | cut -d: -f2- | sort| uniq -c
26 ORA-00904: "PD_LSDL"."DLID": 标识符无效
2 ORA-00904: "XTXH": 标识符无效
1 ORA-00904: "ZDLB": 标识符无效
--//还有2处是别的表引起的错误。
--//在我完成测试后，再次查看ashtop测试：
XXXX> @ ashtop sql_id "machine<>'IMC' and sql_id not in (select sqlid from scott.sqlid where flag=0)" trunc(sysdate) trunc(sysdate)+1
Total
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
240 .0 15% | b0u9avfd5qydx 2020-12-14 00:09:34 2020-12-14 09:58:51
35 .0 2% | 0x6u28dvh18us 2020-12-14 00:40:52 2020-12-14 00:41:34
31 .0 2% | fs9p5prj0hu06 2020-12-14 00:40:15 2020-12-14 00:40:51
--//已经增加到240秒。真心不知道这种情况如何看到执行调用的频率。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69990217/viewspace-2742778/ ，如需转载，请注明出处，否则将追究法律责任。
