|
Oracle FAQ
|
Your Portal to the Oracle Knowledge Grid
|
Home ->
Community ->
Mailing Lists ->
Oracle-L ->
RE: Collecting table statistics
RE: Collecting table statistics
I've check the bug on metalink. The bug was discovered
on 8.1.6. Any info if it is alive on 8.1.5.
I've did small tests and it's working.
- "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU> a
écrit : > Your attention is invited to Bug 1192012;
there are
> situations where the
> "gather stale" option of dbms_stats does not work;
> i.e., it fails to gather
> statistics on tables for which it should.
> The above bug is fixed in 8.1.7
>
>
> Also the gathering procedure may result in errors:
>
> create or replace package nightly_stats as
>
> procedure gather_stale_stats_nightly(schema_owner
> in varchar2);
> procedure gather_empty_stats_nightly(schema_owner
> in varchar2);
> procedure gather_NIGHTLY_STATS_schemas;
>
> end nightly_stats;
> /
>
> create or replace package body nightly_stats as
> procedure gather_stale_stats_nightly(schema_owner
> in varchar2) as
> BEGIN
> DBMS_STATS.GATHER_SCHEMA_STATS
> (OWNNAME => schema_owner,
> CASCADE => TRUE,
> options => 'GATHER STALE');
> END gather_stale_stats_nightly;
> procedure gather_empty_STATS_nightly(schema_owner
> in varchar2) as
> BEGIN
> DBMS_STATS.GATHER_SCHEMA_STATS
> (OWNNAME => schema_owner,
> CASCADE => TRUE,
> options => 'GATHER EMPTY');
> END gather_empty_stats_nightly;
>
>
> procedure gather_NIGHTLY_STATS_schemas as
> schema_owner varchar2(30);
> cursor get_schema is
> select distinct owner from dba_tables where owner
> != 'SYS';
> BEGIN
> open get_schema;
> loop
> fetch get_schema into schema_owner;
> Exit when get_schema%notfound;
> gather_empty_stats_nightly(schema_owner);
> gather_stale_stats_nightly(schema_owner);
> end loop;
> close get_schema;
> end gather_NIGHTLY_STATS_schemas;
> end nightly_stats;
> /
>
>
>
> On one and only one of our 8i databases executing
> nightly_stats.gather_nightly_stats_schemas, I have
> received the following
> messages:
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error:
> character string buffer too
> small
> ORA-06512: at "SYS.DBMS_STATS", line 4560
> ORA-06512: at "SYS.DBMS_STATS", line 4683
> ORA-06512: at "SYS.DBMS_STATS", line 4655
> ORA-06512: at "ORACLE.NIGHTLY_STATS", line 11
> ORA-06512: at "ORACLE.NIGHTLY_STATS", line 27
> ORA-06512: at line 1
>
> The occurs when
> DBMS_STATS.GATHER_SCHEMA_STATS
> (OWNNAME => schema_owner,
> CASCADE => TRUE,
> options => 'GATHER EMPTY');
>
> is being processed. However if I change the
> CASCADE argument to FALSE, the
> error does not occur. Once this is done , CASCADE
> => TRUE
> works, but then the gather empty option would by
> pass the problem object
> anyway.
>
> The buffer is one completely inder Oracle's control.
> The analyst said this
> was fixed in 8.1.7 as well, but I'm a bit skeptical.
>
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
> -----Original Message-----
> Sent: Friday, July 21, 2000 1:48 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I've automated it so I don't have to worry about
> stats. Here's my PR to
> damagement write-up:
>
> We are using the cost-based optimizer for our
> implementation so it is
> important that we keep optimizer statistics
> up-to-date. But computing these
> statistics can be very resource intensive requiring
> lots of CPU and a
> tablespace for temporary sorts of up to 1.25 times
> the size of the largest
> table being analyzed. We take advantage of some of
> the latest Oracle
> features and packages to reduce the amount of
> resource used.
>
> Here are the steps:
> 1. First we gather complete statistics on the entire
> myzoo schema as
> follows:
>
> SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS -
> ('MYZOO',null,null,null,null,null,TRUE);
>
>
> 2. Next we create dynamic SQL to alter all the
> tables to implement
> statistical monitoring. Example DDL for a single
> table:
>
> SQL> alter table myzoo.TBARZ_SESSION_LOGS
> monitoring;
>
>
> 3. Finally, on a weekly basis, we refresh the
> statistics. The first two
> steps above only need to be done once after database
> creation. This step is
> performed every week or as needed. Here's the
> command to refresh the
> statistics:
>
> SQL> execute
> DBMS_STATS.GATHER_SCHEMA_STATS('MYZOO',null,FALSE, -
> 'FOR ALL COLUMNS SIZE
> 1',null,'DEFAULT',TRUE,null,null,'GATHER STALE');
>
>
> By putting the tables in "monitor" mode, Oracle
> tracks statistical changes
> whenever any DML is executed. If the amount data
> changes are such that it
> could affect optimization, Oracle marks the table or
> index as "stale." When
> we perform step 3 above, we are only recomputing
> statistics on the objects
> that need it. This saves a lot of computing resource
> for our 24X7
> environment. For more information see the "Automated
> Statistics Gathering"
> section of the Oracle Tuning manual. Also, reference
> the Oracle packages
> documentation for information on DBMS_STATS.
>
> Here's a shell script for crontab:
>
> # File: getstats.sh, Steve Orr, 5/9/00
> # Purpose: Oracle maintenance, recompute stats.
> # Usage: Executed as an Oracle crontab job.
> (Implement as DBMS_JOB?)
> . /usr/bin/dbaenv
> LOGFILE="$LOGDIR/stats.log"
> {
> echo "`date` -- Compute Stats."
> ORACLE_SID=dazu1;export ORACLE_SID
> sqlplus -s / << EOSQL
> execute
> DBMS_STATS.GATHER_SCHEMA_STATS('MYZOO',null,FALSE, -
> 'FOR ALL COLUMNS SIZE
> 1',null,'DEFAULT',TRUE,null,null,'GATHER STALE')'
> exit;
> EOSQL
> echo "`date` -- Done recomputing stats on
> $ORACLE_SID"
> } >>$LOGFILE
>
>
> HTH... dah, well yeah... I just gave it away!
>
> Happy Friday!!!!!!
> Steve Orr
>
>
>
> -----Original Message-----
> Kirti
> Sent: Friday, July 21, 2000 12:46 PM
>
=== message truncated ===
Stephane Paquette
Received on Fri Jul 28 2000 - 10:01:26 CDT
Original text of this message
