Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!lnewsoutpeer01.lnd.ops.eu.uu.net!bnewsinpeer00.bru.ops.eu.uu.net!bnewspost00.bru.ops.eu.uu.net!emea.uu.net!not-for-mail From: "Niall Litchfield" Newsgroups: comp.databases.oracle.server References: <1115256129.754966.230680@o13g2000cwo.googlegroups.com> Subject: Re: What is the best method to collect EXPLAIN PLAN for a lot of SQL statement Date: Thu, 5 May 2005 09:52:05 +0100 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Lines: 22 Message-ID: <4279deac$0$304$cc9e4d1f@news-text.dial.pipex.com> NNTP-Posting-Host: 81-178-109-126.dsl.pipex.com X-Trace: 1115283117 news-text.dial.pipex.com 304 81.178.109.126:2503 X-Complaints-To: abuse@uk.uu.net Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:242053 wrote in message news:1115256129.754966.230680@o13g2000cwo.googlegroups.com... In 9i there is a new view V$SQLPLAN which will contain the plans for your statements. I'd recommend that you use that for the upgraded db. For the 8i part of it I guess you'd need to write some sort of script to populate plan_table with tyhe plan for each statement (generating a new statement id for each). I'd suggest not bothering with comparing plans until you have identified those statements that perform poorly after the upgrade. HTH -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com