RE: Performance impact of MONITORING and GATHER

From: Leng Kaing <Leng.Kaing_at_hsntech.com>
Date: Fri, 18 Mar 2005 16:08:36 +1100
Message-ID: <18D551B1B928FF47A65B2D91F705906A0123E42E@HSNDON-EX01.hsntech.int>

Hello again, everyone,

Thanks very much for all your responses. Very happy to see that I can always rely on the folks on this list to help me out if all else fails. Much appreciated.

Leng.

Leng Kaing
Hansen Technologies
2 Frederick St; Doncaster VIC 3108
=20
Tel: +61-3-9840-3832
=20
=20
-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com]=20 Sent: Thursday, 17 March 2005 6:01 PM
To: Leng Kaing
Cc: oracle-l_at_freelists.org
Subject: RE: Performance impact of MONITORING and GATHER_STALE

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:

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 Fri Mar 18 2005 - 00:13:30 CST