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( 200000 );
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-YYYY 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-YYYY 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-YYYY HH24:MI:SS' ) ||
' and completed at ' || TO_CHAR( v_edate, 'DD-MON-YYYY 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 see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vergara, Michael (TEM)
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).
