RE: RE: Schedule Analyze using DBMS_STATS ???

Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on a 9i or above release. One of the simple factors being that ANALYZE is being deprecated.. There seemed to be a number of bugs/quirks, whatever you want to call them, in certain 8.1.x versions, which are now fixed within 9i..

There's a number of threads about the issues, available in the list archives (you can find them at http://www.faqchest.com), which I dug around - as I remembered seeing a post by Connor McDonald, where he gave away an alternate script to gather stats, here's the mail:

http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora02100802 _06150.html

I think the general consensus is that DBMS_STATS is quicker. I've never personally done any comparative benchmarks to corroborate this sheep following attitude though.. ;)

Regards

Mark

-----Original Message-----
Sent: 03 June 2003 14:55
To: Mark Leith
Cc: ORACLE-L_at_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

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