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: RE: Schedule Analyze using DBMS_STATS ???

Re: RE: Schedule Analyze using DBMS_STATS ???

From: Prem Khanna J <jprem_at_kssnet.co.jp>
Date: Tue, 03 Jun 2003 06:54:52 -0800
Message-ID: <F001.005A8D82.20030603065452@fatcity.com>


Thanx a lot Mark.
Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-)

So,can i conclude that DBMS_STATS is better than ANALYZE ?!

Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know !

Regards,
Jp.

3-6-2003 22:01:32, "Mark Leith" <mark_at_cool-tools.co.uk> wrote:

>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

-- 
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).
Received on Tue Jun 03 2003 - 09:54:52 CDT

Original text of this message

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