We have business need to do some countings.

It is using MERGE JOIN [CARTESIAN], it will go fast with 10.000 count, but
with 200.000 end sum it will take very long time.
It is using Reports 6i (a bit old

It does utilize very complez views etc, and plan is long as you would
assume.
I am not going to send plan.

One solution would be redesing it, but not quite sure it would help.

Server is 9.2.0.8 (multi prosessor, quite good one), parameters are now very
small, and
not optimized.

Is there some good parameters in server side which could help?

On Jan 31, 1:02*am, "Jack" <n...@INVALIDmail.com> wrote:
> We have business need to do some countings.
>
> It is using *MERGE JOIN [CARTESIAN], it will go fast with 10.000 count, but
> with 200.000 end sum it will take very long time.
> It is using Reports 6i (a bit old
>
> It does utilize very complez views etc, and plan is long as you would
> assume.
> I am not going to send plan.
>
> One solution would be redesing it, but not quite sure it would help.
>
> Server is 9.2.0.8 (multi prosessor, quite good one), parameters are now very
> small, and
> not optimized.
>
> Is there some good parameters in server side which could help?

Who could know? No one here, not with the details you presented (or,
more appropriately, failed to present). If you're not going to post
the explain plan and the init.ora parameters you do have set then
you're really on your own.

Oracle Corp. has stopped distributing crystal balls to DBAs.


David Fitzjarrell

<fitzjarrell@cox.net> wrote in message
news:17ab5b69-4d79-4da6-af83-b30852459c87@e25g2000prg.googlegroups.com...
On Jan 31, 1:02 am, "Jack" <n...@INVALIDmail.com> wrote:
> We have business need to do some countings.
>
> It is using MERGE JOIN [CARTESIAN], it will go fast with 10.000 count, but
> with 200.000 end sum it will take very long time.
> It is using Reports 6i (a bit old
>
> It does utilize very complez views etc, and plan is long as you would
> assume.
> I am not going to send plan.
>
> One solution would be redesing it, but not quite sure it would help.
>
> Server is 9.2.0.8 (multi prosessor, quite good one), parameters are now
> very
> small, and
> not optimized.
>
> Is there some good parameters in server side which could help?

Who could know? No one here, not with the details you presented (or,
more appropriately, failed to present). If you're not going to post
the explain plan and the init.ora parameters you do have set then
you're really on your own.

Oracle Corp. has stopped distributing crystal balls to DBAs.


David Fitzjarrell
-------------------------------

Ora parameters are more or less in default-values.

Oracle Corp. does deliver time machines , and at least I got also crystal
ball

I assume that cartesian join uses pga-memory, perhaps some paraller paramers
migth be also usefull.

In article <Kneoj.40$Ll.3@read4.inet.fi>, "Jack" <none@INVALIDmail.com> wrote:
>We have business need to do some countings.
>
>It is using MERGE JOIN [CARTESIAN], it will go fast with 10.000 count, but
>with 200.000 end sum it will take very long time.
>It is using Reports 6i (a bit old
>
The only way to truly "optimize" a Cartesian join is to eliminate it, by
creating an index for at least one of the tables, on the column(s) used in the
join.

On Jan 31, 2:02 am, "Jack" <n...@INVALIDmail.com> wrote:
> Is there some good parameters in server side which could help?

_fast=true

"Jack" <none@INVALIDmail.com> kirjoitti
viestissä:Kneoj.40$Ll.3@read4.inet.fi...
> We have business need to do some countings.
>
> It is using MERGE JOIN [CARTESIAN], it will go fast with 10.000 count,
> but
> with 200.000 end sum it will take very long time.
> It is using Reports 6i (a bit old
>
> It does utilize very complez views etc, and plan is long as you would
> assume.
> I am not going to send plan.
>
> One solution would be redesing it, but not quite sure it would help.
>
> Server is 9.2.0.8 (multi prosessor, quite good one), parameters are now
> very small, and
> not optimized.
>
> Is there some good parameters in server side which could help?
>

"Doug Miller" <spambait@milmac.com> kirjoitti
viestissä:laosj.115$Mw.13@nlpi068.nbdc.sbc.com...
> In article <Kneoj.40$Ll.3@read4.inet.fi>, "Jack" <none@INVALIDmail.com>
> wrote:
>>We have business need to do some countings.
>>
>>It is using MERGE JOIN [CARTESIAN], it will go fast with 10.000 count,
>>but
>>with 200.000 end sum it will take very long time.
>>It is using Reports 6i (a bit old
>>
> The only way to truly "optimize" a Cartesian join is to eliminate it, by
> creating an index for at least one of the tables, on the column(s) used in
> the join.

Hi

Thanks Doug for your advice.
It does have good indexes. (but not in remarksfield

Now after some basic parameter changes it is running 3,3x faster.

It does not like more memory or paraller query.

Any good advices, what next?

On Feb 22, 1:12*pm, "Jack" <J...@none.com> wrote:
> > We have business need to do some countings.
>
> > It is using *MERGE JOIN [CARTESIAN], it will go fast with 10.000 count,
> > but
> > with 200.000 end sum it will take very long time.
> > It is using Reports 6i (a bit old
>
> > It does utilize very complez views etc, and plan is long as you would
> > assume.
> > I am not going to send plan.
>
> > One solution would be redesing it, but not quite sure it would help.
>
> > Server is 9.2.0.8 (multi prosessor, quite good one), parameters are now
> > very small, and
> > not optimized.
>
> > Is there some good parameters in server side which could help?
>
> "Doug Miller" <spamb...@milmac.com> kirjoitti
> viestissä:laosj.115$Mw...@nlpi068.nbdc.sbc.com...
>
> > In article <Kneoj.40$L...@read4.inet.fi>, "Jack" <n...@INVALIDmail.com>
> > wrote:
> >>We have business need to do some countings.
>
> >>It is using *MERGE JOIN [CARTESIAN], it will go fast with 10.000 count,
> >>but
> >>with 200.000 end sum it will take very long time.
> >>It is using Reports 6i (a bit old
>
> > The only way to truly "optimize" a Cartesian join is to eliminate it, by
> > creating an index for at least one of the tables, on the column(s) used in
> > the join.
>
> Hi
>
> Thanks Doug for your advice.
> It does have good indexes. (but not in remarksfield
>
> Now after some basic parameter changes it is running 3,3x faster.
>
> It does not like more memory or paraller query.
>
> Any good advices, what next?

Merge Join Cartesian/Sort Merge Joins are typically best used when one
of the two data sources contains a very small number of rows (1, 2, 3,
etc.). Think of it this way, if the first data source has 100 rows,
and the second also has 100 rows, a merge join Cartesian will create a
resultset with 100 * 100 = 10,000 rows, and then filter out the rows
that should not be in the resultset which is to be returned to the
client. A nested loop join or hash join works more efficiently in
most cases.

How long has it been since you ran DBMS_STATS to gather the table and
index stats? Can you provide a copy of your initialization
parameters? Also, post the DBMS_Xplan for the query.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.