Skip to Main Content
  • Questions

  • Event SQL*Net break/reset to client in refresh of materialized view

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Juliana.

Asked: March 29, 2016 - 7:22 pm UTC

Last updated: August 20, 2018 - 4:15 am UTC

Version: Oracle Database 12C

Viewed 10K+ times! This question is

You Asked

Dear,

I have had event of "SQL*Net break/reset to client" in session (job) with refresh of materialized view
Same after kill it the session, kill the running job, the new session again remains with this event.
The solution always is re-create the materialized view.
I have not found another solution.

Note: Oracle Database 12c.

Any idea?

Thanks.

Juliana.

and Connor said...

SQL*Net break/reset to client" normally means an error was encountered, so we effectively tell all parties involved (ie, the server and the client) to "reset" and get back to a 'happy' place so we can start afresh.

For example, if I drop a non-existent table, the trace shows this:

=====================
PARSING IN CURSOR #362217712 len=22 dep=0 uid=102 oct=12 lid=102 tim=1168973915843 hv=734550976 ad='b65fe00' sqlid='4k9pkhcpwhqy0'
drop table NOT_A_TABLE
END OF STMT
PARSE #362217712:c=0,e=596,p=0,cr=0,cu=1,mis=1,r=0,dep=0,og=1,plh=0,tim=1168973915842
EXEC #395118808:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=1168973916137
FETCH #395118808:c=0,e=19,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=813480514,tim=1168973916184
CLOSE #395118808:c=0,e=2,dep=1,type=3,tim=1168973916219
=====================
...
...

PARSE #419110816:c=0,e=3085,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1168973945367
EXEC #419110816:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=0,tim=1168973945849
CLOSE #419110816:c=0,e=7,dep=1,type=0,tim=1168973945906
WAIT #362217712: nam='SQL*Net break/reset to client' ela= 6 driver id=1111838976 break?=1 p3=0 obj#=663 tim=1168973945984
WAIT #362217712: nam='SQL*Net break/reset to client' ela= 35 driver id=1111838976 break?=0 p3=0 obj#=663 tim=1168973946055
WAIT #362217712: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=663 tim=1168973946097
WAIT #362217712: nam='SQL*Net message from client' ela= 92 driver id=1111838976 #bytes=1 p3=0 obj#=663 tim=1168973946236


So put a trace on, and see what error lead to the reset.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Abrupt "SQL*Net break/reset to client" event

Jitu Keshwani, August 06, 2018 - 12:21 pm UTC

Hi Connor,
Need your advice on how to proceed with investigation of this issue further.

I get this event abruptly (atleast, seems so as of now). Abruptly, because I see other operations being performed in trace before and after this event. And due to high ela of this event, app sessions get timed out.
Have provided the excerpt from trace file below:

1. Why break/reset takes 1839 secs ?
2. Why it suddenly appears while other operations were in progress ?

I can provide the entire trace file if required. But didn't know how to upload it here.

STAT #139905122835312 id=59 cnt=18 pid=58 pos=1 obj=113474 op='INDEX RANGE SCAN CSM_OFFER_1IX (cr=3 pr=0 pw=0 time=8 us cost=2 size=0 card=4)'
STAT #139905122835312 id=60 cnt=1 pid=44 pos=2 obj=94172 op='INDEX UNIQUE SCAN TARFF_CALC_PK (cr=2 pr=0 pw=0 time=9 us cost=0 size=0 card=1)'
STAT #139905122835312 id=61 cnt=1 pid=43 pos=2 obj=94169 op='TABLE ACCESS BY INDEX ROWID TARIFF_CALC (cr=1 pr=0 pw=0 time=9 us cost=1 size=165 card=1)'
CLOSE #139905122835312:c=18,e=19,dep=1,type=0,tim=7243220076700
WAIT #139905123183048: nam='gc buffer busy acquire' ela= 687 file#=213 block#=508971 class#=1 obj#=350766 tim=7243220087669
WAIT #139905123183048: nam='latch: cache buffers chains' ela= 4 address=569290018016 number=228 tries=0 obj#=350766 tim=7243220087726
WAIT #139905123183048: nam='gc buffer busy acquire' ela= 528 file#=213 block#=508975 class#=1 obj#=350766 tim=7243220088274
WAIT #139905123183048: nam='gc cr block 2-way' ela= 389 p1=213 p2=508983 p3=1 obj#=350766 tim=7243220088776
WAIT #139905123183048: nam='gc buffer busy acquire' ela= 1171 file#=213 block#=508991 class#=1 obj#=350766 tim=7243220090146
WAIT #139905123183048: nam='gc buffer busy acquire' ela= 359 file#=213 block#=509009 class#=1 obj#=350766 tim=7243220090691
WAIT #139905123183048: nam='gc buffer busy acquire' ela= 1013 file#=213 block#=509011 class#=1 obj#=350766 tim=7243220091753
WAIT #139905123183048: nam='gc cr block 2-way' ela= 1441 p1=213 p2=509017 p3=1 obj#=350766 tim=7243220093284
WAIT #139905123183048: nam='gc cr block 2-way' ela= 391 p1=213 p2=509023 p3=1 obj#=350766 tim=7243220093912
EXEC #139905123183048:c=13841,e=19379,p=0,cr=1635,cu=2,mis=0,r=1,dep=1,og=1,plh=1534810539,tim=7243220096195
CLOSE #139905123183048:c=2,e=3,dep=1,type=3,tim=7243220096360
PARSE #139905124702736:c=7,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=7243220096424
XCTEND rlbk=0, rd_only=0, tim=7243220096465
EXEC #139905124702736:c=143,e=142,p=0,cr=0,cu=1,mis=0,r=0,dep=1,og=0,plh=0,tim=7243220096604
CLOSE #139905124702736:c=2,e=1,dep=1,type=3,tim=7243220096657
WAIT #139905123477216: nam='SQL*Net message to client' ela= 2 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=7243220096728
EXEC #139905123477216:c=54730,e=3716129,p=21,cr=2615,cu=34,mis=0,r=1,dep=0,og=1,plh=0,tim=7243220096975
XCTEND rlbk=0, rd_only=1, tim=7243220097040
WAIT #139905123477216: nam='log file sync' ela= 106 buffer#=155817 sync scn=2366658610 p3=0 obj#=-1 tim=7243220097200
WAIT #139905123477216: nam='SQL*Net message from client' ela= 40 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=7243220097423

