Oracle-L: Re: Tales Of Big Hammer #10046 (AKA event 10046)

From: Arup Nanda <arupnanda_at_hotmail.com>
Date: Tue, 31 Dec 2002 05:58:40 -0800
Message-ID: <F001.005249D1.20021231055840@fatcity.com>

Tales Of Big Hammer #10046 (AKA event 10046)Raj,

Thanks for sharing this with us. In your original post you mentioned that you saw a "rollback" right after the "SQL*Net break/reset to client" and that was how you figured out the 1403 error. I don't see that in this excerpt from the raw trace file, though. How did you determine it was this particulat SQL? Also the FETCH#54 line you have highlighted has r=1, not r=0; what's special about that line?

Thanks in advance.

Arup

Original Message ----- From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 31, 2002 7:58 AM Subject: RE: Tales Of Big Hammer #10046 (AKA event 10046)

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=1016624815165463   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=1016624815167288
  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=1016624815954763

  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=1016624845553752 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=1016624845553883 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=1016624845567339 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=1016624845567450 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-----
From: Tim Gorman [mailto:Tim_at_SageLogix.com] Sent: Monday, December 30, 2002 7:49 PM To: Multiple recipients of list ORACLE-L Subject: Re: Tales Of Big Hammer #10046 (AKA event 10046)

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!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Arup Nanda
INET: arupnanda_at_hotmail.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). Received on Tue Dec 31 2002 - 07:58:40 CST