RE: how to influence the threshold for optimizer statistics to become stale

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Thu, 24 May 2007 07:07:28 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAFB2399A@MSXVS04.trivadis.com>

> I thought to calculate the percent for the table I wanted
> to have a different threshhold.

Thanks David to remember me that 4-5 years ago, with an Oracle8i DB, I also wrote something like that. My query also included truncated tables and tables without 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 = ut.table_name
  GROUP BY ut.table_name
  UNION ALL
  SELECT table_name, 1 val -- tables modified via TRUNCATE   FROM user_tab_modifications
  WHERE truncated = 'YES'
  UNION ALL
  SELECT table_name, 1 val -- tables without statistics   FROM user_tables
  WHERE num_rows IS NULL
  )
WHERE val <= 5

Cheers,
Chris

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

Received on Thu May 24 2007 - 00:07:28 CDT