Re: is parallel cause data lost?another Oracle Bug?

From: havel.zhang <havel.zhang_at_gmail.com>
Date: Tue, 1 Sep 2009 04:02:44 -0700 (PDT)
Message-ID: <931a4863-b39a-4207-b0c0-74e5450a43ce_at_p15g2000vbl.googlegroups.com>

On Sep 1, 4:43 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Sep 1, 11:18 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
>
>
>
> > hi all:
> >        I'm using Oracle 10g 2.0.3. on Windows Server 2003 64bit
> > Service Pack2. I found some strange thing.
> >        When I have set parameter:parallel_execution_message_size =
> > 65535 and alter some table parallel degree to 8 or 4. If the table
> > have huge data volumn, when I join this table with another table like
> > follow:
> > alter table table_a parallel 8;
> > alter table table_b parallel 8;
>
> > -----------------------
> >     insert into table_c
> >     select a.*,b.column_b
> >     from table_a inner join table_b on a.column_b = b.column b
> > -----------------------
> >  it's a normal insert query. The strange thing is: When I  just
> > query :
> > ----
> >     select a.*,b.column_b
> >     from table_a inner join table_b on a.column_b = b.column b
> > ---
> >  it's no problem. but when I do insert:
> > --
> >     insert into table_c
> >     select a.*,b.column_b
> >     from table_a inner join table_b on a.column_b = b.column b
> > -----
> > it will lost data! some data will missing when insert into table_c!
>
> > but, when i set all these table back to no parallel:
> > alter table table_a noparallel;
> > alter table table_b noparallel;
>
> > do the same query, we'll get right data. Why? any one have answer?
> > another Oracle Bug?
>
> Try this with 10.2.0.4 - might indeed be a PQ defect fixed in third
> patchset. If you can reliably reproduce this issue on 10.2.0.4, you'd
> need to open a SR to get it fixed eventually. Since you know there is
> a workaround, they might not treat it as a P1/P2 issue and the fix may
> take, well, time to arrive.
>
> Regards,
>    Vladimir M. Zakharychev
>    N-Networks, makers of Dynamic PSP(tm)
>    http://www.dynamicpsp.com

thank you, I will try that on 10.2.0.4.

And I found another method to solve this problem. Don't set table parallel property, just using parallel hint!. just as follows:

---

alter table table_a noparallel;
alter table table_b noparallel;

insert into table_c
select /*+parallel(a,12)*/ /*+parallel(b,12)*/ a.*
from
table_a a inner join table_b b on a.col_a inner join b.col_a;

---

it's will get right result.

Thank you again:) Received on Tue Sep 01 2009 - 06:02:44 CDT