Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

aggregate with parallel worker process

7 posts in General Discussion Last posting was on 2009-09-15 12:26:00.0Z
Kaboum Posted on 2009-09-10 19:44:35.0Z
From: Kaboum <yvesmiron@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: aggregate with parallel worker process
Date: Thu, 10 Sep 2009 12:44:35 -0700 (PDT)
Organization: http://groups.google.com
Lines: 250
Message-ID: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com>
NNTP-Posting-Host: 64.86.141.133
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1252611875 308 127.0.0.1 (10 Sep 2009 19:44:35 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 10 Sep 2009 19:44:35 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 38g2000yqr.googlegroups.com; posting-host=64.86.141.133; posting-account=wFnfIwoAAAA77vcHGM_rC3YQpeOEmq8U
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; MS-RTC LM 8),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!38g2000yqr.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28297
Article PK: 77541

Hi,

Version : ASE 12.5.4
OS: Sun Solaris

I have a select from a customer which behave strangely. When we
enable (configure) the parallel processing as follows:
sp_configure 'number of worker processes', 50
go
sp_configure 'max parallel degree', 8
go
sp_configure 'max scan parallel degree', 3
go
sp_configure 'Parallel Query'
go


The select:

select count(*)
from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
where T1.M_TYPE='RPL_D'
and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
and T2.M_TYPE ='RPL_M'
go

The plan:

QUERY PLAN FOR STATEMENT 1 (at line 2).
Executed in parallel by coordinating process and 6 worker processes.


STEP 1
The type of query is INSERT.
The update mode is direct.
Executed in parallel by coordinating process and 3 worker
processes.
Worktable1 created for REFORMATTING.

FROM TABLE
MKT_OP_DBF
T2
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 3-way hash scan.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 2
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.
Executed in parallel by coordinating process and 6 worker
processes.

FROM TABLE
MKT_OP_DBF
T1
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 3-way hash scan.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
MKT_OP_DBF
T0
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 2-way hash scan.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
Worktable1.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

Parallel result buffer merge.

STEP 3
The type of query is SELECT.
Executed by coordinating process.

The sort for Worktable1 is done in Serial
----------------------------------------------------------------------------------

The request take about 5 hours to run.

If we disable the parallel processing, we get the results after 2
minutes.



Now I rewrote the select and it take about 2 minutes with or whithout
the parallel enable:

The Select:

select count(*)
from MKT_OP_DBF z
where z.M_TYPE = 'RPL_D'
and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
from MKT_OP_DBF b
where b.M_TYPE = 'RPL_M'
and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
from MKT_OP_DBF c
where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
go

The plan:

QUERY PLAN FOR STATEMENT 1 (at line 2).
Executed in parallel by coordinating process and 3 worker processes.


STEP 1
The type of query is INSERT.
The update mode is direct.
Executed in parallel by coordinating process and 3 worker
processes.
Worktable1 created, in allpages locking mode, for DISTINCT.

FROM TABLE
MKT_OP_DBF
z
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 3-way hash scan.

Run subquery 1 (at nesting level 1).

Run subquery 2 (at nesting level 1).
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.

STEP 2
The type of query is SELECT.
Executed by coordinating process.
This step involves sorting.

FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
Executed by 3 worker processes.

FROM TABLE
MKT_OP_DBF
b
Nested iteration.
Index : MKT_OP_ND7
Forward scan.
Positioning by key.
Keys are:
M_ORIGIN_NB ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.


QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 9).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
Executed by 3 worker processes.

FROM TABLE
MKT_OP_DBF
c
Nested iteration.
Index : MKT_OP_ND7
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be
read.
Keys are:
M_ORIGIN_NB ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.

END OF QUERY PLAN FOR SUBQUERY 2.


The sort for Worktable1 is done in Serial

The table has about 1 650 000 million rows. I see a difference with
the number of worker process
Executed in parallel by coordinating process and 6 worker processes.
(first select that takes 5 hours)

Executed in parallel by coordinating process and 3 worker processes.
(second select that takes 2 minutes)

The question: Is there a logical reason for that degradation in
performance ?
PS: If i take out the count(*) in the first select and replace it with
a select field_name as follow:
select distinct T1.M_ORIGIN_NB
from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
where T1.M_TYPE='RPL_D'
and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
and T2.M_TYPE ='RPL_M'
We get the results set in 2 minutes !

Anyone ?
Thank you


Sherlock, Kevin [TeamSybase] Posted on 2009-09-10 20:31:51.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com>
Subject: Re: aggregate with parallel worker process
Lines: 257
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa96237@forums-1-dub>
Date: 10 Sep 2009 13:31:51 -0700
X-Trace: forums-1-dub 1252614711 10.22.241.152 (10 Sep 2009 13:31:51 -0700)
X-Original-Trace: 10 Sep 2009 13:31:51 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28298
Article PK: 77542

post the DDL for each table.
Reformating usually has something to do with datatype mismatches.

