[20181120]SQLNet break/reset to client.txt
--//最近一直在做优化工作,最后剩下SQL*Net break/reset to client.排在第5.
--//出现这种情况SQL*Net break/reset to client实际上相关的sql语句根本没有正确执行，查询V$sql视图是无法获得sql语句的。
--//要查询底层视图 x$kglob.
1.环境:
SYSTEM@zzzzzz > @ 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
SELECT sql_id, COUNT (*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE event = 'SQL*Net break/reset to client'
GROUP BY sql_id
ORDER BY 2 DESC;
SQL_ID COUNT(*)
------------- ----------
8gvfr81z8nfs7 151
gftx8vhbhujf3 7
ddd4xgabw2tct 5
c21vhszr9gbdq 1
--//可以发现主要集中在sql_id=8gvfr81z8nfs7.
SYSTEM@zzzzzz > @ sql_id 8gvfr81z8nfs7
no rows selected
--//查询v$sqlarea视图没有发现.
SYSTEM@zzzzzz > @ sql_id gftx8vhbhujf3
SQL_ID SQLTEXT
------------- --------------------------------------------------------------------------
gftx8vhbhujf3 Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )
--//sql_id='gftx8vhbhujf3'可以发现.
2.继续分析:
SELECT DISTINCT kglnaobj c120 , kglobt03 sql_id
FROM x$kglob
WHERE kglobt03 IN ( SELECT sql_id
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE event = 'SQL*Net break/reset to client'
GROUP BY sql_id);
C120 SQL_ID
------------------------------------------------------------------------------------------------------------------------ -------------
SELECT PBE_NAME,PBE_EDIT,PBE_TYPE,PBE_CNTR,PBE_WORK,PBE_SEQN,PBE_FLAG FROM SYSTEM.PBCATEDT ORDER BY PBE_NAME,PBE_SEQN 8gvfr81z8nfs7
Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 ) gftx8vhbhujf3
select count ( :"SYS_B_0" ) from yk_gnt where ypxh =:1 ddd4xgabw2tct
Select csz2 From gy_xtcs where xtxh =:"SYS_B_0" And csmc =:"SYS_B_1" c21vhszr9gbdq
3.问题解决:
--//sql_id='8gvfr81z8nfs7',主要因为SYSTEM.PBCATEDT没有建立,在应用scahma下有一个相似的表.建立一样的结构就ok了.
SYSTEM@zzzzzz > create table SYSTEM.PBCATEDT tablespace users as select * from XXXXXX_YYY.PBCATEDT where 1=2;
Table created.
--//sql_id='ddd4xgabw2tct',根本不存在这个表以及对象.
SYSTEM@zzzzzz > select * from dba_objects where object_name='YK_GNT';
no rows selected
--//sql_id=c21vhszr9gbdq
SYSTEM@zzzzzz > select * from dba_objects where object_name='GY_XTCS';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
---------- ----------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
XXXXXX_YYY GY_XTCS 81538 81538 TABLE 2015-01-28 10:11:59 2018-04-02 14:48:16 2018-04-02:14:48:16 VALID N N N 1
SYSTEM@zzzzzz > @ desc XXXXXX_YYY.gy_xtcs
Name Null? Type
----- -------- ----------------------------
CSMC NOT NULL VARCHAR2(20)
CSZ VARCHAR2(150)
MRZ VARCHAR2(100)
BZ VARCHAR2(80)
--//sql_id=c21vhszr9gbdq,开发写的什么语句,csz2字段根本不存在,xtxh字段也不存在.无语.什么验证的.
--//剩下sql_id='gftx8vhbhujf3',有点奇怪,我给审计跟踪看看.因为
SYSTEM@zzzzzz > select sql_id,sql_text,executions,rows_processed from v$sqlarea where sql_id='gftx8vhbhujf3';
SQL_ID SQL_TEXT EXECUTIONS ROWS_PROCESSED
------------- -------------------------------------------------------------------------------- ---------- --------------
gftx8vhbhujf3 Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 ) 13919 0
--//EXECUTIONS=13919,ROWS_PROCESSED=0? 另外写一篇.