From oracle-l-bounce@freelists.org Tue Sep 14 15:24:06 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8EKO6713926 for ; Tue, 14 Sep 2004 15:24:06 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8EKO6I13921 for ; Tue, 14 Sep 2004 15:24:06 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9DE3672CA69; Tue, 14 Sep 2004 15:29:55 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24022-04; Tue, 14 Sep 2004 15:29:55 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C193A72D052; Tue, 14 Sep 2004 15:29:54 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Subject: RE: Parallel query on when it's not supposed to be (?) Date: Tue, 14 Sep 2004 16:28:22 -0400 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660E47@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Parallel query on when it's not supposed to be (?) Thread-Index: AcSamDl2CXJluBTnTn6jS5ucbJShqAAAOXog From: "Bobak, Mark" To: "Janine A Sisk" Cc: X-OriginalArrivalTime: 14 Sep 2004 20:28:23.0151 (UTC) FILETIME=[619C17F0:01C49A99] X-archive-position: 9481 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: Mark.Bobak@il.proquest.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@furfly.net] Sent: Tuesday, September 14, 2004 4:20 PM To: Bobak, Mark Cc: oracle-l@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@freelists.org > [mailto:oracle-l-bounce@freelists.org]On Behalf Of Janine A Sisk > Sent: Tuesday, September 14, 2004 3:58 PM > To: oracle-l@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