"Kaboum" <yvesmiron@gmail.com> wrote in message
news:ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com...
> Hi,
>
> Version : ASE 12.5.4
> OS: Sun Solaris
>
> I have a select from a customer which behave strangely. When we
> enable (configure) the parallel processing as follows:
> sp_configure 'number of worker processes', 50
> go
> sp_configure 'max parallel degree', 8
> go
> sp_configure 'max scan parallel degree', 3
> go
> sp_configure 'Parallel Query'
> go
>
>
> The select:
>
> select count(*)
> from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> where T1.M_TYPE='RPL_D'
> and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> and T2.M_TYPE ='RPL_M'
> go
>
> The plan:
>
> QUERY PLAN FOR STATEMENT 1 (at line 2).
> Executed in parallel by coordinating process and 6 worker processes.
>
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Executed in parallel by coordinating process and 3 worker
> processes.
> Worktable1 created for REFORMATTING.
>
> FROM TABLE
> MKT_OP_DBF
> T2
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 3-way hash scan.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
> TO TABLE
> Worktable1.
>
> STEP 2
> The type of query is SELECT.
> Evaluate Ungrouped COUNT AGGREGATE.
> Executed in parallel by coordinating process and 6 worker
> processes.
>
> FROM TABLE
> MKT_OP_DBF
> T1
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 3-way hash scan.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> MKT_OP_DBF
> T0
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 2-way hash scan.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> Worktable1.
> Nested iteration.
> Using Clustered Index.
> Forward scan.
> Positioning by key.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> Parallel result buffer merge.
>
> STEP 3
> The type of query is SELECT.
> Executed by coordinating process.
>
> The sort for Worktable1 is done in Serial
> ----------------------------------------------------------------------------------
>
> The request take about 5 hours to run.
>
> If we disable the parallel processing, we get the results after 2
> minutes.
>
>
>
> Now I rewrote the select and it take about 2 minutes with or whithout
> the parallel enable:
>
> The Select:
>
> select count(*)
> from MKT_OP_DBF z
> where z.M_TYPE = 'RPL_D'
> and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
> from MKT_OP_DBF b
> where b.M_TYPE = 'RPL_M'
> and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
> and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
> from MKT_OP_DBF c
> where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
> go
>
> The plan:
>
> QUERY PLAN FOR STATEMENT 1 (at line 2).
> Executed in parallel by coordinating process and 3 worker processes.
>
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Executed in parallel by coordinating process and 3 worker
> processes.
> Worktable1 created, in allpages locking mode, for DISTINCT.
>
> FROM TABLE
> MKT_OP_DBF
> z
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 3-way hash scan.
>
> Run subquery 1 (at nesting level 1).
>
> Run subquery 2 (at nesting level 1).
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
> TO TABLE
> Worktable1.
>
> STEP 2
> The type of query is SELECT.
> Executed by coordinating process.
> This step involves sorting.
>
> FROM TABLE
> Worktable1.
> Using GETSORTED
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 4 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data pages.
> STEP 1
>
> NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
>
> QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).
>
> Correlated Subquery.
> Subquery under an EXPRESSION predicate.
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> Executed by 3 worker processes.
>
> FROM TABLE
> MKT_OP_DBF
> b
> Nested iteration.
> Index : MKT_OP_ND7
> Forward scan.
> Positioning by key.
> Keys are:
> M_ORIGIN_NB ASC
> Using I/O Size 4 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> END OF QUERY PLAN FOR SUBQUERY 1.
>
>
> QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 9).
>
> Correlated Subquery.
> Subquery under an EXPRESSION predicate.
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> Executed by 3 worker processes.
>
> FROM TABLE
> MKT_OP_DBF
> c
> Nested iteration.
> Index : MKT_OP_ND7
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> M_ORIGIN_NB ASC
> Using I/O Size 4 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> END OF QUERY PLAN FOR SUBQUERY 2.
>
>
> The sort for Worktable1 is done in Serial
>
> The table has about 1 650 000 million rows. I see a difference with
> the number of worker process
> Executed in parallel by coordinating process and 6 worker processes.
> (first select that takes 5 hours)
>
> Executed in parallel by coordinating process and 3 worker processes.
> (second select that takes 2 minutes)
>
> The question: Is there a logical reason for that degradation in
> performance ?
> PS: If i take out the count(*) in the first select and replace it with
> a select field_name as follow:
> select distinct T1.M_ORIGIN_NB
> from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> where T1.M_TYPE='RPL_D'
> and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> and T2.M_TYPE ='RPL_M'
> We get the results set in 2 minutes !
>
> Anyone ?
> Thank you


Kaboum Posted on 2009-09-11 13:48:01.0Z
From: Kaboum <yvesmiron@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: aggregate with parallel worker process
Date: Fri, 11 Sep 2009 06:48:01 -0700 (PDT)
Organization: http://groups.google.com
Lines: 268
Message-ID: <af2d5bcf-f141-4a52-845e-0fc5599ed4c9@j19g2000yqk.googlegroups.com>
References: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com> <4aa96237@forums-1-dub>
NNTP-Posting-Host: 64.86.141.133
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1252676881 4614 127.0.0.1 (11 Sep 2009 13:48:01 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 11 Sep 2009 13:48:01 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: j19g2000yqk.googlegroups.com; posting-host=64.86.141.133; posting-account=wFnfIwoAAAA77vcHGM_rC3YQpeOEmq8U
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; MS-RTC LM 8),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!j19g2000yqk.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28299
Article PK: 77543

