RE: fire off multiple dbms_stats.gather

From: David <thump_at_cosmiccooler.org>
Date: Thu, 8 Jul 2004 10:24:52 -0700 (PDT)
Message-ID: <3250.64.37.153.21.1089307492.squirrel@www.cosmiccooler.org>

I decided to take your advice and revisit sample size auto functionality. My concern is the overhead of the checking degrades the time to complete, but as I said last time I tried I was also testing histograms...

BEGIN dbms_stats.gather_schema_stats(ownname=> 'SGRANT' ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,block_sample=>TRUE ,degree=>4,options=>'GATHER EMPTY',GRANULARITY=>'PARTITION'); END;

ERROR at line 1:

ORA-06550: line 1, column 87:
PLS-00302: component 'AUTO_SAMPLE_SIZE' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

If I replace DBMS_STATS.AUTO_SAMPLE_SIZE with 10, then I receive no error. I also tried quotes...sigh...what is the solution? Thanks

-- 
..
David



> The sample size auto just takes too long for us, although I might give

> that another evaluation as last time I played with it was also with

> histograms.

>

> We plan on using gather monthly and gather empty weekly...

> --

> ..

> David

>

>> David

>>    Oops, I read your posting more closely and it looks as if you are

>> only

>> gathering statistics on tables that don't have any. Am I reading that

>> correctly. Also, you might want to consider the SAMPLE SIZE AUTO. With a

>> fixed percentage you may be undersampling small tables and oversampling

>> large ones.

>

>


----------------------------------------------------------------
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 Thu Jul 08 2004 - 12:22:04 CDT

RE: fire off multiple dbms_stats.gather_schema