DB versions : 12.1, 19c
Question1. The ideal way to generate histograms in a table (for 19c and 12.1 )
Is the below command the ideal way to generate histograms in a table ?
begin
dbms_stats.gather_table_stats
(
ownname => 'DTPMS',
tabname => 'CUSTOMER_INFO',
cascade => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
end;
/
90% of the DBs in my shop are already in 19c and the rest are in 12.1 and yet to be migrated to 19c.
Question2. Can we let Oracle generate histograms on all business tables ?
Last week, for an in-house developed application running on 19c PDB, I fixed a performance issue by generating histogram on the column which had skewed data.
So now, the application owners and developers have asked me if it fine to let Oracle generate histograms on all tables in their application schema i.e, run a for loop like below ? Would this be an overkill ? Or are there any downsides in letting Oracle collect histograms in all tables ?
--- Untested code
declare
v_sql varchar2(10000);
begin
for rec in
(select owner, table_name from all_tables where owner='DTPMS' )
loop
begin
v_sql := q'[exec dbms_stats.gather_table_stats(ownname=>'DTPMS',TABNAME=>']'||rec.TABLE_NAME||q'[',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,||q'['method_opt => 'FOR ALL COLUMNS SIZE AUTO']'||q'[', cascade => true);]' ;
dbms_output.put_line(v_sql);
execute immediate v_sql;
exception
when others
then
dbms_output.PUT_LINE (rec.TABLE_NAME||' '||sqlerrm);
end;
end loop;
end;
/