fire off multiple dbms_stats.gather_schema 2004-07-08 - By DENNIS WILLIAMS
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@(protected)
I said it "looked " clear - Riddick
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of David
Sent: Thursday, July 08, 2004 12:25 PM
To: oracle-l@(protected)
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 (See ORA-06550.ora-code.com): line 1, column 87:
PLS-00302: component 'AUTO_SAMPLE_SIZE ' must be declared
ORA-06550 (See ORA-06550.ora-code.com): 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@(protected)
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
