gathering statistics on Oracle DB

Here comes a script to gather statistics on Oracle DB

begin

  dbms_stats.unlock_schema_stats('SCHEMA');
  dbms_stats.delete_schema_stats('SCHEMA');
  dbms_stats.gather_schema_stats(ownname       => 'SCHEMA',
                                estimate_percent => 40.0,
                                granularity      => 'GLOBAL',
                                block_sample     => TRUE,
                                cascade          => TRUE,
                                degree           => DBMS_STATS.DEFAULT_DEGREE,
                                method_opt       => 'for all columns size 1');

  dbms_stats.lock_schema_stats('SCHEMA');

end;



select t.owner, t.table_name, t.last_analyzed from dba_all_tables t
where lower(t.owner) = 'your_schema';

foreign keys without indexes

In Oracle database a foreign keys without an index defined on it could lead to contention problems (enq: TM – contention wait event). The following script retrieves foreign keys not having indexes

--the script finds the missing indexes on FK

SELECT * FROM (
   SELECT c.table_name, cc.column_name, cc.position column_position
   FROM   user_constraints c, user_cons_columns cc
   WHERE  c.constraint_name = cc.constraint_name
   AND    c.constraint_type = 'R'
   MINUS
   SELECT i.table_name, ic.column_name, ic.column_position
   FROM   user_indexes i, user_ind_columns ic
   WHERE  i.index_name = ic.index_name
   )
ORDER BY table_name, column_position;