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

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