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 (ownname => '&i', cascade =>true, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => '8',method_opt => 'FOR ALL COLUMNS SIZE AUTO', force=>TRUE );

Easy way
exec dbms_stats.gather_schema_stats( ownname => '&i',degree => 8);exec fnd_stats.gather_schema_stats('owner',degree=>8)

Gather Stats for Table

1st Method:===========
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => '&x',
tabname => '&y',
cascade => true, ---- For collecting stats for respective indexes
method_opt=>'for all indexed columns size 1',  -- if no histogram for index
granularity => 'ALL',
estimate_percent =>dbms_stats.auto_sample_size,
degree => 8);
END;
/

2nd Method:-

execute dbms_stats.gather_table_stats(ownname => '&i', tabname =>'&y', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree=> 4, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Easy way
exec dbms_stats.gather_table_stats('&i','&j');exec fnd_stats.gather_table_stats('owner','table_name',percent=> 30,cascade=>true);

Gather Stats for Partitions

1st Method:============
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => '&i',
tabname => '&j', --- TABLE NAME
partname => '&k' --- PARTITOIN NAME
method_opt=>'for all indexed columns size 1',
GRANULARITY => 'APPROX_GLOBAL AND PARTITION',
degree => 8);
END;
/

Second Method:

execute dbms_stats.gather_table_stats(ownname => '&i', tabname =>'&y', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree=> 4,partname='&k' ,method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Easy way:=========

execute fnd_stats.gather_table_stats('owner','TABLE_NA<E',percent=> 30,cascade => TRUE,PARTNAME=>'&i');