From oracle-l-bounce@freelists.org Tue Mar 2 03:11:32 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i229BWg03631 for ; Tue, 2 Mar 2004 03:11:32 -0600 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 i229BWo03626 for ; Tue, 2 Mar 2004 03:11:32 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 41CA7394C48; Tue, 2 Mar 2004 04:12:04 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Mar 2004 04:10:53 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from bristol24.audit-commission.gov.uk (mailhost.audit-commission.gov.uk [193.128.236.210]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0432F394BDA for ; Tue, 2 Mar 2004 04:10:51 -0500 (EST) Received: from bristol21.bristol.ac (unverified) by bristol24.audit-commission.gov.uk (Content Technologies SMTPRS 4.3.6) with SMTP id for ; Tue, 2 Mar 2004 09:14:27 +0000 Received: from AC-Message_Server by bristol21.bristol.ac with Novell_GroupWise; Tue, 02 Mar 2004 09:09:03 +0000 Message-Id: X-Mailer: Novell GroupWise Internet Agent 5.5.5.1 Date: Tue, 02 Mar 2004 09:08:33 +0000 From: "Niall Litchfield" To: Subject: RE: query slow in 9i, but not slow in 8i Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Disposition: inline X-archive-position: 239 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: n-litchfield@audit-commission.gov.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l I wondered about pga_aggregate_target as well? My impression was that if you set the new parameters and the old ones the new ones took precedence (ie sas etc were ignored). I haven't tested this. Niall Litchfield Oracle DBA Audit Commission +44 117 975 7805 > -----Original Message----- > From: breitliw@centrexcc.com > Sent: 02 March 2004 05:12 > To: breitliw@centrexcc.com; oracle-l@freelists.org > Subject: Re: query slow in 9i, but not slow in 8i > > > At 09:44 PM 3/1/2004, you wrote: > > [...] > > >It did not seem to help. > > What did not seem to help? I am questioning if not having > workarea_size_policy=auto is overriding the setting of > sort_area_size and > hash_area_size. If that is so, then your exercise didn't > prove or disprove > anything. > > >On Mon, 1 Mar 2004, Wolfgang Breitling wrote: > > > > > >NAME > > > >---------------------------------------------------------------- > > > >VALUE > > > > >------------------------------------------------------------- > ---------- > > --------- > > > >workarea_size_policy > > > >AUTO > > > > > > > >Would this value have some effect on my query? > > > > > > It could. What is the pga_aggregate_target? I couldn't > quickly find what > > > overrides what: > > > Does setting sort_area_size or hash_area_size override > > > workare_size_policy=auto or vice versa? > > > Does anyone know? (Rhetorical question - someone'll > know). I lean towards > > > the former. > > > > > > Run a 10053 event trace when explaining the sql - both in > 8i and in 9i. It > > > may be too big to post. You can e-mail it to me. > > > > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > > ---------------------------------------------------------------- > 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 > ----------------------------------------------------------------- > > ********************************************************************** This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or professional privilege. Any dissemination, distribution, copyright or use of this communication without prior permission of the sender is strictly prohibited. ********************************************************************** ---------------------------------------------------------------- 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 -----------------------------------------------------------------