Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Schedule Analyze using DBMS_STATS ???

RE: Schedule Analyze using DBMS_STATS ???

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 03 Jun 2003 07:04:53 -0800
Message-ID: <F001.005A8DCF.20030603070453@fatcity.com>


Thanks Mark - That is exactly what I was just getting ready to write!

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM 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 - 10:04:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US