RE: Parallel query on when it's not supposed to be (?)

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 14 Sep 2004 16:28:22 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660E47@bosmail00.bos.il.pqe>

Janine, yes, for that table, do:
alter table table_name parallel (degree 1); and also set parallel_max_servers to 0, as the other Mark suggested.

Um, the table w/ degree set to default, was that table involved in the problem query you described in the original post?

-----Original Message-----
From: Janine A Sisk [mailto:janine_at_furfly.net] Sent: Tuesday, September 14, 2004 4:20 PM To: Bobak, Mark
Cc: oracle-l_at_freelists.org
Subject: Re: Parallel query on when it's not supposed to be (?)

Here you go:

SQL> select table_name from dba_tables where degree >1; select table_name from dba_tables where degree >1

ERROR at line 1:
ORA-01722: invalid number

SQL> select index_name from dba_indexes where degree >1;

no rows selected

The error turned out to be because degree is a varchar2(10) in=20 dba_tables, so I did this instead:

SQL> select distinct degree from dba_tables;

DEGREE

1
DEFAULT Interesting... I think the DEFAULT might be the problem, because the=20 tables that have this set are very closely related to this query. =20 Should I change them to 1? Actually, neither of those values is=20 exactly as it appears:

SQL> select distinct '|' || degree || '|' from dba_tables;

'|'||DEGREE||'|'

| 1|
| DEFAULT| So I guess I would change it to 3 spaces + 1. Sounds like a magic=20 incantation or something!

thanks,

janine

On Sep 14, 2004, at 4:10 PM, Bobak, Mark wrote:

> Janine,

> Try this:

> select table_name from dba_tables where degree >1;
> select index_name from dba_indexes where degree >1;

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Janine A Sisk
> Sent: Tuesday, September 14, 2004 3:58 PM
> To: oracle-l_at_freelists.org
> Subject: Parallel query on when it's not supposed to be (?)

>
>

> Hi all,

> I have a mystery on my hands. There must be a good explanation, but=20
> so=3D20
> far it has eluded me; I'm hoping someone out there will know the=3D20
> answer.

> Configuration: Oracle 8.1.7.4 64-bit on Solaris 9. System is a=3D20
> SunFire V440.

>
> As far as I can tell, this system does not have the parallel =
query=3D20
> facility turned on:

> SQL> show parameters para

>


> NAME                                 TYPE    VALUE

> ------------------------------------ -------=3D20

> ------------------------------

> fast_start_parallel_rollback         string  FALSE

> optimizer_percent_parallel           integer 0

> parallel_adaptive_multi_user         boolean FALSE

> parallel_automatic_tuning            boolean FALSE

> parallel_broadcast_enabled           boolean FALSE

> parallel_execution_message_size      integer 2152

> parallel_instance_group              string

> parallel_max_servers                 integer 5

> parallel_min_percent                 integer 0

> parallel_min_servers                 integer 0

> parallel_server                      boolean FALSE

>


> NAME                                 TYPE    VALUE

> ------------------------------------ -------=3D20

> ------------------------------

> parallel_server_instances            integer 1

> parallel_threads_per_cpu             integer 2

> recovery_parallelism                 integer 2

>

> Everything is off, right? Even parallel rollback has been disabled.

> I have a very simple query:

> select count(dotlrn_member_rels_approved.rel_id)
> from dotlrn_member_rels_approved
> where dotlrn_member_rels_approved.community_id =3D3D 1767463;
>
> dotlrn_member_rels_approved is a view, which is a subset of =
another=3D20
> view, which is a simple join. Nothing obviously tricky there.

> This query, when profiled via autotrace, produces the following=3D20
> execution plan:

> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D64 =
Card=3D3D1 =3D
> Bytes=3D3D31)
> 1 0 SORT (AGGREGATE)
> 2 1 SORT* (AGGREGATE) =

> =3D20
> :Q428002
> 3 2 NESTED LOOPS* (Cost=3D3D64 Card=3D3D65 =
Bytes=3D3D2015) =20
> =3D
> =3D20
> :Q428002
> 4 3 HASH JOIN* (Cost=3D3D64 Card=3D3D65 Bytes=3D3D1690) =

