RE: Tales Of Big Hammer #10046 (AKA event 10046)

Raj,

The application may have interpreted this as ORA-01403, but I've no clue as to why the "SQL*Net break/reset to client" occurred. You can see about 7 seconds of wait for response from the client, after which a rollback occurs...

The FETCH shows "r=1" indicating that at least one row was returned. Was the application doing "array fetches" for that statement? Was a row returned? Is this PL/SQL or ???

It is interesting how these traces feel very much like archaeology; just have to use a delicate hammer to knock the bits loose...

-Tim

>
> Tim,
>
> I don't see that ... I know this sql caused the problem
> ..
> ==========================================================
> ================== ===============================
> PARSING IN CURSOR #54 len=238 dep=1 uid=44 oct=3 lid=44
> tim=1016624815165467 hv=2089161539 ad='24ba3ad8'
> SELECT b.pr_mobility_code
> FROM prp_requests b,
> prp_sr_cal_pps a
> WHERE b.pr_req_unit_id = :b3
> AND b.pr_gr_id = a.pscp_id
> AND a.pscp_prp_id = :b2
> AND a.pscp_prp_version = :b1
> END OF STMT
> PARSE
> #54:c=0,e=1192,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1016
> 624815165463 BINDS #54:
> bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03
> oacfl2=1 size=72 offset=0
> bfp=11054edb0 bln=22 avl=05 flg=05
> value=9044628
> bind 1: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03
> oacfl2=1 size=0 offset=24
> bfp=11054edc8 bln=22 avl=04 flg=01
> value=586823
> bind 2: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03
> oacfl2=1 size=0 offset=48
> bfp=11054ede0 bln=22 avl=02 flg=01
> value=2
> EXEC
> #54:c=0,e=1681,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1016
> 624815167288 WAIT #54: nam='global cache cr request' ela=
> 417 p1=32 p2=500383 p3=504403158449678016
> WAIT #54: nam='db file sequential read' ela= 2816 p1=32
> p2=500383 p3=1 WAIT #54: nam='global cache cr request'
> ela= 445 p1=33 p2=517103 p3=504403158399168096
> WAIT #54: nam='db file sequential read' ela= 1858 p1=33
> p2=517103 p3=1 WAIT #54: nam='db file sequential read'
> ela= 781221 p1=31 p2=1251231 p3=1 FETCH
> #54:c=0,e=787456,p=3,cr=17,cu=0,mis=0,r=1,dep=1,og=4,tim=1
> 016624815954763 WAIT #0: nam='SQL*Net break/reset to
> client' ela= 57 p1=675562835 p2=1 p3=0 WAIT #0:
> nam='SQL*Net break/reset to client' ela= 630 p1=675562835
> p2=0 p3=0 WAIT #0: nam='SQL*Net message to client' ela= 1
> p1=675562835 p2=1 p3=0 *** 2002-12-27 16:17:21.846
> WAIT #0: nam='SQL*Net message from client' ela= 29596938
> p1=675562835 p2=1 p3=0
> =====================
> PARSING IN CURSOR #45 len=8 dep=0 uid=3318 oct=45 lid=3318
> tim=1016624845553757 hv=1226881397 ad='24f0b6b8'
> ROLLBACK
> END OF STMT
> PARSE
> #45:c=0,e=412,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=10166
> 24845553752 XCTEND rlbk=1, rd_only=1
> EXEC
> #45:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=101662
> 4845553883 WAIT #45: nam='SQL*Net message to client' ela=
> 3 p1=675562835 p2=1 p3=0 WAIT #45: nam='SQL*Net message
> from client' ela= 13212 p1=675562835 p2=1 p3=0
> =====================
> PARSING IN CURSOR #45 len=8 dep=0 uid=3318 oct=45 lid=3318
> tim=1016624845567343 hv=1226881397 ad='24f0b6b8'
> ROLLBACK
> END OF STMT
> PARSE
> #45:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=101662
> 4845567339 XCTEND rlbk=1, rd_only=1
> EXEC
> #45:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=101662
> 4845567450 WAIT #45: nam='SQL*Net message to client' ela=
> 1 p1=675562835 p2=1 p3=0 WAIT #45: nam='SQL*Net message
> from client' ela= 6733 p1=675562835 p2=1 p3=0 STAT #43
> id=1 cnt=1 pid=0 pos=1 obj=29433 op='TABLE ACCESS BY INDEX
> ROWID SELLING_ROTATIONS (cr=3 r=1 w=0 time=14786 us)'
> STAT #43 id=2 cnt=1 pid=1 pos=1 obj=422698 op='INDEX
> UNIQUE SCAN SR_PK_PRIM (cr=2 r=0 w=0 time=27 us)'
>
> Thanks for the explanation ...
> Raj
> ______________________________________________________
>
> Rajendra Jamadagni MIS, ESPN Inc.
>
> Rajendra dot Jamadagni at ESPN dot com
>
> Any opinion expressed here is personal and doesn't reflect
> that of ESPN Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an
> art!
> -----Original Message-----
> Sent: Monday, December 30, 2002 7:49 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Generally you won't find "err=1403" text in the raw ".trc"
> file. Instead, if you carefully examine the FETCH lines,
> you'll see "r=0" (i.e. zero rows returned) in amongst all
> the other statistics. Very very difficult to catch and
> often requires a Vulcan mind-meld to the application over
> several hours of careful perusal (something best left to
> Vulcans)...
>
> Great job!
>
>
> [Attachment: ESPN_Disclaimer.txt]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).