When we last gathered stats in Oracle 11G

select * from dba_optstat_operations;

To see the current retention value,

select dbms_stats.get_stats_history_retention from dual;

BIND VARIABLE PEEKING

There are 3 things that might put you at risk of unstable plans due to bind variable peeking.
1. Histograms: The histogram tells Oracle about data distribution
2. Partitions,
3. Range-based predicates

select column_name, endpoint_number, endpoint_value
from dba_tab_histograms
where table_name='TEST_INVOICE';

Just get rid of any histogram that does nothing but messes up your execution plans.


exec dbms_stats.gather_table_stats('table_owner', 'test_invoice', method_opt => 'for columns invoice_id size 1', cascade => true);

shrink the undo tablespace to free up some disk space after some unusual database activity

1. define a new temporary undo tablespace
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/oradata/xxxx/undotbs201.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;
2. issue the alter system command to point to the temporary tablespace
alter system set undo_tablespace = UNDOTBS2 ;
3. drop the original undo tablespace including datafile
drop tablespace "UNDOTBS" INCLUDING CONTENTS AND DATAFILES ;
4. recreate the original one using the original name ( optional and size according to your needs )
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE '/u01/oradata/xxxx/undotbs01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M ;
5. Repeat step 2 to switch back to the original named tablespace
6. drop the tablespace created in step 1 using the syntax in step 3