RE: Weird Behavior

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 10 Dec 2004 09:46:29 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410511E6BD@usahm236.amer.corp.eds.com>

One common problem that usually results in generating an extremely long explain plan where the plan should not be that long is to end the SQL with a semicolon and then follow the SQL statement with a "/". Basically you double execute the explain and then attempt to query the result via a connect by which can result in the ORA-01436 "CONNECT BY loop in user data" error.

Since I upgraded to version 9.2 I have also been receiving ORA-00942 errors whenever I explain an SQL statement that references a SYS owned base table. A direct grant by sys eliminates this error.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark Sent: Thursday, December 09, 2004 5:16 PM To: SauerBL_at_ldschurch.org; oracle-l_at_freelists.org Subject: RE: Weird Behavior

Overquoted before. What I said was:
First:
=20
OS? OS version? Database version?
Always, always, always! ;-)

Second:

Define "fail", as used below. =20
Does it crash? If so, with what error?
Does it hang? If so, what's V$SESSION_WAIT have to say about the session trying to do the explain plan?

Hope that helps,

-Mark
> > I have two tables with basic views on top of them. And I=20
> > need to run some
> > basic
> > sql against the database. The stuff you see in the body of=20
> > the explain plan.
> > Neither table is that big. About 2000 records in each of=20
> > them. The weird
> > part
> > is that even trying to run an explain plan locks up whatever=20
> > I'm running it
> > from. =20
> > =20
> > Does anybody have any idea what would make an explain plan=20
> > request fail and
> > what to do about it? =20
> > =20
> > Thanks, Ben
> > =20
> > This is what crashes. (With or without the first line) =20
> > [SMSYSPLAN is my
> > plan_table]
> > =20
> > explain plan set statement_id =3D 'cr_weird' into SMSYSPLAN for
> > SELECT
> > Clients."Title", Clients."InActive:", Clients."Client=20

> Full Name",


> > Clients."Classification", Clients."Home Country Name",

> >     Incident."InActive:", Incident."Visit Date", Incident."GA=20

> > Host Text"
> > FROM
> > "_SMDBA_"."Clients" Clients,
> > "_SMDBA_"."Incident" Incident
> > WHERE
> > Clients."Sequence" =3D Incident."Seq.Client" (+)
> > AND
> > Incident."Visit Date" >=3D TO_DATE('2004-12-01 00:00:00',=20
> > 'YYYY-MM-DD
> > HH:MI:SS') AND
> > Incident."Visit Date" < TO_DATE('2004-12-10 00:00:00',=20

> 'YYYY-MM-DD


> > HH:MI:SS')  AND

> >     Clients."InActive:" =3D 0=20

> > AND

> >     Incident."InActive:" =3D 0

> > ORDER BY

> >     Clients."Classification" ASC

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 10 2004 - 08:48:11 CST