Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 067161960FCF for ; Thu, 6 Jun 2013 17:58:23 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Thu, 6 Jun 2013 17:58:22 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 98E3822C70; Thu, 6 Jun 2013 11:45:11 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id iYhtrcYee2i3; Thu, 6 Jun 2013 11:45:11 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7D6B322B87; Thu, 6 Jun 2013 11:44:30 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 06 Jun 2013 11:43:48 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D408B22A57 for ; Thu, 6 Jun 2013 11:43:48 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id uhGJ1oovH0pU for ; Thu, 6 Jun 2013 11:43:48 -0400 (EDT) Received: from pps.mail.medcity.net (hcalegaldev20.mail.medcity.net [199.91.33.184]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 858A222805 for ; Thu, 6 Jun 2013 11:43:46 -0400 (EDT) Received: from pps.filterd (NADCLZMSGPMG01D [127.0.0.1]) by NADCLZMSGPMG01D.medcity.net (8.14.4/8.14.4) with SMTP id r56FrrxD001358; Thu, 6 Jun 2013 10:56:54 -0500 Received: from pps.mail.medcity.net ([10.26.43.100]) by NADCLZMSGPMG01D.medcity.net with ESMTP id 1ctvwxgv0c-1 (version=TLSv1/SSLv3 cipher=AES256-SHA bits=256 verify=NOT); Thu, 06 Jun 2013 10:56:54 -0500 Received: from pps.filterd (NADCLPMSGPMG02A [127.0.0.1]) by NADCLPMSGPMG02A.hca.corpad.net (8.14.4/8.14.4) with SMTP id r56FtIaS010718; Thu, 6 Jun 2013 10:55:48 -0500 Received: from nadcwpmsght02.hca.corpad.net (nrdc-f5-frontside.mgmt.medcity.net [10.26.109.5]) by NADCLPMSGPMG02A.hca.corpad.net with ESMTP id 1ctw42r2n0-1 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NOT); Thu, 06 Jun 2013 10:55:48 -0500 Received: from NADCWPMSGCMS10.hca.corpad.net ([10.26.103.29]) by nadcwpmsght02.hca.corpad.net ([10.26.103.52]) with mapi; Thu, 6 Jun 2013 10:55:47 -0500 From: To: CC: , Date: Thu, 6 Jun 2013 10:55:46 -0500 Subject: RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution Thread-Topic: Partitioned Explain Plan - Pstart/Pstop = KEY after execution Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-type: text/plain MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-archive-position: 49207 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Christopher.Taylor2@parallon.net Precedence: normal Reply-To: Christopher.Taylor2@parallon.net List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l User error - they sent me the wrong query without a required predicate. Adding the additional filter does show the partition pruning after execution - doh! Chris From: Don Seiler [mailto:don@seiler.us] Sent: Thursday, June 06, 2013 10:43 AM To: Taylor Christopher - Nashville Cc: ORACLE-L; mohamed.houri@gmail.com Subject: RE: Partitioned Explain Plan - Pstart/Pstop = KEY after execution Are you using bind variables on the partition keys? I normally see the key: key values when that happens. You don't want to use binds on partition key fields. Don. On Jun 6, 2013 10:33 AM, > wrote: Well, complete partition pruning definitely did not occur. (See my email about running the trace file through orasrp - multiple partitions were touched/checked/scanned for the partitioned table in question. It threw me off that after execution it still said KEY for the execution plan. (Unless you're telling me that every partition will be touched in a partition pruning scenario anyway which I don't *think* would happen but perhaps it might) Chris From: Mohamed Houri [mailto:mohamed.houri@gmail.com] Sent: Thursday, June 06, 2013 10:26 AM To: Taylor Christopher - Nashville Cc: ORACLE-L Subject: Re: Partitioned Explain Plan - Pstart/Pstop = KEY after execution The partition pruning did occur in this case. This is just an indication that during the parse time the optimizer couldn't know what is the exact partition to prune. This happens when you are using partition keys as bind variable Best regards Mohamed Houri www.hourim.wordpress.com 2013/6/6 >> Env: 10.2.0.4 RAC Linux 64-bit I've got an explain plan with Pstart/Pstop with "KEY" instead of partition numbers. I've been reading http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf (The Oracle Optimizer Explain the Explain Plan) and it explains that KEY is used when Oracle believes the execution will dynamically partition prune. So I executed my statement and traced it (of course) and still the explain plan shows KEY KEY and running it through orasrp shows that many partitions were scanned (perhaps not all of them however - I haven't checked). So, is it safe to say that *after* execution if the plan has KEY KEY then partition pruning did not occur? Thanks!! Chris Taylor Oracle DBA Parallon IT&S -- http://www.freelists.org/webpage/oracle-l -- Bien Respectueusement Mohamed Houri -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l