From oracle-l-bounce@freelists.org Wed Apr 27 12:40:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3RHe8us003617 for ; Wed, 27 Apr 2005 12:40:08 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3RHe74Z003613 for ; Wed, 27 Apr 2005 12:40:07 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B212F186B45; Wed, 27 Apr 2005 11:37:39 -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 07593-07; Wed, 27 Apr 2005 11:37:39 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 29FC6187570; Wed, 27 Apr 2005 11:37:39 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=IsPvfTfwv0K3bE5ImSsMewgF4Ji4hXbLx2Rrb7VYF8NZvIj8mZtXJgpCNvoz7F+cqqlnmDAVq7gIAtiVB5hwqyFxA0Bx5e2Nx1gDzPALzVhxUZ2N1w+xYI/3OZKDzXYC8iRtktULAxnq4e5HLiuH6bMIwQOyHf5V/kgjFKW8HP0= Message-ID: <52a152eb050427093526cae1cf@mail.gmail.com> Date: Wed, 27 Apr 2005 12:35:46 -0400 From: Christo Kutrovsky To: oracle-l Subject: procedure call with multiple children Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline X-archive-position: 19087 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: kutrovsky.oracle@gmail.com Precedence: normal Reply-To: kutrovsky.oracle@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL,LINES_OF_YELLING autolearn=ham version=2.63 Hello All, We have a rather bizare situation. We have a call to a procedure which has multiple children. The call looks like this: call package.procedure(:1,:2) As you can see, it uses 2 parameters which are bound via bind variables. We run this concurently in 12 sessions, and during the time this runs we would have 400+ of the "call package.procdure" in the shared pool. We fail to understand why this call would not be shared. We examined v$sql_shared_cursor and the only columns marked with Y are: OPTIMIZER_MISMATCH STATS_ROW_MISMATCH The first one i can understand, even thow it's a mystery too , we're not changing anything. The second one however is just not right. How can a call to a procedure be invalidated by STATS ? And we would have 400+ versions of this sql, but only 50-ish rows in v$sql_shared_cursor. And why would we have 400+ versions of this exact same procedure call. FYI:=20 cursor_sharing is FORCE 9i database --=20 Christo Kutrovsky Database/System Administrator The Pythian Group -- http://www.freelists.org/webpage/oracle-l