| 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: ANALYZE question
I looked into the same thing some time ago. I found Oracle papers that
recommend using estimate 25% on the tables and a full analyze on the
indexes.
I wrote the script below to generate a script with all the analyze
statements I needed for the schema to be analyzed. I added date and time
displays so I could see how far along I am while the script is processing.
I run the output script on a weekly basis.
Ron Smith
set feedback off;
set heading off;
set pagesize 0;
set linesize 120;
set heading off;
set feedback off;
set show off;
spool analyze2.sql
select 'set feedback off;'|| CHR(10) || 'set heading off;' || CHR(10) ||
'set pagesize 0;' || 'set linesize 80;'|| CHR(10) |
| 'set heading off;' || CHR(10) || 'set feedback off;' || CHR(10) || 'spool
analyze_schema2.lst;' from dual
/
select 'select ' || '''' || object_type || ' ' || object_name || '''' ||',
to_char(sysdate, ''MM/DD/YYYY HH24:MI'') from dua
l;'|| CHR(10) || 'analyze '|| object_type || ' ' || owner || '.' ||
object_name || ' estimate statistics sample 25 percent f
or table;' from dba_objects where object_type in('TABLE') and owner = 'PROD'
order by object_type desc
/
select 'select ' || '''' || object_type || ' ' || object_name || '''' ||',
to_char(sysdate, ''MM/DD/YYYY HH24:MI'') from dua
l;'|| CHR(10) || 'analyze '|| object_type || ' ' || owner || '.' ||
object_name || ' compute statistics;' from dba_objects w
here object_type in('INDEX') and owner = 'PROD' order by object_type desc
/
This is what part of the output script looks like:
set feedback off;
set heading off;
set pagesize 0;
set linesize 80;
set heading off;
set feedback off;
spool analyze_schema2.lst;
select 'TABLE DIST', to_char(sysdate, 'MM/DD/YYYY HH24:MI') from dual; analyze TABLE PROD.DIST estimate statistics sample 25 percent for table;
select 'TABLE EXCEPTIONS', to_char(sysdate, 'MM/DD/YYYY HH24:MI') from dual; analyze TABLE PROD.EXCEPTIONS estimate statistics sample 25 percent for table;
Ron Smith
-----Original Message-----
Sent: Wednesday, July 24, 2002 11:25 AM
To: Multiple recipients of list ORACLE-L
Yechiel
Consider ANALYZE TABLE ESTIMATE STATISTICS.
If you consider sampling theory, with larger tables you shouldn't need to
inspect each row. You can vary the number of rows that are sampled. We had a
good discussion on this topic on this list awhile back.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, July 24, 2002 9:59 AM
To: Multiple recipients of list ORACLE-L
On the subject on analyzed:
We are doing analyze compute statistics and it takes about an hour.
Do you know of ways to speed it up?
Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent: Wednesday, July 24, 2002 3:54 PM
> On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote:
> > A question:
> >
> > If analyzing SYS objects is a bad idea, why is it included by default in
the
> > analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)?
> >
> > >
>
>> To REMOVE yourself from this mailing list, send an E-Mail message
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: adar76_at_inter.net.il 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-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.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-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: rlsmith_at_kmg.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-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jul 24 2002 - 11:58:59 CDT
 |
 |