Oracle-L: RE: Schedule Analyze using DBMS

From: Vergara, Michael (TEM) <mvergara_at_guidant.com>
Date: Tue, 03 Jun 2003 08:19:49 -0800
Message-ID: <F001.005A8F49.20030603081949@fatcity.com>

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: mvergara_at_guidant.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 - 11:19:49 CDT

RE: Schedule Analyze using DBMS_STATS ???