ORACLE GATHER STATS QUERY

We run gather stats query to imporve query performance on a table.

Below is the Gather stats query for multiple tables:

DECLARE
  sql_stmt VARCHAR2(500);
BEGIN
  FOR rec IN
  (SELECT DISTINCT INDEX_NAME FROM ALL_IND_STATISTICS WHERE OWNER='schema_name' AND STALE_STATS = 'YES'
  AND TABLE_NAME LIKE 'table_name'
  )
  LOOP
    sql_stmt := 'begin dbms_stats.gather_index_stats (ownname=>'||'''schema_name'''||',indname =>'||''''||rec.INDEX_NAME||'''' || ',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>64); end;';
    dbms_output.put_line (sql_stmt);
    execute immediate sql_stmt;
  END LOOP;
END;

Gather stats query for partitioned Table:

exec dbms_stats.gather_table_stats ('schema_name','Table_name',CASCADE=>TRUE,granularity=>'ALL',estimate_percent=>dbms_stats.auto_sample_size, degree=>15) ;

Gather stats query for normal Table:

execute dbms_stats.Gather_table_stats(ownname=>'schema_name',tabname=>'Table_name', estimate_percent=>dbms_stats.auto_sample_size, CASCADE=>true, degree=>15);  normal table

replace the table owner(schema_name) and table name in the above queries.
----------------------------------------------------------------------------------------------