2012-01-20

A single serializable session throwing ORA-08177

We have a newly created table in a 11.2.0.3 Enterprise edition instance. A single session is throwing ORA-08177 can't serialize access for this transaction. The whole service level is marked for trace.

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('servicename')

No one else is updating the tables involved. There comes out only two trace files. One having only the select 1 from dual connection pool check. And in the another trace there may be found.
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE
END OF STMT
Yes we wanted transactions to be serialized. Couple of inserts and internal staements. And an update to seg$ table thowing ORA-08177.
PARSING IN CURSOR #47594977567928 len=314 dep=1 uid=0 oct=6 lid=0 tim=1327068762208173 hv=3096556448 ad='de83deb8' sqlid='0kkhhb2w93cx0'
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL
, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where t
s#=:1 and file#=:2 and block#=:3
END OF STMT
PARSE #47594977567928:c=0,e=673,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1327068762208172
EXEC #47594977567928:c=1999,e=1411,p=0,cr=3,cu=1,mis=1,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209667
STAT #47594977567928 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  SEG$ (cr=3 pr=0 pw=0 time=116 us)'
STAT #47594977567928 id=2 cnt=1 pid=1 pos=1 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=16 us cost=2 size=68 card=1)'
STAT #47594977567928 id=3 cnt=1 pid=2 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=6 us cost=1 size=0 card=1)'
CLOSE #47594977567928:c=0,e=2,dep=1,type=3,tim=1327068762209778
EXEC #47594977567928:c=0,e=125,p=0,cr=3,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762209931
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762209972
EXEC #47594977567928:c=0,e=129,p=0,cr=4,cu=1,mis=0,r=1,dep=1,og=4,plh=2170058777,tim=1327068762210127
CLOSE #47594977567928:c=0,e=1,dep=1,type=3,tim=1327068762210167
EXEC #47594984047704:c=50992,e=50764,p=7,cr=576,cu=241,mis=1,r=0,dep=0,og=1,plh=0,tim=1327068762211409
ERROR #47594984047704:err=8177 tim=1327068762211441
STAT #47594984047704 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=1 us)'

*** 2012-01-20 16:12:42.223
WAIT #47594984047704: nam='log file sync' ela= 11537 buffer#=84 sync scn=604674242 p3=0 obj#=-1 tim=1327068762223055
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 21 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=1327068762223170
WAIT #47594984047704: nam='SQL*Net break/reset to client' ela= 3715 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=1327068762226909

After awhile the problem vanished... No solutions in this post. Just wondering why...

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.