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: [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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: [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).
