Posts

Showing posts from May, 2012

Oracle

Flushing caches for testing:
ALTER SYSTEM FLUSH BUFFER_CACHE;alter system flush shared_pool;Finding unusable, invalid, and disabled indexes:
select owner, index_name from dba_indexes where status='UNUSABLE';select index_name, status from dba_indexes where status = 'DISABLED';select owner,table_name,index_name,last_analyzed,status from dba_indexes where status != 'VALID';Rebuild index:
alter index XXX.XXX rebuild;
  BEGIN
    FOR i IN
      (SELECT index_name 
       FROM dba_indexes
       WHERE status != 'VALID' 
                       AND table_name='')
    LOOP
      EXECUTE immediate 'alter index ' || i.index_name || 'rebuild';
    END LOOP;
  END;
  /

Compute schema statistics:

Auto is superior to estimate
Use "cascade => TRUE" to also update all index stats on the table. EXEC DBMS_STATS.gather_table_stats('schemaName', 'tableName', cascade => TRUE, estimate_percent => dbms_stats.auto_sample_size);EXEC DBMS_…