*** 2018-08-03 19:16:17.222
WAIT #139905123477216: nam='SQL*Net break/reset to client' ela= 1839835333 driver id=1952673792 break?=0 p3=0 obj#=-1 tim=7245059932812
STAT #139905124520192 id=1 cnt=0 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION (cr=174 pr=0 pw=1 time=1185 us)'
STAT #139905124520192 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT (cr=174 pr=0 pw=1 time=997 us)'
STAT #139905124520192 id=3 cnt=1 pid=2 pos=1 obj=0 op='SORT ORDER BY (cr=174 pr=0 pw=0 time=437 us cost=80 size=73 card=1)'
STAT #139905124520192 id=4 cnt=1 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=174 pr=0 pw=0 time=416 us cost=79 size=73 card=1)'
STAT #139905124520192 id=5 cnt=25 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=149 pr=0 pw=0 time=845 us cost=79 size=73 card=9)'
STAT #139905124520192 id=6 cnt=25 pid=5 pos=1 obj=0 op='NESTED LOOPS (cr=101 pr=0 pw=0 time=619 us cost=61 size=495 card=9)'
STAT #139905124520192 id=7 cnt=1 pid=6 pos=1 obj=113484 op='TABLE ACCESS BY INDEX ROWID BATCHED SUBSCRIBER (cr=4 pr=0 pw=0 time=25 us cost=5 size=50 card=2)'
STAT #139905124520192 id=8 cnt=1 pid=7 pos=1 obj=113488 op='INDEX RANGE SCAN SUBSCRIBER_L9_1IX (cr=3 pr=0 pw=0 time=19 us cost=3 size=0 card=2)'
STAT #139905124520192 id=9 cnt=25 pid=6 pos=2 obj=113507 op='TABLE ACCESS BY INDEX ROWID BATCHED SERVICE_AGREEMENT (cr=97 pr=0 pw=0 time=568 us cost=28 size=150 card=5)'
STAT #139905124520192 id=10 cnt=93 pid=9 pos=1 obj=114247 op='INDEX RANGE SCAN SERVICE_AGREEMENT_PK (cr=5 pr=0 pw=0 time=103 us cost=3 size=0 card=29)'
STAT #139905124520192 id=11 cnt=25 pid=5 pos=2 obj=113475 op='INDEX UNIQUE SCAN CSM_OFFER_PK (cr=48 pr=0 pw=0 time=94 us cost=1 size=0 card=1)'
STAT #139905124520192 id=12 cnt=1 pid=4 pos=2 obj=113473 op='TABLE ACCESS BY INDEX ROWID CSM_OFFER (cr=25 pr=0 pw=0 time=45 us cost=2 size=18 card=1)'


Connor McDonald
August 20, 2018 - 4:15 am UTC

The wait is the time of:

- we got an error
- we need to tell the client
- client says "Yes, I'm good to resume"

So if we cannot get a response back from your client for some reason (network, dead client, client busy doing something else), this will all add to the wait time.

In this case, we got something back from 1800 seconds.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.