quick guide to statistics and individual generation for oracle database

This is a quick way to restore some performance equivalence between environments, or after some heavy data loads.  I was having a report (integrity) finish in 9 minutes in PY and then 45 minutes in PD (actually 3 different integrities were slower in PD).  PD had a faster database, more memory, more RAM, what was going on.  Data was identical – it was a copy.

It all came down to stats, but also interestingly when the DBA ran their standard stat’s generation – it went slow again…  weird.

I ran the following for the table

begin 
DBMS_STATS.GATHER_TABLE_STATS (
ownname => '"PDFINDTA"',
tabname => '"F0911"',
estimate_percent => 10
);
end;

Then executed this for the indexes.

select 'ALTER INDEX "PDFINDTA"."' || index_name || '" compute statistics;' 
from all_indexes where table_name = 'F0911' and owner = 'PDFINDTA' ;

Then ran all of the results of the above, which looked somewhat like below:

alter index "PDFINDTA"."F0011_0" compute statistics