Re: SQL without end

Hi,
sorry my hold up.
Operational system: Windows-server 2003 and SUSE 10.2 Oracle db: Oracle 10g - 10.2.0.1.0
Hardware: CPU: 2 x Intel Core2 CPU 6400_at_2.13GHz

                MEM: 4GB

I try to rewrite SQL but the problem not in specific join. Problem is realy in quantity JOINs. This sql is generated from SQL generator from java aplication. I can't change SQL. This SQL running about 6 days. I have enable SQL trace but anything is generated. Only in enterprice manager I see db file sequential read on system Tablespace.

Roman Hodain

DA Morgan napsal:
> romanhodain_at_email.cz wrote:
> > I know that this is very much OUTER JOIN bad I don't know why Oracle
> > can't generate execution plan? Is oacle use 100% CPU to generate
> > execution plan in fact? This SQL run about 14 hours. On mssql is this
> > SQL complet in 5 sec. Is possible that this is a bug?
>
> Anything is possible but you've provided no metrics. No version number,
> no operating system, no information on hardware resources available and
> usage. So while it "could" be a bug it is very premature to jump that
> direction.
>
> I would suggest the following:
>
> Dump the ANSI syntax and start by building a small SQL statement that
> contains the query's basics. Wrap it in parentheses (an inline view) and
> join it with a second query. Wrap that in parentheses and continue on
> including more and more of this until it either works or breaks.
>
> Not Czech Republic language I can't comment on your business logic but
> my inclination is to agree with Sybrand that this problem should be
> fixed by coming up with a proper design. I have never seen anything in
> Oracle requiring this kind of logic and suspect much of your problem
> may relate to trying to kludge Oracle into being another company's product.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Tue Jan 23 2007 - 05:52:36 CST