Re: dbms_stats.gather_schema

From: Darrell Landrum <darrell_at_landrum.com>
Date: Sun, 14 Mar 2004 21:18:50 -0600
Message-ID: <011401c40a3c$3d1b2080$6501a8c0@SHIRE>

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):

FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer] This value is passed to all of the individual tables.

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

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 ----- From: Paula Winkler To: oracle-l_at_freelists.org 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.

     dbms_stats.gather_schema_stats ( 
        NULL,   --ownname VARCHAR2, 
        20,   --estimate_percent NUMBER DEFAULT NULL, 
        FALSE,   --block_sample BOOLEAN DEFAULT FALSE, 


        'FOR ALL COLUMNS SIZE 25',   --method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1', 

        4,   --degree NUMBER DEFAULT NULL, 


        'ALL',   --granularity VARCHAR2 DEFAULT 'DEFAULT', 

        TRUE   --cascade BOOLEAN DEFAULT FALSE 
       );

Thank you in advance for your help!

Paula W. Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_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
-----------------------------------------------------------------

Received on Sun Mar 14 2004 - 21:15:21 CST

Re: dbms_stats.gather_schema_