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;
end;
/
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Goulet, Dick
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).
