| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: fire off multiple dbms_stats.gather_schema
David
The following works, if that helps.
exec dbms_stats.gather_database_stats( DBMS_STATS.AUTO_SAMPLE_SIZE, cascade
=> true);
Also, in the documentation there is something about declaring the
AUTO_SAMPLE_SIZE variable.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
I said it "looked" clear - Riddick
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David
Sent: Thursday, July 08, 2004 12:25 PM
To: oracle-l_at_freelists.org
Subject: RE: fire off multiple dbms_stats.gather_schema
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=>T
RUE
,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:
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
-- .. DavidReceived on Thu Jul 08 2004 - 13:21:17 CDT
> 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------
 |
 |