Oracle-L: RE: Schedule Analyze using DBMS

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Tue, 03 Jun 2003 05:01:32 -0800
Message-ID: <F001.005A8B78.20030603050132@fatcity.com>

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: mark_at_cool-tools.co.uk


===================================================

           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: jprem_at_kssnet.co.jp

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: 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).

---
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: mark_at_cool-tools.co.uk

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: 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 Tue Jun 03 2003 - 08:01:32 CDT

RE: Schedule Analyze using DBMS_STATS ???