RE: how to influence the threshold for optimizer statistics to become stale
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <oradbt054@xxxxxxxxx>, <lutz.hartmann@xxxxxxxxx>
- Date: Thu, 24 May 2007 07:07:28 +0200
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