On Sep 10, 4:31 pm, "Sherlock, Kevin [TeamSybase]"

<kevin.sherl...@teamsybase.com> wrote:
> post the DDL for each table.
>  Reformating usually has something to do with datatype mismatches.
>
> "Kaboum" <yvesmi...@gmail.com> wrote in message
>
> news:ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com...
>
>
>
> > Hi,
>
> > Version : ASE 12.5.4
> > OS: Sun Solaris
>
> > I have a select from a customer which behave strangely.  When we
> > enable (configure) the parallel processing as follows:
> > sp_configure 'number of worker processes', 50
> > go
> > sp_configure 'max parallel degree', 8
> > go
> > sp_configure 'max scan parallel degree', 3
> > go
> > sp_configure 'Parallel Query'
> > go
>
> > The select:
>
> > select count(*)
> > from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> > where T1.M_TYPE='RPL_D'
> > and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> > and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> > and T2.M_TYPE ='RPL_M'
> > go
>
> > The plan:
>
> > QUERY PLAN FOR STATEMENT 1 (at line 2).
> > Executed in parallel by coordinating process and 6 worker processes.
>
> >    STEP 1
> >        The type of query is INSERT.
> >        The update mode is direct.
> >        Executed in parallel by coordinating process and 3 worker
> > processes.
> >        Worktable1 created for REFORMATTING.
>
> >        FROM TABLE
> >            MKT_OP_DBF
> >            T2
> >        Nested iteration.
> >        Table Scan.
> >        Forward scan.
> >        Positioning at start of table.
> >        Executed in parallel with a 3-way hash scan.
> >        Using I/O Size 4 Kbytes for data pages.
> >        With LRU Buffer Replacement Strategy for data pages.
> >        TO TABLE
> >            Worktable1.
>
> >    STEP 2
> >        The type of query is SELECT.
> >        Evaluate Ungrouped COUNT AGGREGATE.
> >        Executed in parallel by coordinating process and 6 worker
> > processes.
>
> >        FROM TABLE
> >            MKT_OP_DBF
> >            T1
> >        Nested iteration.
> >        Table Scan.
> >        Forward scan.
> >        Positioning at start of table.
> >        Executed in parallel with a 3-way hash scan.
> >        Using I/O Size 4 Kbytes for data pages.
> >        With LRU Buffer Replacement Strategy for data pages.
>
> >        FROM TABLE
> >            MKT_OP_DBF
> >            T0
> >        Nested iteration.
> >        Table Scan.
> >        Forward scan.
> >        Positioning at start of table.
> >        Executed in parallel with a 2-way hash scan.
> >        Using I/O Size 4 Kbytes for data pages.
> >        With LRU Buffer Replacement Strategy for data pages.
>
> >        FROM TABLE
> >            Worktable1.
> >        Nested iteration.
> >        Using Clustered Index.
> >        Forward scan.
> >        Positioning by key.
> >        Using I/O Size 4 Kbytes for data pages.
> >        With LRU Buffer Replacement Strategy for data pages.
>
> >        Parallel result buffer merge.
>
> >    STEP 3
> >        The type of query is SELECT.
> >        Executed by coordinating process.
>
> > The sort for Worktable1 is done in Serial
> > ---------------------------------------------------------------------------­-------
>
> > The request take about 5 hours to run.
>
> > If we disable the parallel processing, we get the results after 2
> > minutes.
>
> > Now I rewrote the select and it take about 2 minutes with or whithout
> > the parallel enable:
>
> > The Select:
>
> > select  count(*)
> > from  MKT_OP_DBF z
> > where z.M_TYPE = 'RPL_D'
> >  and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
> >               from MKT_OP_DBF b
> >              where b.M_TYPE = 'RPL_M'
> >                and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
> >  and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
> >               from MKT_OP_DBF c
> >              where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
> > go
>
> > The plan:
>
> > QUERY PLAN FOR STATEMENT 1 (at line 2).
> > Executed in parallel by coordinating process and 3 worker processes.
>
> >    STEP 1
> >        The type of query is INSERT.
> >        The update mode is direct.
> >        Executed in parallel by coordinating process and 3 worker
> > processes.
> >        Worktable1 created, in allpages locking mode, for DISTINCT.
>
> >        FROM TABLE
> >            MKT_OP_DBF
> >            z
> >        Nested iteration.
> >        Table Scan.
> >        Forward scan.
> >        Positioning at start of table.
> >        Executed in parallel with a 3-way hash scan.
>
> >        Run subquery 1 (at nesting level 1).
>
> >        Run subquery 2 (at nesting level 1).
> >        Using I/O Size 4 Kbytes for data pages.
> >        With LRU Buffer Replacement Strategy for data pages.
> >        TO TABLE
> >            Worktable1.
>
> >    STEP 2
> >        The type of query is SELECT.
> >        Executed by coordinating process.
> >        This step involves sorting.
>
> >        FROM TABLE
> >            Worktable1.
> >        Using GETSORTED
> >        Table Scan.
> >        Forward scan.
> >        Positioning at start of table.
> >        Using I/O Size 4 Kbytes for data pages.
> >        With MRU Buffer Replacement Strategy for data pages.
> >    STEP 1
>
> > NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
>
> >  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).
>
> >    Correlated Subquery.
> >    Subquery under an EXPRESSION predicate.
>
> >    STEP 1
> >        The type of query is SELECT.
> >        Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> >        Executed by 3 worker processes.
>
> >        FROM TABLE
> >            MKT_OP_DBF
> >            b
> >        Nested iteration.
> >        Index : MKT_OP_ND7
> >        Forward scan.
> >        Positioning by key.
> >        Keys are:
> >            M_ORIGIN_NB  ASC
> >        Using I/O Size 4 Kbytes for index leaf pages.
> >        With LRU Buffer Replacement Strategy for index leaf pages.
> >        Using I/O Size 4 Kbytes for data pages.
> >        With LRU Buffer Replacement Strategy for data pages.
>
> >  END OF QUERY PLAN FOR SUBQUERY 1.
>
> >  QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 9).
>
> >    Correlated Subquery.
> >    Subquery under an EXPRESSION predicate.
>
> >    STEP 1
> >        The type of query is SELECT.
> >        Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> >        Executed by 3 worker processes.
>
> >        FROM TABLE
> >            MKT_OP_DBF
> >            c
> >        Nested iteration.
> >        Index : MKT_OP_ND7
> >        Forward scan.
> >        Positioning by key.
> >        Index contains all needed columns. Base table will not be
> > read.
> >        Keys are:
> >            M_ORIGIN_NB  ASC
> >        Using I/O Size 4 Kbytes for index leaf pages.
> >        With LRU Buffer Replacement Strategy for index leaf pages.
>
> >  END OF QUERY PLAN FOR SUBQUERY 2.
>
> > The sort for Worktable1 is done in Serial
>
> > The table has about 1 650 000 million rows. I see a difference with
> > the number of worker process
> > Executed in parallel by coordinating process and 6 worker processes.
> > (first select that takes 5 hours)
>
> > Executed in parallel by coordinating process and 3 worker processes.
> > (second select that takes 2 minutes)
>
> > The question: Is there a logical reason for that degradation in
> > performance ?
> > PS: If i take out the count(*) in the first select and replace it with
> > a select field_name as follow:
> >            select distinct T1.M_ORIGIN_NB
> >              from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> >           where T1.M_TYPE='RPL_D'
> >               and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> >               and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> >               and T2.M_TYPE ='RPL_M'
> >      We get the results set in 2 minutes !
>
> > Anyone ?
> > Thank you- Hide quoted text -
>
> - Show quoted text -

