How to Gather stats for schema, table and partitions
Check Stats for Schema, table and Partitions 1. Check for Schema select owner, min(last_Analyzed), max(last_analyzed) from dba_tables where owner = '&i' group by owner order by 1; 2. Check for table stats select table_name, last_analyzed from dba_tables where owner='&i' AND TABLE_NAME IN ('&y'); 3. Check for partition stats col table_name for a10 col partition_name for a10 SELECT table_name, partition_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_TAB_PARTITIONS WHERE table_name='&i' AND partition_name like 'TEST2020%' order by partition_name; Gather Stats for Schema 1st Method:- Begin dbms_stats.gather_schema_stats( ownname => '&i', --- schema name options => 'GATHER AUTO', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 24 ); / 2md Method:- EXEC DBMS_STATS.gather_schema_stats (