RE: Performance impact of MONITORING and GATHER_STALE

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 17 Mar 2005 08:00:58 +0100
Message-ID: <2CF83791A616BB4DA203FFD13007824A021440AE@MSXVS02.trivadis.com>

Hi Leng

>Thanks for the clarification! Ok, now back on track. Yes, agreed. 10% =
is
>not necessarily the best rule of thumb but it's better than nothing I
>guess.

If you don't like the 10% you could try something like this:

1. create a view that returns the tables with stale (according to your = whishes) statistics

CREATE OR REPLACE VIEW stale_statistics AS SELECT DISTINCT table_name
FROM (

  SELECT ut.table_name,         -- tables modified via DML statements
         sum(ut.num_rows)/sum(utm.inserts+utm.updates+utm.deletes) val

  FROM user_tab_modifications utm, user_tables ut   WHERE utm.table_name =3D ut.table_name   GROUP BY ut.table_name
  UNION ALL
  SELECT table_name, 1 val -- tables modified via TRUNCATE=20   FROM user_tab_modifications
  WHERE truncated =3D 'YES'
  UNION ALL
  SELECT table_name, 1 val -- tables without statistics   FROM user_tables
  WHERE num_rows IS NULL
  )
WHERE <add condition on val here>

2) gather statistics based on the tables returned by the view

BEGIN
  FOR tablist IN (SELECT * FROM stale_statistics) LOOP     DBMS_STATS.GATHER_TABLE_STATS(

      ownname          =3D> user,=20
      tabname          =3D> tablist.table_name,
      estimate_percent =3D> 1,
      cascade          =3D> TRUE);

  END LOOP;
END; HTH
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 17 2005 - 02:07:18 CST