From oracle-l-bounce@freelists.org Thu Oct 7 04:25:40 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i979Pde17065 for ; Thu, 7 Oct 2004 04:25:39 -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 i979PdI17059 for ; Thu, 7 Oct 2004 04:25:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1A72D72C2E0; Thu, 7 Oct 2004 04:31:44 -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 19136-48; Thu, 7 Oct 2004 04:31:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7FB9772C18E; Thu, 7 Oct 2004 04:31:43 -0500 (EST) In-Reply-To: <910046b404100617057d7f3b93@mail.gmail.com> To: bdbafh@gmail.com Cc: oracle-l@freelists.org, oracle-l-bounce@freelists.org Subject: Re: PGA_AGGREGATE_TARGET is another way to instability. MIME-Version: 1.0 Message-ID: From: J.Velikanovs@alise.lv Date: Thu, 7 Oct 2004 12:26:21 +0300 X-MIMETrack: Serialize by Router on ROSS/IT ALISE/LV(Release 5.0.11 |July 24, 2002) at 2004.10.07 12:28:35, Serialize complete at 2004.10.07 12:28:35 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 10778 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: J.Velikanovs@alise.lv Precedence: normal Reply-To: J.Velikanovs@alise.lv X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org >I think that if your miss of sql in the shared pool is 10% that most >statements that are not frequently executed will quickly be aged out >and re-parsed during subsequent execution. How about great guideline - parse ones execute many times. In this context, the systems with good design which use bind variables are more instable then others ;) >How is this any different, if the bind variables used at parse (with >cursor_sharing=SIMILAR) differ greatly from one execution to another, >in terms of distribution? And this is just begining of the list: - PGA_AGGREGATE_TARGET - bind variables peeking - regular statistic gathering ... ... ... More features we have (use), more instable our systems ;) But there are some methods to get rid of it, or at least minimize impact I suppose. Jurijs On 07.10.2004 03:05:28 oracle-l-bounce wrote: >Jurijs, > >I think that if your miss of sql in the shared pool is 10% that most >statements that are not frequently executed will quickly be aged out >and re-parsed during subsequent execution. > >If then follows that this is only a concern if your application SQL >code is nearly perfectly written and statements are not >invalidated/aged out. > >Right. > > >How is this any different, if the bind variables used at parse (with >cursor_sharing=SIMILAR) differ greatly from one execution to another, >in terms of distribution? > >Paul > > -- http://www.freelists.org/webpage/oracle-l