| 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: Collecting table statistics
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;
/
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-----
From: Steve Orr [mailto:sorr_at_arzoo.com]
Sent: Friday, July 21, 2000 1:48 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Collecting table statistics
I've automated it so I don't have to worry about stats. Here's my PR to damagement write-up:
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.
# 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
HTH... dah, well yeah... I just gave it away!
Happy Friday!!!!!!
Steve Orr
-----Original Message-----
Kirti
Sent: Friday, July 21, 2000 12:46 PM
To: Multiple recipients of list ORACLE-L
Hi,
That would be the new 'monitoring' option (as in , alter table <tablename>
monitoring, default is nomonitoring).
I have not used it yet, but remember reading about it..
- Kirti
> -----Original Message----- > From: Chuck Hamilton [SMTP:chuck_hamilton_at_yahoo.com] > Sent: Friday, July 21, 2000 1:57 PM > To: Multiple recipients of list ORACLE-L > Subject: Collecting table statistics > > Some time ago I thought I saw something on the list about 8i having the > ability to automatically collecet statistics on tables with needing to > issue the ANALYZE TABLE command. Can someone tell me how this works or > point me to a document that describes it? Is this what the MONITORING > clause of ALTER/CREATE table is for? > also send the HELP command for other information (like subscribing).
-- Author: Steve Orr INET: sorr_at_arzoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Sat Jul 22 2000 - 10:09:55 CDT
 |
 |