RE: RE: Schedule Analyze using DBMS_STATS ???
There are a Lot of options to DBMS_STATS .. And be Judicious in using
the same. Read the docs before attempting it.
BTW .. What is the session waiting for .. Just Check v$session_wait.
HTH
Best Regards,
Ganesh R
DID : +65-6215-8413
HP : +65-9067-8474
===
Live to learn... forget... and learn again.
===
-Original Message-
Keshava Murthy (Cognizant)
Sent: Wednesday, June 04, 2003 8:55 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Everyone is recommending DBMS_STATS to be used for
computing the statistics. But even after specifying the parallel option
, DBMS_STATS is taking lots of time ! in comparison to Analyze...
In case of tables with a million records the query just hangs when i
use the stats package...
Can anybody tell me whts happening ?
Regards
kesh
-Original Message-
Sent: Tuesday, June 03, 2003 8:55 PM
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/ora0210
0802
_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: Ganesh Raja
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
RE: Schedule Analyze using DBMS_STATS ???
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
===
Mark Leith | T: +44 (0)1905 330 281
Sales Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput performance
-Original Message-
Sent: 03 June 2003 12:55
To: Multiple recipients of list ORACLE-L
Guys,
I would like to scedule the process of analyzing tables/indexes
using DBMS_STATS ?
Hope someone of u would have a script for the same.
can u share with me please ?!
BTW,Which is advisable : ANALYZE or DBMS_STATS ?
there was a discussion about the same on the list also.
but not found any conclusion yet.
anu suggestions !!!
the ENV is oracle 9.2.0.1/Win2K.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
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).
---
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).
Re: RE: Schedule Analyze using DBMS_STATS ???
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
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).
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: [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).
RE: Schedule Analyze using DBMS_STATS ???
Thanks Mark - That is exactly what I was just getting ready to write!
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, June 03, 2003 8:02 AM
To: Multiple recipients of list ORACLE-L
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
===
Mark Leith | T: +44 (0)1905 330 281
Sales Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: [EMAIL PROTECTED]
===
http://www.cool-tools.co.uk
Maximising throughput performance
-Original Message-
Sent: 03 June 2003 12:55
To: Multiple recipients of list ORACLE-L
Guys,
I would like to scedule the process of analyzing tables/indexes
using DBMS_STATS ?
Hope someone of u would have a script for the same.
can u share with me please ?!
BTW,Which is advisable : ANALYZE or DBMS_STATS ?
there was a discussion about the same on the list also.
but not found any conclusion yet.
anu suggestions !!!
the ENV is oracle 9.2.0.1/Win2K.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
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).
---
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: DENNIS WILLIAMS
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).
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 10
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
RE: Schedule Analyze using DBMS_STATS ???
This may be a little over-the-top for your needs, but it works for us.
We have a standard DBA-level user that we create in every instance.
This is a stored procedure within that instance. We schedule it using
DBMS_JOB.
Typically, we don't analyze every schema every time. Usage patterns
vary widely, so I wrote this to be schema-specific. It also creates a
log file in the /tmp directory, and sends output to the session with
DBMS_OUTPUT. You should be able to Window-icize this procedure
- it's written for UNIX.
There is an INSERT statement near the bottom, into a table called
SEND_EMAIL. This is a table that uses a trigger to send the attached
e-mail message. You can take this out if you don't have a similar
mechanism.
HTH,
Mike
create or replace procedure gdt_analyze_schema
( p_schema_name IN VARCHAR2 ) AS
/*
* *
* Name: GDT_ANALYZE_SCHEMA *
* Author : M. Vergara *
* Date: 19-Sept-2002*
* Purpose : To compute fresh statistics for a specific schema. The *
* notion here is that this procedure will be executed from*
* the DBMS_JOB processor. *
* *
* Change Log: *
* Chg# Date Description *
* --- -*
* *
*/
--
v_fh UTL_FILE.file_type;
v_sdate DATE;
v_edate DATE;
v_host_name V$INSTANCE.host_name%TYPE;
v_logfile_name VARCHAR2(64);
/* Error handling variables */
v_error_code NUMBER;
v_error_message VARCHAR2( 200 );
--
BEGIN
DBMS_OUTPUT.enable( 20 );
DBMS_OUTPUT.put_line( p_schema_name || ' Analysis started...' );
--
SELECT host_name
INTO v_host_name
FROM v$instance;
--
v_sdate:= SYSDATE;
v_logfile_name := 'Analyze_' || p_schema_name;
v_fh := UTL_FILE.fopen( '/tmp', v_logfile_name, 'a' );
UTL_FILE.put_line( v_fh, '' );
UTL_FILE.put_line( v_fh, p_schema_name || ' Analysis started at ' ||
TO_CHAR( v_sdate, 'DD-MON- HH24:MI:SS' ));
UTL_FILE.fflush( v_fh );
--
UTL_FILE.put_line( v_fh, 'Compute Current Statistics' );
UTL_FILE.fflush( v_fh );
DBMS_UTILITY.analyze_schema( schema = p_schema_name, method = 'COMPUTE' );
--
UTL_FILE.put_line( v_fh, 'Compute Statistics on Indexed Columns' );
UTL_FILE.fflush( v_fh );
DBMS_UTILITY.analyze_schema( schema = p_schema_name,
method = 'COMPUTE',
method_opt = 'FOR ALL INDEXED COLUMNS' );
--
v_edate := SYSDATE;
UTL_FILE.put_line( v_fh, 'Analyze Complete at ' ||
TO_CHAR( v_edate, 'DD-MON- HH24:MI:SS' ));
UTL_FILE.fflush( v_fh );
--
INSERT INTO send_email( msg_to, msg_from, msg_subj, msg_text )
VALUES( 'mvergara', 'dbamon',
p_schema_name|| ' Analysis on ' || v_host_name,
'Analyze started at ' || TO_CHAR( v_sdate, 'DD-MON- HH24:MI:SS' ) ||
' and completed at ' || TO_CHAR( v_edate, 'DD-MON- HH24:MI:SS' ) ||
'. Check /tmp/' || v_logfile_name || ' on ' ||
v_host_name || ' for details.' );
COMMIT;
--
UTL_FILE.put_line( v_fh, 'Mail Sent and work committed.' );
UTL_FILE.fflush( v_fh );
UTL_FILE.fclose( v_fh );
--
EXCEPTION
WHEN OTHERS THEN
v_error_code:= SQLCODE;
v_error_message := SUBSTR( SQLERRM, 1, 200 );
ROLLBACK;
UTL_FILE.put_line( v_fh, 'Exception Exit.' );
UTL_FILE.put_line( v_fh, 'ERROR! (' || v_error_code || ')' );
UTL_FILE.put_line( v_fh, 'ERROR : ' || v_error_message );
UTL_FILE.fflush( v_fh );
UTL_FILE.fclose( v_fh );
DBMS_OUTPUT.put_line( 'Exception Exit' );
DBMS_OUTPUT.put_line( 'ERROR! (' || v_error_code || ')' );
DBMS_OUTPUT.put_line( 'ERROR : ' || v_error_message );
END;
/
show errors
-Original Message-
Sent: Tuesday, June 03, 2003 4:55 AM
To: Multiple recipients of list ORACLE-L
Guys,
I would like to scedule the process of analyzing tables/indexes
using DBMS_STATS ?
Hope someone of u would have a script for the same.
can u share with me please ?!
BTW,Which is advisable : ANALYZE or DBMS_STATS ?
there was a discussion about the same on the list also.
but not found any conclusion yet.
anu suggestions !!!
the ENV is oracle 9.2.0.1/Win2K.
--
Please
RE: RE: Schedule Analyze using DBMS_STATS ???
DBMS_STATS is quicker because it can run in parallel mode. Analyze cannot.
-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: Mercadante, Thomas F
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).
RE: Schedule Analyze using DBMS_STATS ???
Title: RE: Schedule Analyze using DBMS_STATS ??? We don't analyze schema, we use dbms_stats ... I wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time. This package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams. I prefer to use cron, but you can use it with dbms_job as well. Currently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say 5%, skip the table from stats collections. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 03, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: Schedule Analyze using DBMS_STATS ??? Thanks Mark - That is exactly what I was just getting ready to write! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Schedule Analyze using DBMS_STATS ???
Title: RE: Schedule Analyze using DBMS_STATS ??? The sample output looks like ... [EMAIL PROTECTED] sys SQL*Plus: Release 9.2.0.2.0 - Production on Tue Jun 3 11:57:51 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. 11:57:51 SQL select * from system.v_analysis_info; Analysis Information - Group 01 includes 2490 tables, analysis should take approx 01574.93 seconds. Group 02 includes 65 tables, analysis should take approx 01579.41 seconds. Group 03 includes 26 tables, analysis should take approx 01578.01 seconds. Group 04 includes 16 tables, analysis should take approx 01586.48 seconds. Group 05 includes 9 tables, analysis should take approx 01472.85 seconds. Group 06 includes 5 tables, analysis should take approx 01594.39 seconds. Group 07 includes 2 tables, analysis should take approx 01550.56 seconds. Group 08 includes 1 tables, analysis should take approx 01169.40 seconds. Group 09 includes 1 tables, analysis should take approx 01749.20 seconds. Group 10 includes 1 tables, analysis should take approx 01991.25 seconds. 10 rows selected. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Jamadagni, Rajendra Sent: Tuesday, June 03, 2003 12:02 PM To: '[EMAIL PROTECTED]' Subject: RE: Schedule Analyze using DBMS_STATS ??? We don't analyze schema, we use dbms_stats ... I wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time. This package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams. I prefer to use cron, but you can use it with dbms_job as well. Currently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say 5%, skip the table from stats collections. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Schedule Analyze using DBMS_STATS ???
With some versions, dbms_stats has issues with partitions. Since we use partitioning, we wrote a script to simulate the gather stale functionality. Monitoring is enabled and we do a compute of any table or partition thereof that has more than x% modified. This is essentially what the gather stale command does, but our script has the benefit that we can adjust the percantage if we feel it is necessary. Though in about 1 1/2 years, we haven't felt it neccessary to adjust that number. We choose to do a full COMPUTE since each partition is relatively small and the whole analyze_stale runs well within our maintenance window. We are close to being mostly on 9i and will be looking at dbms_stats to see if we can start using it. Stephen [EMAIL PROTECTED] 06/03/03 09:59AM We don't analyze schema, we use dbms_stats ... I wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time. This package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams. I prefer to use cron, but you can use it with dbms_job as well. Currently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say 5%, skip the table from stats collections. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, June 03, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Thanks Mark - That is exactly what I was just getting ready to write! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert 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).
RE: RE: Schedule Analyze using DBMS_STATS ???
Hi all,
Everyone is recommending DBMS_STATS to be used for computing the
statistics.
But even after specifying the parallel option , DBMS_STATS is taking lots of time !
in comparison to Analyze...
In case of tables with a million records the query just hangs when i use the stats
package...
Can anybody tell me whts happening ?
Regards
kesh
-Original Message-
Sent: Tuesday, June 03, 2003 8:55 PM
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).
This e-mail and any files transmitted with it are for the sole use of the intended
recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or
copying of this email or any action taken in reliance on this e-mail is strictly
prohibited and may be unlawful.
Visit us at http://www.cognizant.com
Schedule Analyze using DBMS_STATS ???
Guys, I would like to scedule the process of analyzing tables/indexes using DBMS_STATS ? Hope someone of u would have a script for the same. can u share with me please ?! BTW,Which is advisable : ANALYZE or DBMS_STATS ? there was a discussion about the same on the list also. but not found any conclusion yet. anu suggestions !!! the ENV is oracle 9.2.0.1/Win2K. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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).