Kevin, the from is on the same table (3 times)
from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
There should'nt be any reformatting...


Sherlock, Kevin [TeamSybase] Posted on 2009-09-11 18:38:23.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com> <4aa96237@forums-1-dub> <af2d5bcf-f141-4a52-845e-0fc5599ed4c9@j19g2000yqk.googlegroups.com>
Subject: Re: aggregate with parallel worker process
Lines: 12
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aaa991f@forums-1-dub>
Date: 11 Sep 2009 11:38:23 -0700
X-Trace: forums-1-dub 1252694303 10.22.241.152 (11 Sep 2009 11:38:23 -0700)
X-Original-Trace: 11 Sep 2009 11:38:23 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28300
Article PK: 77544

Yep, you're right. I didn't catch that. Sorry. I have a hard reading
all-caps I guess.

"Kaboum" <yvesmiron@gmail.com> wrote in message
news:af2d5bcf-f141-4a52-845e-0fc5599ed4c9@j19g2000yqk.googlegroups.com...
...

Kevin, the from is on the same table (3 times)
from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
There should'nt be any reformatting...


Sherlock, Kevin [TeamSybase] Posted on 2009-09-11 18:47:33.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com> <4aa96237@forums-1-dub> <af2d5bcf-f141-4a52-845e-0fc5599ed4c9@j19g2000yqk.googlegroups.com> <4aaa991f@forums-1-dub>
Subject: Re: aggregate with parallel worker process
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aaa9b45$1@forums-1-dub>
Date: 11 Sep 2009 11:47:33 -0700
X-Trace: forums-1-dub 1252694853 10.22.241.152 (11 Sep 2009 11:47:33 -0700)
X-Original-Trace: 11 Sep 2009 11:47:33 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28301
Article PK: 77545

and apparently a hard [time] typing as well.

"Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
message news:4aaa991f@forums-1-dub...
> Yep, you're right. I didn't catch that. Sorry. I have a hard reading
> all-caps I guess.
>
> "Kaboum" <yvesmiron@gmail.com> wrote in message
> news:af2d5bcf-f141-4a52-845e-0fc5599ed4c9@j19g2000yqk.googlegroups.com...
> ...
>
> Kevin, the from is on the same table (3 times)
> from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> There should'nt be any reformatting...
>
>


Jeff Tallman [Sybase] Posted on 2009-09-14 06:21:30.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: aggregate with parallel worker process
References: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com>
In-Reply-To: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aade0ea$1@forums-1-dub>
Date: 13 Sep 2009 23:21:30 -0700
X-Trace: forums-1-dub 1252909290 10.22.241.152 (13 Sep 2009 23:21:30 -0700)
X-Original-Trace: 13 Sep 2009 23:21:30 -0700, vip152.sybase.com
Lines: 329
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28302
Article PK: 77546

