Forum OpenACS Q&A: Analyzing on Oracle? best ways...

Hi!
I have done this commands to analyze my Oracle 8.1.6 DB, is something better?
Thanks!

$ svrmgrl
SVRMGR> connect internal
SVRMGR> execute dbms_utility.analyze_database('COMPUTE');
SVRMGR> execute dbms_utility.analyze_schema('WIMPY','ESTIMATE');
SVRMGR> execute dbms_utility.analyze_schema('WIMPY','COMPUTE');

$ sqlplus sys/rocael @analyze.sql

This are the contents of analyze.sql:

SET PAGES 0 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF TERMOUT OFF

TTITLE OFF
CLEAR COLUMN

SPOOL analyze.tmp

PROMPT SET ECHO ON VERIFY OFF FEEDBACK ON TERMOUT ON
PROMPT SPOOL analyze.res
PROMPT

SELECT 'ANALYZE TABLE ' || owner || '.' || table_name || ' COMPUTE STATISTICS;'
FROM dba_tables
WHERE owner Not IN ('SYS', 'SYSTEM')
ORDER BY owner
/

PROMPT
PROMPT SPOOL OFF
PROMPT SET ECHO OFF
PROMPT

SPOOL OFF
CLEAR COLUMN
SET PAGES 24 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON TERMOUT ON

@analyze.tmp

PROMPT
PROMPT ****************************************************************
PROMPT
PROMPT Output saved at analyze.res
PROMPT

Collapse
Posted by Sebastiano Pilla on
Have a look at the dbms_stats PL/SQL package: as far as I know, Oracle nowadays recommends using dbms_stats rather than analyze.
Collapse
Posted by Andrew Piskorski on
It is easy, all you need is "dbms_stats.gather_schema_stats". E.g., take this proc and schedule it periodically (once per night or once per week) in AOLserver:
ad_proc dtk_analyze_tables {{percent 20}} {
   Analyze all tables.  The Monitoring package has a more
   sophisticated system for analyzing tables, but that particular feature
   is overly complicated for our needs, and may or may not work, so we
   simply run this proc once a day with ns_schedule_daily instead.
} {
   set db_user [ns_config "ns/db/pool/main" {User}]
   db_exec_plsql dtk_analyze_tables {
      begin dbms_stats.gather_schema_stats (:db_user, :percent, cascade => true); end;
   }
}

To do the scheduling use something like this:

ad_schedule_proc -thread t -schedule_proc ns_schedule_daily [list 04 00] dtk_analyze_tables