Re: Debug ORA-03113 on Oracle XE

From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Wed, 17 Jun 2009 17:21:21 GMT
Message-ID: <lC9_l.32484$PH1.20031_at_edtnps82>

"joel garry" <joel-garry_at_home.com> wrote in message news:fa016d85-ccfe-4ed1-9754-acb7e774e9d7_at_j18g2000yql.googlegroups.com... On Jun 17, 7:57 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> CarlosAL escribió:
>
>
>
> > On 17 jun, 14:03, "Álvaro G. Vicario"
> > <alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> >> Álvaro G. Vicario escribió:
>
> >>> I have a web application written in PHP that's failing on one computer
> >>> (and only that computer): a laptop that runs the Oracle 10g Express
> >>> Edition. It fails when it executes a specific SELECT query: I get a
> >>> "ORA-03113 end-of-file on communication channel" error and the
> >>> connection drops.
> >>> My main concern is that I can't figure out what to do next. It seems
> >>> that ORA-03113 is a very generic error that can happen for lots of
> >>> reasons.
> >>> The query returns no rows with the current data set but it's pretty
> >>> complex (lots of joins and subqueries). However, I only get the
> >>> ORA-03113 error when I run it through PHP and Apache. When I run it
> >>> from
> >>> SQL Developer I get no error and the results come almost instantly.
> >>> The
> >>> PHP backtrace doesn't provide additional info. The PHP and Apache
> >>> versions do not seem to matter. There're neither antivirus nor
> >>> firewall
> >>> in the laptop. If Oracle XE generates further info I don't know where
> >>> I
> >>> can find it.
> >> I've been able to kind of isolate the problem. I'm running two queries
> >> and both involve a temporary table. This is the flow:
>
> >> 1. INSERT data in a temporary table
> >> 2. Run a SELECT joining several tables (including the temp table)
>
> >> It's a transaction temporary table and its data gets discarded because
> >> it's no longer needed.
>
> >> - Using a session (rather than transaction) temporary table makes no
> >> difference.
> >> - If I ROLLBACK before the query there's no ORA-03113 (but of course
> >> the
> >> temporary data is gone).
> >> - If I use a regular table there's no ORA-03113 (though I'll need to
> >> redesign the module to separate the data from the different sessions
> >> and
> >> remove it when no longer need it).
>
> >> The redesign is always an option but... What server settings do you
> >> think that could be causing the ORA-03113 when using temporary tables?
> > I'm not to fond to global temporary tables. Is there a sound reason
> > for using them?
>
> The code was the result of refactoring a megahuge query that took 15
> minutes to run by dividing it in two big queries that took 5 seconds
> each. As about temporary vs regular, I just read Oracle had such feature
> and I figured out it could be handy: automagical session isolation and
> data removal looked cool at the time.

Yes, it is a cool feature.

>
> > They are stored in temporary segments, so you'd better
> > check your configuration on this.
>
> Thank you, I'll look into it.
>

Since it is replicable on different installations, and is dependent on specific SQL as well as temporary segments, it is likely one of several things (if indeed it is Oracle):

Temporary segment configuration, as Carlos suggested. This could have something to do with the limitations of XE as well, perhaps you could download SE and EE for testing purposes, and see if they have the same problem. Somewhere I got the idea XE is a full-featured Oracle with some memory and disk limitations, which could mean they simply didn't get that quite right for temporary segments. See the interesting thread
http://forums.oracle.com/forums/thread.jspa?threadID=910059&start=0&tstart=0 for some ideas of what to play with - perhaps turning off pga management and using sort_area instead, to force things out of memory onto disk with these complex SQL could slow it down but make it work.

Wrong results, optimizer or temp seg bug. If you can, see metalink Note: 401436.1 and you can drill down to look at various interesting bugs. If SE or XE has the same problem, you could patch to 10.2.0.4 and see if it is fixed. It is worth it to get metalink access, even if you are just using XE.

Of course, since you now have a workaround, you may not want to bother.

Normally, to get support, you would have to come up with a replicable test case including ddl and data on the latest supported patch set. It may be worthwhile doing that and letting the XE people on OTN know about it, so they can try it on the 11 version.

jg

--
_at_home.com is bogus.
http://www3.signonsandiego.com/stories/2009/jun/17/1b17myspace212851-myspace-lay-nearly-30-percent-st/?uniontrib


XE is most definitely not EE with memory/disk limitations.  It is actually 
missing functionality.  One that I ran across was the absence of the 
utl_file package.

-- 
Terry Dykstra

Received on Wed Jun 17 2009 - 12:21:21 CDT