Oracle tuning / analyze tables

Question

What are the means to schedule automatic "analyze tables". Is it possible to request an automatic "analyze tables" when a lot of data change througt insert and deletes? What are the means to parametrize the automatic analyze tables process, i.e. to set rules when it should be triggered.

Answer 1

What version of Oracle are you using? From 10.1 on, Oracle has shipped with an automatic job that gathers statistics every night on any object that has been changed substantially (based on an internal algorithm subject to change, but I believe the threshold is ~15%) since that last time statistics were gathered. Assuming you are using a currently supported release, statistics are gathered automatically by default unless you explicitly disable that job.

You can use the DBMS_STATS package to gather statistics on an object based on an event and you can use the DBMS_JOB (or DBMS_SCHEDULER) package to have that done asynchronously. So at the end of an ETL process, you can gather statistics

BEGIN
  dbms_stats.gather_table_stats( 
    ownname => <<schema name>>,
    tabname => <<table name>> 
  );
END;

You can have that run asynchronously

DECLARE
  l_jobno INTEGER;
BEGIN
  dbms_job.submit(
    l_jobno,
    'BEGIN dbms_stats.gather_table_stats( ''<<schema name>>'', ''<<table name>>'' ); END;',
    sysdate + interval '1' minute
  );
END;

Answer 2

"Is there a way to have this job being triggered when there is a substantial proportion of data changed" In theory you could do an AFTER INSERT trigger on a table that automatically sets off DBMS_STATS.

But be careful what you wish for. If you are in the middle of a large ETL job, having just inserted a million rows into the table, you don't necessarily want to automatically immediately set off a DBMS_STATS job. Equally, when you are at your busiest on-line processing time (eg lunch) you don't want to slow it down by gathering stats at the same time.

You generally want these stats gathering jobs running at low-usage periods (nights, weekends). If you have a large batch run that loads into a lot of tables, I'd build the DBMS_STATS into the batch.