There is a difference in your queries.....I don't know what the join
condition results are, but if T0 -> T1 is 1:M and T1 -> T2 is 1:M, then
the count(*) is counting all the materialized rows when all three copies
of the table are joined. If M_ORIGIN_NB is the PKEY and the only column
in the PKEY, then it would still be joining all three to get the
materialized result set before doing the count(*)...

BTW - exactly what is the role of T0 in this query - is it necessary at
all???

select distinct T1.M_ORIGIN_NB
from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
where T1.M_TYPE='RPL_D'
and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
and T2.M_TYPE ='RPL_M'

Ouch - watch the implicit partial cartesians. Any distinct projection
on more than one table in which only one table is in the projection is a
partial cartesian. Best bet would be to write as:

select T1.M_ORIGIN_NB
from MKT_OP_DBF T1
where T1.M_TYPE='RPL_D'
and M_ORIGIN_NB in (select T2.M_ORIGIN_NB
from MKT_OP_NB T2, MKT_OP_NB T0
where T2.M_TYPE ='RPL_M'
and T2.M_ORIGIN_NB= T0.M_ORIGIN_NB)

...which like I said - since this is the same table, the transitive
closure join above on T0 doesn't really make any sense for T0 to
participate at all - which means the query becomes:

select T1.M_ORIGIN_NB
from MKT_OP_DBF T1
where T1.M_TYPE='RPL_D'
and M_ORIGIN_NB in (select T2.M_ORIGIN_NB
from MKT_OP_NB T2
where T2.M_TYPE ='RPL_M')

You can see including T0 just doesn't make sense when you consider the
last subquery in the one rewrite you did:

> select count(*)

> from MKT_OP_DBF z
> where z.M_TYPE = 'RPL_D'
> and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
> from MKT_OP_DBF b
> where b.M_TYPE = 'RPL_M'
> and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
> and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
> from MKT_OP_DBF c
> where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
> go

Think about it....it is like saying:

select count(*)
from pubs2..titles t1
where type='business'
and title_id = (select t2.title_id from titles t2 where
t1.title_id=t2.title_id)

....which is a bit redundant. I suspect there is something missing -
like either the join was planned T0 -> T1 -> T2 and the SARGS were meant
to be on T0 and T2 vs. T1 & T2 ...and the linking columns something else
than those listed.....

....or this was a query that was cut down from a more complex one and
not all the extraneous stuff removed.

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Kaboum wrote:
> Hi,
>
> Version : ASE 12.5.4
> OS: Sun Solaris
>
> I have a select from a customer which behave strangely. When we
> enable (configure) the parallel processing as follows:
> sp_configure 'number of worker processes', 50
> go
> sp_configure 'max parallel degree', 8
> go
> sp_configure 'max scan parallel degree', 3
> go
> sp_configure 'Parallel Query'
> go
>
>
> The select:
>
> select count(*)
> from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> where T1.M_TYPE='RPL_D'
> and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> and T2.M_TYPE ='RPL_M'
> go
>
> The plan:
>
> QUERY PLAN FOR STATEMENT 1 (at line 2).
> Executed in parallel by coordinating process and 6 worker processes.
>
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Executed in parallel by coordinating process and 3 worker
> processes.
> Worktable1 created for REFORMATTING.
>
> FROM TABLE
> MKT_OP_DBF
> T2
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 3-way hash scan.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
> TO TABLE
> Worktable1.
>
> STEP 2
> The type of query is SELECT.
> Evaluate Ungrouped COUNT AGGREGATE.
> Executed in parallel by coordinating process and 6 worker
> processes.
>
> FROM TABLE
> MKT_OP_DBF
> T1
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 3-way hash scan.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> MKT_OP_DBF
> T0
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 2-way hash scan.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> Worktable1.
> Nested iteration.
> Using Clustered Index.
> Forward scan.
> Positioning by key.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> Parallel result buffer merge.
>
> STEP 3
> The type of query is SELECT.
> Executed by coordinating process.
>
> The sort for Worktable1 is done in Serial
> ----------------------------------------------------------------------------------
>
> The request take about 5 hours to run.
>
> If we disable the parallel processing, we get the results after 2
> minutes.
>
>
>
> Now I rewrote the select and it take about 2 minutes with or whithout
> the parallel enable:
>
> The Select:
>
> select count(*)
> from MKT_OP_DBF z
> where z.M_TYPE = 'RPL_D'
> and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
> from MKT_OP_DBF b
> where b.M_TYPE = 'RPL_M'
> and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
> and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
> from MKT_OP_DBF c
> where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
> go
>
> The plan:
>
> QUERY PLAN FOR STATEMENT 1 (at line 2).
> Executed in parallel by coordinating process and 3 worker processes.
>
>
> STEP 1
> The type of query is INSERT.
> The update mode is direct.
> Executed in parallel by coordinating process and 3 worker
> processes.
> Worktable1 created, in allpages locking mode, for DISTINCT.
>
> FROM TABLE
> MKT_OP_DBF
> z
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Executed in parallel with a 3-way hash scan.
>
> Run subquery 1 (at nesting level 1).
>
> Run subquery 2 (at nesting level 1).
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
> TO TABLE
> Worktable1.
>
> STEP 2
> The type of query is SELECT.
> Executed by coordinating process.
> This step involves sorting.
>
> FROM TABLE
> Worktable1.
> Using GETSORTED
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 4 Kbytes for data pages.
> With MRU Buffer Replacement Strategy for data pages.
> STEP 1
>
> NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
>
> QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).
>
> Correlated Subquery.
> Subquery under an EXPRESSION predicate.
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> Executed by 3 worker processes.
>
> FROM TABLE
> MKT_OP_DBF
> b
> Nested iteration.
> Index : MKT_OP_ND7
> Forward scan.
> Positioning by key.
> Keys are:
> M_ORIGIN_NB ASC
> Using I/O Size 4 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
> Using I/O Size 4 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> END OF QUERY PLAN FOR SUBQUERY 1.
>
>
> QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 9).
>
> Correlated Subquery.
> Subquery under an EXPRESSION predicate.
>
>
> STEP 1
> The type of query is SELECT.
> Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> Executed by 3 worker processes.
>
> FROM TABLE
> MKT_OP_DBF
> c
> Nested iteration.
> Index : MKT_OP_ND7
> Forward scan.
> Positioning by key.
> Index contains all needed columns. Base table will not be
> read.
> Keys are:
> M_ORIGIN_NB ASC
> Using I/O Size 4 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf pages.
>
> END OF QUERY PLAN FOR SUBQUERY 2.
>
>
> The sort for Worktable1 is done in Serial
>
> The table has about 1 650 000 million rows. I see a difference with
> the number of worker process
> Executed in parallel by coordinating process and 6 worker processes.
> (first select that takes 5 hours)
>
> Executed in parallel by coordinating process and 3 worker processes.
> (second select that takes 2 minutes)
>
> The question: Is there a logical reason for that degradation in
> performance ?
> PS: If i take out the count(*) in the first select and replace it with
> a select field_name as follow:
> select distinct T1.M_ORIGIN_NB
> from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> where T1.M_TYPE='RPL_D'
> and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> and T2.M_TYPE ='RPL_M'
> We get the results set in 2 minutes !
>
> Anyone ?
> Thank you


