From oracle-l-bounce@freelists.org Wed May 5 16:15:26 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45LFBc11948 for ; Wed, 5 May 2004 16:15:21 -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 i45LF1611912 for ; Wed, 5 May 2004 16:15:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 73A6272DE7A; Wed, 5 May 2004 16:05:47 -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 13912-76; Wed, 5 May 2004 16:05:47 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 50DB572DC93; Wed, 5 May 2004 16:05:35 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 16:04:17 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F30872D83B for ; Wed, 5 May 2004 16:04:17 -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 13912-31 for ; Wed, 5 May 2004 16:04:16 -0500 (EST) Received: from deet.btinternet.com (deet.btinternet.com [194.73.73.29]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BA26572D7EB for ; Wed, 5 May 2004 16:04:16 -0500 (EST) Received: from [217.42.79.77] (helo=Primary) by deet.btinternet.com with smtp (Exim 3.22 #25) id 1BLTmG-00017l-00 for oracle-l@freelists.org; Wed, 05 May 2004 22:18:04 +0100 Message-ID: <01a201c432e6$74920510$7102a8c0@Primary> From: "Jonathan Lewis" To: References: <409957B3.30554C03@sun.com> Subject: Re: PLAN_TABLE gotcha in 10g Date: Wed, 5 May 2004 22:18:05 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4519 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Clearly someone from Oracle has been reading my seminar notes - chapter 11 page 7 I think. I think it great !! Now if only they change autotrace so it doesn't do the delete bit and waste resources - and then, of course, every time you do an explain plan there are not stats on the table, so every report starts with a dynamic sample. And have you noticed when you trace dbms_xplan, the query against the plan table doesn't do a connect by any more - it a pure procedural process, walking the lines in order, so you can't figure out what sneaky tricks are built into the plan table content. On the plus side, the plan table had to be 'on commit preserve rows' - so you can copy it out before you end your session. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: "Daniel Fink" To: Sent: Wednesday, May 05, 2004 10:08 PM Subject: PLAN_TABLE gotcha in 10g I've been working with some sql in a new 10g db doing the typical 'explain plan for ' syntax. Today, I decided to preserve some of the plans, so I set statement_ids and commited them. Lo and behold, they were not there when I went back later in the day. In 10g, a PLAN_TABLE$ table is created and given the PUBLIC synonym PLAN_TABLE. Convenient, now I don't have to run utlxplan for every user. Just be disciplined and use distinct statement_ids. Unfortunately, the PLAN_TABLE$ is a global temporary table, so my records are deleted when the session ends! ARGH! FYI, this table is created with the catplan.sql script that is called by catalog.sql. Okay, Pete. Where's the doc on this one? ;) Daniel ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------