Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance impact of MONITORING and GATHER_STALE

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US