Kaboum Posted on 2009-09-15 12:26:00.0Z
From: Kaboum <yvesmiron@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: aggregate with parallel worker process
Date: Tue, 15 Sep 2009 05:26:00 -0700 (PDT)
Organization: http://groups.google.com
Lines: 382
Message-ID: <c779b4c2-6377-4cc7-ab1f-54a309654fca@h30g2000vbr.googlegroups.com>
References: <ebfceb85-33a0-4845-995f-aa63518a0e6d@38g2000yqr.googlegroups.com> <4aade0ea$1@forums-1-dub>
NNTP-Posting-Host: 64.86.141.133
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1253017560 23817 127.0.0.1 (15 Sep 2009 12:26:00 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 15 Sep 2009 12:26:00 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: h30g2000vbr.googlegroups.com; posting-host=64.86.141.133; posting-account=wFnfIwoAAAA77vcHGM_rC3YQpeOEmq8U
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; MS-RTC LM 8),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!h30g2000vbr.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28306
Article PK: 77549

On Sep 14, 2:21 am, "Jeff Tallman [Sybase]" <jeff.tall...@sybase.com>

wrote:
> There is a difference in your queries.....I don't know what the join
> condition results are, but if T0 -> T1 is 1:M and T1 -> T2 is 1:M, then
> the count(*) is counting all the materialized rows when all three copies
> of the table are joined.  If M_ORIGIN_NB is the PKEY and the only column
> in the PKEY, then it would still be joining all three to get the
> materialized result set before doing the count(*)...
>
> BTW - exactly what is the role of T0 in this query - is it necessary at
> all???
>
> select distinct T1.M_ORIGIN_NB
>    from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
>    where T1.M_TYPE='RPL_D'
>     and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
>     and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
>     and T2.M_TYPE ='RPL_M'
>
> Ouch - watch the implicit partial cartesians.  Any distinct projection
> on more than one table in which only one table is in the projection is a
> partial cartesian.  Best bet would be to write as:
>
> select T1.M_ORIGIN_NB
>    from MKT_OP_DBF T1
>    where T1.M_TYPE='RPL_D'
>      and M_ORIGIN_NB in (select T2.M_ORIGIN_NB
>                            from MKT_OP_NB T2, MKT_OP_NB T0
>                            where T2.M_TYPE ='RPL_M'
>                              and T2.M_ORIGIN_NB= T0.M_ORIGIN_NB)
>
> ...which like I said - since this is the same table, the transitive
> closure join above on T0 doesn't really make any sense for T0 to
> participate at all - which means the query becomes:
>
> select T1.M_ORIGIN_NB
>    from MKT_OP_DBF T1
>    where T1.M_TYPE='RPL_D'
>      and M_ORIGIN_NB in (select T2.M_ORIGIN_NB
>                            from MKT_OP_NB T2
>                            where T2.M_TYPE ='RPL_M')
>
> You can see including T0 just doesn't make sense when you consider the
> last subquery in the one rewrite you did:
>
>  > select  count(*)
>  > from  MKT_OP_DBF z
>  > where z.M_TYPE = 'RPL_D'
>  >   and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
>  >                from MKT_OP_DBF b
>  >               where b.M_TYPE = 'RPL_M'
>  >                 and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
>  >   and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
>  >                from MKT_OP_DBF c
>  >               where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
>  > go
>
> Think about it....it is like saying:
>
> select count(*)
>    from pubs2..titles t1
>    where type='business'
>      and title_id = (select t2.title_id from titles t2 where
> t1.title_id=t2.title_id)
>
> ....which is a bit redundant.  I suspect there is something missing -
> like either the join was planned T0 -> T1 -> T2 and the SARGS were meant
> to be on T0 and T2 vs. T1 & T2 ...and the linking columns something else
> than those listed.....
>
> ....or this was a query that was cut down from a more complex one and
> not all the extraneous stuff removed.
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tall...@sybase.comhttp://blogs.sybase.com/database
>
>
>
> Kaboum wrote:
> > Hi,
>
> > Version : ASE 12.5.4
> > OS: Sun Solaris
>
> > I have a select from a customer which behave strangely.  When we
> > enable (configure) the parallel processing as follows:
> > sp_configure 'number of worker processes', 50
> > go
> > sp_configure 'max parallel degree', 8
> > go
> > sp_configure 'max scan parallel degree', 3
> > go
> > sp_configure 'Parallel Query'
> > go
>
> > The select:
>
> > select count(*)
> > from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> > where T1.M_TYPE='RPL_D'
> > and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> > and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> > and T2.M_TYPE ='RPL_M'
> > go
>
> > The plan:
>
> > QUERY PLAN FOR STATEMENT 1 (at line 2).
> > Executed in parallel by coordinating process and 6 worker processes.
>
> >     STEP 1
> >         The type of query is INSERT.
> >         The update mode is direct.
> >         Executed in parallel by coordinating process and 3 worker
> > processes.
> >         Worktable1 created for REFORMATTING.
>
> >         FROM TABLE
> >             MKT_OP_DBF
> >             T2
> >         Nested iteration.
> >         Table Scan.
> >         Forward scan.
> >         Positioning at start of table.
> >         Executed in parallel with a 3-way hash scan.
> >         Using I/O Size 4 Kbytes for data pages.
> >         With LRU Buffer Replacement Strategy for data pages.
> >         TO TABLE
> >             Worktable1.
>
> >     STEP 2
> >         The type of query is SELECT.
> >         Evaluate Ungrouped COUNT AGGREGATE.
> >         Executed in parallel by coordinating process and 6 worker
> > processes.
>
> >         FROM TABLE
> >             MKT_OP_DBF
> >             T1
> >         Nested iteration.
> >         Table Scan.
> >         Forward scan.
> >         Positioning at start of table.
> >         Executed in parallel with a 3-way hash scan.
> >         Using I/O Size 4 Kbytes for data pages.
> >         With LRU Buffer Replacement Strategy for data pages.
>
> >         FROM TABLE
> >             MKT_OP_DBF
> >             T0
> >         Nested iteration.
> >         Table Scan.
> >         Forward scan.
> >         Positioning at start of table.
> >         Executed in parallel with a 2-way hash scan.
> >         Using I/O Size 4 Kbytes for data pages.
> >         With LRU Buffer Replacement Strategy for data pages.
>
> >         FROM TABLE
> >             Worktable1.
> >         Nested iteration.
> >         Using Clustered Index.
> >         Forward scan.
> >         Positioning by key.
> >         Using I/O Size 4 Kbytes for data pages.
> >         With LRU Buffer Replacement Strategy for data pages.
>
> >         Parallel result buffer merge.
>
> >     STEP 3
> >         The type of query is SELECT.
> >         Executed by coordinating process.
>
> > The sort for Worktable1 is done in Serial
> > ---------------------------------------------------------------------------­-------
>
> > The request take about 5 hours to run.
>
> > If we disable the parallel processing, we get the results after 2
> > minutes.
>
> > Now I rewrote the select and it take about 2 minutes with or whithout
> > the parallel enable:
>
> > The Select:
>
> > select  count(*)
> > from  MKT_OP_DBF z
> > where z.M_TYPE = 'RPL_D'
> >   and z.M_ORIGIN_NB = (select distinct b.M_ORIGIN_NB
> >                from MKT_OP_DBF b
> >               where b.M_TYPE = 'RPL_M'
> >                 and b.M_ORIGIN_NB = z.M_ORIGIN_NB)
> >   and z.M_ORIGIN_NB = (select distinct c.M_ORIGIN_NB
> >                from MKT_OP_DBF c
> >               where c.M_ORIGIN_NB = z.M_ORIGIN_NB)
> > go
>
> > The plan:
>
> > QUERY PLAN FOR STATEMENT 1 (at line 2).
> > Executed in parallel by coordinating process and 3 worker processes.
>
> >     STEP 1
> >         The type of query is INSERT.
> >         The update mode is direct.
> >         Executed in parallel by coordinating process and 3 worker
> > processes.
> >         Worktable1 created, in allpages locking mode, for DISTINCT.
>
> >         FROM TABLE
> >             MKT_OP_DBF
> >             z
> >         Nested iteration.
> >         Table Scan.
> >         Forward scan.
> >         Positioning at start of table.
> >         Executed in parallel with a 3-way hash scan.
>
> >         Run subquery 1 (at nesting level 1).
>
> >         Run subquery 2 (at nesting level 1).
> >         Using I/O Size 4 Kbytes for data pages.
> >         With LRU Buffer Replacement Strategy for data pages.
> >         TO TABLE
> >             Worktable1.
>
> >     STEP 2
> >         The type of query is SELECT.
> >         Executed by coordinating process.
> >         This step involves sorting.
>
> >         FROM TABLE
> >             Worktable1.
> >         Using GETSORTED
> >         Table Scan.
> >         Forward scan.
> >         Positioning at start of table.
> >         Using I/O Size 4 Kbytes for data pages.
> >         With MRU Buffer Replacement Strategy for data pages.
> >     STEP 1
>
> > NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
>
> >   QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 5).
>
> >     Correlated Subquery.
> >     Subquery under an EXPRESSION predicate.
>
> >     STEP 1
> >         The type of query is SELECT.
> >         Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> >         Executed by 3 worker processes.
>
> >         FROM TABLE
> >             MKT_OP_DBF
> >             b
> >         Nested iteration.
> >         Index : MKT_OP_ND7
> >         Forward scan.
> >         Positioning by key.
> >         Keys are:
> >             M_ORIGIN_NB  ASC
> >         Using I/O Size 4 Kbytes for index leaf pages.
> >         With LRU Buffer Replacement Strategy for index leaf pages.
> >         Using I/O Size 4 Kbytes for data pages.
> >         With LRU Buffer Replacement Strategy for data pages.
>
> >   END OF QUERY PLAN FOR SUBQUERY 1.
>
> >   QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 9).
>
> >     Correlated Subquery.
> >     Subquery under an EXPRESSION predicate.
>
> >     STEP 1
> >         The type of query is SELECT.
> >         Evaluate Ungrouped ONCE-UNIQUE AGGREGATE.
> >         Executed by 3 worker processes.
>
> >         FROM TABLE
> >             MKT_OP_DBF
> >             c
> >         Nested iteration.
> >         Index : MKT_OP_ND7
> >         Forward scan.
> >         Positioning by key.
> >         Index contains all needed columns. Base table will not be
> > read.
> >         Keys are:
> >             M_ORIGIN_NB  ASC
> >         Using I/O Size 4 Kbytes for index leaf pages.
> >         With LRU Buffer Replacement Strategy for index leaf pages.
>
> >   END OF QUERY PLAN FOR SUBQUERY 2.
>
> > The sort for Worktable1 is done in Serial
>
> > The table has about 1 650 000 million rows. I see a difference with
> > the number of worker process
> > Executed in parallel by coordinating process and 6 worker processes.
> > (first select that takes 5 hours)
>
> > Executed in parallel by coordinating process and 3 worker processes.
> > (second select that takes 2 minutes)
>
> > The question: Is there a logical reason for that degradation in
> > performance ?
> > PS: If i take out the count(*) in the first select and replace it with
> > a select field_name as follow:
> >             select distinct T1.M_ORIGIN_NB
> >               from MKT_OP_DBF T0,MKT_OP_DBF T1, MKT_OP_DBF T2
> >            where T1.M_TYPE='RPL_D'
> >                and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
> >                and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB
> >                and T2.M_TYPE- Hide quoted text -
>
> - Show quoted text -...
>
> read more »