=20
> =3D
> =3D20
> :Q428002
> 5 4 TABLE ACCESS* (BY INDEX ROWID) OF 'ACS_RELS'=20
> (Cost=3D20
> :Q428000
> =3D3D17 Card=3D3D65 Bytes=3D3D780)
>
> 6 5 INDEX (RANGE SCAN) OF =
'ACS_RELS_OBJECT_ID_ONE_ID
> X' (NON-UNIQUE) (Cost=3D3D1 Card=3D3D65)

>


>     7    4           TABLE ACCESS* (FULL) OF 'MEMBERSHIP_RELS' =3D

> (Cost=3D3D46=3D20
> :Q428001
> Card=3D3D34577 Bytes=3D3D484078)

>


>     8    3         INDEX* (UNIQUE SCAN) OF=20

> 'DOTLRN_MEMBER_RELS_REL_ID_P=3D20
> :Q428002
> K' (UNIQUE)

>
>
>


>     2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */=3D20

> SYS_OP_MSR(COUNT(A1.C0

>                                     )) FROM (SELECT /*+ ORDERED=20

> NO_EXPAN

>


>     3 PARALLEL_COMBINED_WITH_PARENT

>     4 PARALLEL_COMBINED_WITH_PARENT

>     5 PARALLEL_FROM_SERIAL

>     7 PARALLEL_TO_PARALLEL          SELECT /*+ Q428001 NO_EXPAND=3D20

> ROWID(A1) */ A1

>                                     ."REL_ID" C0,A1."MEMBER_STATE" C1=20

> FR

>


>     8 PARALLEL_COMBINED_WITH_PARENT

>

> Huh? How is this possible?

> Furthermore, when I do a 10046 event trace and look at the tkprof=3D20
> report, I don't see anything about parallel:

>


> Rows     Row Source Operation

> -------  ---------------------------------------------------

>        1  SORT AGGREGATE

>        0   SORT AGGREGATE

>        0    NESTED LOOPS

>        0     HASH JOIN

>        0      TABLE ACCESS BY INDEX ROWID ACS_RELS

>      109       INDEX RANGE SCAN (object id 26428)

>        0      TABLE ACCESS FULL MEMBERSHIP_RELS

>        0     INDEX UNIQUE SCAN (object id 26694)

>

> I have even tried doing an "explain plan for" and then running=3D20
> utlxplp.sql to look at the plan, but the parallel part is not there.
>
> Even more perplexing, it does look like parallel query is turned =
on,=3D20
> despite the parameter settings:

> SQL> select * from v_$px_process;

>


> SERV STATUS           PID SPID             SID    SERIAL#

> ---- --------- ---------- --------- ---------- ----------

> P000 AVAILABLE         24 5175

> P001 AVAILABLE         26 5177

> P002 AVAILABLE         27 5179

> P003 AVAILABLE         28 5181

> P004 AVAILABLE         29 5183

>

> I first started looking at this yesterday and at that time, this=20
> select=3D20
> returned no rows. But now it does. I assume that executing the=20
> query=3D20
> caused the parallel facility to "wake up" since it was needed, but=20
> I'm=3D20
> only guessing. This is the development server and it's fairly=20
> lightly=3D20
> used (compared to the production server), so it's not implausible=20
> that=3D20
> the query doesn't get executed very often.
>
> One last detail: fast_start_parallel_rollback was originally set =
to=3D20
> LOW, which I believe is the default. We set it to FALSE via an=20
> "alter=3D20
> system" command but it doesn't seem to have changed anything. This=20
> may=3D20
> not be important but I mention it for the sake of completeness.

> BTW, the reason I care about this is that I'm trying to tune the=3D20
> production server and a fair number of waits associated with=20
> parallel=3D20
> query are showing up in the statspack report. Since parallel query=20
> is=3D20
> not supposed to be turned on there either, I started looking into =
it=3D20
> and found that both systems are exhibiting this bizarre (to me,=20
> anyway)=3D20
> behavior.

> Can anyone a) explain what the heck is going on here and b) tell me=20
> how=3D20
> to drive a stake through the heart of parallel query on this system?

> thanks,

> janine

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

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

Received on Tue Sep 14 2004 - 15:24:06 CDT