From oracle-l-bounce@freelists.org Sun Mar 14 21:15:21 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2F3FLK00342 for ; Sun, 14 Mar 2004 21:15:21 -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 i2F3FLo00337 for ; Sun, 14 Mar 2004 21:15:21 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DD57E3903E0; Sun, 14 Mar 2004 22:14:18 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 14 Mar 2004 22:13:09 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from c000.snv.cp.net (h020.c000.snv.cp.net [209.228.32.84]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 61A2B3908C4 for ; Sun, 14 Mar 2004 22:13:09 -0500 (EST) Received: (cpmta 23707 invoked from network); 14 Mar 2004 19:18:48 -0800 Received: from 68.113.193.93 (HELO SHIRE) by smtp.landrum.com (209.228.32.84) with SMTP; 14 Mar 2004 19:18:48 -0800 X-Sent: 15 Mar 2004 03:18:48 GMT Message-ID: <011401c40a3c$3d1b2080$6501a8c0@SHIRE> From: "Darrell Landrum" To: References: <20040314233655.69349.qmail@web61107.mail.yahoo.com> Subject: Re: dbms_stats.gather_schema_ Date: Sun, 14 Mar 2004 21:18:50 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0111_01C40A09.F2206810" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-archive-position: 802 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: darrell@landrum.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------=_NextPart_000_0111_01C40A09.F2206810 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Paula, Although I do not understand why, it may be a conflict between the = method_opt and degree. Here's a quote from the 8.1.7 Supplied PL/SQL Packages document: method_opt Method options of the following format (the phrase 'SIZE 1' is = required to ensure gathering statistics in parallel and for use with the = phrase hidden):=20 FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer] This value is passed to all of the individual tables.=20 =20 degree Degree of parallelism (NULL means use table default value).=20 =20 Maybe try it once with 'for all columns size 1' and see if parallelism = occurs as expected. If you do, let us know how it works out. ----- Original Message -----=20 From: Paula Winkler=20 To: oracle-l@freelists.org=20 Sent: Sunday, March 14, 2004 5:36 PM Subject: dbms_stats.gather_schema_ Hi listers, I am trying to improve our statistics gathering process which takes = several hours. Does the degree parameter work in the below = dbms_stats.gather_schema_stats call in an Oracle 8.1.7.4 database on = HPUX 11i? Oracle seems to ignore the degree specification and run = serially. =20 dbms_stats.gather_schema_stats (=20 NULL, --ownname VARCHAR2,=20 20, --estimate_percent NUMBER DEFAULT NULL,=20 FALSE, --block_sample BOOLEAN DEFAULT FALSE,=20 'FOR ALL COLUMNS SIZE 25', --method_opt VARCHAR2 DEFAULT 'FOR = ALL COLUMNS SIZE 1',=20 4, --degree NUMBER DEFAULT NULL,=20 'ALL', --granularity VARCHAR2 DEFAULT 'DEFAULT',=20 TRUE --cascade BOOLEAN DEFAULT FALSE=20 );=20 Thank you in advance for your help! - Paula W. Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam ------=_NextPart_000_0111_01C40A09.F2206810 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Paula,
 
Although I do not understand why, it = may be a=20 conflict between the method_opt and degree.
Here's a quote from the 8.1.7 Supplied = PL/SQL=20 Packages document:
method_opt
 

Method options of the following format (the phrase = 'SIZE=20 1' is required to ensure gathering statistics in parallel = and for=20 use with the phrase hidden): 

FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer]

This value is passed to all of the individual=20 tables. 

degree
 

Degree of parallelism (NULL means use = table=20 default value). 

 
Maybe try it once with 'for all columns = size 1' and=20 see if parallelism occurs as expected.
If you do, let us know how it works=20 out.
----- Original Message -----
From:=20 Paula = Winkler=20
Sent: Sunday, March 14, 2004 = 5:36=20 PM
Subject: = dbms_stats.gather_schema_

Hi listers,
 
I am trying to improve our statistics gathering process = which takes=20 several hours.  Does the degree parameter work in the below=20 dbms_stats.gather_schema_stats call in an Oracle 8.1.7.4 database on = HPUX=20 11i?  Oracle seems to ignore the degree specification = and run=20 serially.
   dbms_stats.gather_schema_stats (=20
      NULL,   --ownname = VARCHAR2,=20
      20,   --estimate_percent = NUMBER=20 DEFAULT NULL,
      FALSE,  =20 --block_sample BOOLEAN DEFAULT FALSE, =
      'FOR=20 ALL COLUMNS SIZE 25',   --method_opt VARCHAR2 DEFAULT 'FOR = ALL=20 COLUMNS SIZE 1', 
      = 4,  =20 --degree NUMBER DEFAULT NULL,
     =20 'ALL',   --granularity VARCHAR2 DEFAULT 'DEFAULT',=20
      TRUE   --cascade BOOLEAN = DEFAULT=20 FALSE
     );
Thank you in advance for your help!
 
- Paula W.

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, = less=20 spam

------=_NextPart_000_0111_01C40A09.F2206810-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------