Thank you Jeff

After a few tests I realized that in the original request there is a
joint that is not necessary (in my opinion) at all and could be the
problem, which is (like you mentionned)

select count(*)
from MKT_OP_DBF T0,
MKT_OP_DBF T1,
MKT_OP_DBF T2
where T1.M_TYPE='RPL_D'
and T1.M_ORIGIN_NB= T2.M_ORIGIN_NB
and T1.M_ORIGIN_NB= T0.M_ORIGIN_NB **************************
and T2.M_TYPE ='RPL_M'

I tried without the joint in parallel and the results are good and it
took only a few seconds. I did the same with the query which as a
subquery in it, and the results are Identital, except for the plan
which is a little more work with the subquery is seems.

select count(*)
from MKT_OP_DBF
where M_TYPE = 'RPL_D'
and M_ORIGIN_NB in (select b.M_ORIGIN_NB
from MKT_OP_DBF b
where b.M_TYPE = 'RPL_M'
and b.M_ORIGIN_NB = M_ORIGIN_NB)



Looks like that it is a bug when we use a self joint with no
restriction. So what i'm wondering is why the process start running
(for 5 hours on PROD and 15 hours on DEV) and then the query is stop
by Sybase with no messages of any kind, no results returned on the
session and no errors messages in the errorlog, nothing no messages at
all ???

I can understand that the query is bad with that joint, probably a cut
and paste from a bigger query (i did ask the customer and waiting for
a answer) But I’m surprise that Sybase stops a query without any
messages after a long time running !

I did open a case at Sybase and waiting for the results.
Again thank you all for the info and the time you took for answering,
much apprciated.