| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Schedule Analyze using DBMS_STATS ???
JP,
After you've initially created statistics and set the tables into monitoring mode this script will create a DBMS_JOB that keeps them up to date each morning:
set serverout on size 100000
prompt "Connecting to SYS"
connect sys@&_dbname
grant select on dba_tab_modifications to system;
grant analyze any to system;
prompt "Connecting as System"
@connect system@&_dbname
create procedure compute_daily_stats is
begin
for a in (select distinct table_owner from dba_tab_modifications) loop
DBMS_STATS.GATHER_SCHEMA_STATS(a.table_owner,
NULL,FALSE,
'FOR ALL indexed COLUMNS SIZE 1',
NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE','DEFAULT');
end loop;
declare
jb integer;
begin
dbms_job.submit(jb, 'compute_daily_stats;',trunc(sysdate+1)+(6/24),'trunc(sysdate+1)+(6/24)',FALSE);
dbms_output.put_line('Job is '||jb);
end;
/
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
Sent: Tuesday, June 03, 2003 11:25 AM
To: Multiple recipients of list ORACLE-L
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: DGoulet_at_vicr.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:54:42 CDT
 |
 |