Gather Stats - Oracle

select object_name & owner for gathering stats at database level ,


this query will list you the object_name, stats status and Last analyzed details


select TABLE_NAME, OBJECT_TYPE, LAST_ANALYZED, USER_STATS, STALE_STATS from dba_tab_statistics where table_name in ('TABLE_1','TABLE_2');

after collecting information you can execute below given details , it will list you the exact syntax with owner / table details Just execute the syntax and it will gather stats on Database level.


select 'exec dbms_stats.gather_table_stats(ownname=>'||''''||owner||''''||',tabname=>'||''''||table_name||''''||',estimate_percent=>20, cascade=>true,degree=>8,method_opt=>'||'''FOR ALL COLUMNS size 250'''||');'
from dba_tables where owner = 'OWNER' and table_name IN (''TABLE_1','TABLE_2');



for plan has value use :


select SQL_ID, PLAN_HASH_VALUE, OBJECT_NAME, COST, timestamp from DBA_HIST_SQL_PLAN where SQL_ID='YOUR_SQL_ID';