From oracle-l-bounce@freelists.org Tue Sep 14 14:53:55 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8EJrta08522 for ; Tue, 14 Sep 2004 14:53:55 -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 i8EJrtI08517 for ; Tue, 14 Sep 2004 14:53:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 28A5C72CFF1; Tue, 14 Sep 2004 14:59:53 -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 18460-14; Tue, 14 Sep 2004 14:59:53 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7DAC372CFD2; Tue, 14 Sep 2004 14:59:52 -0500 (EST) Mime-Version: 1.0 (Apple Message framework v619) Content-Transfer-Encoding: 8bit Message-Id: <6EFB723E-0688-11D9-B6F5-000393AED29A@furfly.net> Content-type: text/plain; charset=US-ASCII To: oracle-l@freelists.org From: Janine A Sisk Subject: Parallel query on when it's not supposed to be (?) Date: Tue, 14 Sep 2004 15:58:23 -0400 X-archive-position: 9476 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: janine@furfly.net Precedence: normal Reply-To: janine@furfly.net X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi all, I have a mystery on my hands. There must be a good explanation, but so far it has eluded me; I'm hoping someone out there will know the answer. Configuration: Oracle 8.1.7.4 64-bit on Solaris 9. System is a SunFire V440. As far as I can tell, this system does not have the parallel query facility turned on: SQL> show parameters para NAME TYPE VALUE ------------------------------------ ------- ------------------------------ 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 ------------------------------------ ------- ------------------------------ 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 = 1767463; dotlrn_member_rels_approved is a view, which is a subset of another view, which is a simple join. Nothing obviously tricky there. This query, when profiled via autotrace, produces the following execution plan: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1 Bytes=31) 1 0 SORT (AGGREGATE) 2 1 SORT* (AGGREGATE) :Q428002 3 2 NESTED LOOPS* (Cost=64 Card=65 Bytes=2015) :Q428002 4 3 HASH JOIN* (Cost=64 Card=65 Bytes=1690) :Q428002 5 4 TABLE ACCESS* (BY INDEX ROWID) OF 'ACS_RELS' (Cost :Q428000 =17 Card=65 Bytes=780) 6 5 INDEX (RANGE SCAN) OF 'ACS_RELS_OBJECT_ID_ONE_ID X' (NON-UNIQUE) (Cost=1 Card=65) 7 4 TABLE ACCESS* (FULL) OF 'MEMBERSHIP_RELS' (Cost=46 :Q428001 Card=34577 Bytes=484078) 8 3 INDEX* (UNIQUE SCAN) OF 'DOTLRN_MEMBER_RELS_REL_ID_P :Q428002 K' (UNIQUE) 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C0 )) FROM (SELECT /*+ ORDERED NO_EXPAN 3 PARALLEL_COMBINED_WITH_PARENT 4 PARALLEL_COMBINED_WITH_PARENT 5 PARALLEL_FROM_SERIAL 7 PARALLEL_TO_PARALLEL SELECT /*+ Q428001 NO_EXPAND ROWID(A1) */ A1 ."REL_ID" C0,A1."MEMBER_STATE" C1 FR 8 PARALLEL_COMBINED_WITH_PARENT Huh? How is this possible? Furthermore, when I do a 10046 event trace and look at the tkprof 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 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, 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 select returned no rows. But now it does. I assume that executing the query caused the parallel facility to "wake up" since it was needed, but I'm only guessing. This is the development server and it's fairly lightly used (compared to the production server), so it's not implausible that the query doesn't get executed very often. One last detail: fast_start_parallel_rollback was originally set to LOW, which I believe is the default. We set it to FALSE via an "alter system" command but it doesn't seem to have changed anything. This may 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 production server and a fair number of waits associated with parallel query are showing up in the statspack report. Since parallel query is not supposed to be turned on there either, I started looking into it and found that both systems are exhibiting this bizarre (to me, anyway) behavior. Can anyone a) explain what the heck is going on here and b) tell me how to drive a stake through the heart of parallel query on this system? thanks, janine -- http://www.freelists.org/webpage/oracle-l