Thanks Mark - That is exactly what I was just getting ready to write!
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, June 03, 2003 8:02 AM
To: Multiple recipients of list ORACLE-L
Prem,
Use DBMS_JOBS to run the analyze:
===================================
undef username
undef password
grant analyze any to &&username
/
connect &&username/&&password
CREATE or REPLACE PROCEDURE analyze_tables (
v_stat_type IN VARCHAR2 := 'COMPUTE') AS
CURSOR c IS
SELECT DISTINCT owner
FROM all_tables
WHERE owner not in ('SYS','SYSTEM');
BEGIN
FOR any_row IN c LOOP
dbms_utility.analyze_schema(
any_row.owner,v_stat_type);
END LOOP;
END;
/
===================================
variable jobno number
declare jobno number;
BEGIN
dbms_job.submit(:jobno,
'begin &&username.analyze_tables; end;',
to_date('03jun0304:00','DDMONYYHH24:MI'),
'trunc(sysdate)+(1+(4/24))');
END;
===================================
The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM,
at 4:00am every day. Modify it to your own needs, but it should give you a
starting point..
I would also recommend using DBMS_STATS to generate your statistics.
Have fun! ;0)
Mark
===================================================
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===================================================
http://www.cool-tools.co.uk
Maximising throughput & performance
-----Original Message-----
Sent: 03 June 2003 12:55
To: Multiple recipients of list ORACLE-L
Guys,
I would like to scedule the process of analyzing tables/indexes
using DBMS_STATS ?
Hope someone of u would have a script for the same.
can u share with me please ?!
BTW,Which is advisable : ANALYZE or DBMS_STATS ?
there was a discussion about the same on the list also.
but not found any conclusion yet.
anu suggestions !!!
the ENV is oracle 9.2.0.1/Win2K.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
