Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 7083 invoked from network); 7 Jul 2006 11:28:51 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 7 Jul 2006 11:28:48 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 31409394082; Fri, 7 Jul 2006 12:28:39 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07223-06; Fri, 7 Jul 2006 12:28:39 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A3386394118; Fri, 7 Jul 2006 12:28:36 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 Jul 2006 12:27:48 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F3E6F394140 for ; Fri, 7 Jul 2006 12:27:47 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07002-03 for ; Fri, 7 Jul 2006 12:27:47 -0400 (EDT) Received: from NT15.oneneck.corp (dot092host.oneneck.net [63.226.42.92]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F1B43393FF5 for ; Fri, 7 Jul 2006 12:27:43 -0400 (EDT) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2663 Priority: normal Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6A1E2.6B0B073A" Subject: RE: Pinning/keeping plans in shared pool Date: Fri, 7 Jul 2006 09:28:45 -0700 Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1A49@NT15.oneneck.corp> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Pinning/keeping plans in shared pool thread-index: Acah3+xKlsSyPp4JRD2PZaTuTpYKSAAAUodA From: "Allen, Brandon" To: X-archive-position: 36834 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Brandon.Allen@OneNeck.com Precedence: normal Reply-to: Brandon.Allen@OneNeck.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net ------_=_NextPart_001_01C6A1E2.6B0B073A Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Sorry - I'm guilty of posting the below inquiry before searching the archives. I just searched them and found that Tanel Poder has already answered this question: =20 http://www.freelists.org/archives/oracle-l/12-2005/msg00588.html =20 It appears that one of the 20+ tables was analyzed last night (by the gather_stats_job) and that invalidated the plans. Apparently this is typical behaviour for the plan to be aged out when invalidated even though the cursor is kept. I'll try locking the stats on these tables and see if that keeps the plan from being aged out. =20 Regards, Brandon =20 ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Allen, Brandon =20 The problem is that the execution plan still seems to get aged out of the pool - if I query v$sql_plan for these hash_values, I get no rows, and when I run the queries again, they still take a long time to parse. This is my first experience with keeping objects in the shared pool so I don't know if this is the normal behaviour. Has anyone else seen the same? =20 Privileged/Confidential Information may be contained in this message or = attachments hereto. Please advise immediately if you or your employer do = not consent to Internet email for messages of this kind. Opinions, = conclusions and other information in this message that do not relate to = the official business of this company shall be understood as neither = given nor endorsed by it. ------_=_NextPart_001_01C6A1E2.6B0B073A Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Sorry - I'm guilty of posting the below inquiry = before=20 searching the archives.  I just searched them and found that Tanel = Poder=20 has already answered this question:
 
http://www.freelists.org/archives/oracle-l/12-2005/msg00588.html
 
It appears that one of the 20+ tables was = analyzed last=20 night (by the gather_stats_job) and that invalidated the=20 plans.  Apparently this is typical behaviour for the plan to = be aged=20 out when invalidated even though the cursor is kept.  I'll try = locking=20 the stats on these tables and see if that keeps the plan from being aged = out.
 
Regards,
Brandon
 

From: oracle-l-bounce@freelists.org=20 [mailto:oracle-l-bounce@freelists.org] On Behalf Of Allen,=20 Brandon
 
 The problem is that the = execution plan=20 still seems to get aged out of the pool - if I query v$sql_plan for = these=20 hash_values, I get no rows, and when I run the queries again, they still = take a=20 long time to parse.  This is my first experience with keeping = objects in=20 the shared pool so I don't know if this is the normal = behaviour.  Has=20 anyone else seen the same?
 

Privileged/Confidential Information may be contained in this message = or attachments hereto. Please advise immediately if you or your employer = do not consent to Internet email for messages of this kind. Opinions, = conclusions and other information in this message that do not relate to = the official business of this company shall be understood as neither = given nor endorsed by it.

 ------_=_NextPart_001_01C6A1E2.6B0B073A-- -- http://www.freelists.org/webpage/oracle-l