DBMS_STATS.gather_table_stats

Question: when do I use dbms_stats.gather_table_stats overdbms_stats.gather_schema_stats?  How do I analyze a single table withdbms_stats.gather_table_stats?  When I analyze withdbms_stats.gather_table_stats, are the associated indexes also analyzed?

Answer:  Unlike dbms_stats.gather_schema_stats, which analyzes all tables and indexes within a schema, dbms_stats.gather_table_stats is used to analyze a single table.

          EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SMART' , tabname => 'AGENT',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

When analyzing a single table, you must also remember to analyze all associated indexes using dbms_stats.gather_index_stats:

exec dbms_stats.gather_table_stats(null, 'CHANGELOG', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

exec dbms_stats.gather_index_stats(null, 'IDX_PCTREE_PARENTID', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

different ways to execute the above command,
First Approach:
create or replace procedure dev.gatherStats IS
begin
SYS.dbms_stats.gather_table_stats('DEV',  'EFFECTIVE_DATE_COMPARISION',CAScade => TRUE);
end;

exec dev.gatherStats

Second Approach:
EXEC SYS.dbms_stats.gather_table_stats('DEV',  'EFFECTIVE_DATE_COMPARISION',CAScade => TRUE);

Third Approach:(To gather stats on table present in a different schema from current schema where procedure is created):

To gather statistics on an object in another schema you need the ANALYZE ANY system privilege. Event though the user that runs procedure has that privilege, if the privilege is granted through a role it will still give an error as "insufficient privileges". As the documentation says:

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights.

You can either GRANT ANALYZE ANY directly to your user, or create the procedure with invoker's rights, as:

CREATE OR REPLACE PROCEDURE dev.gatherStats
AUTHID CURRENT_USER IS
BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;
/

When you EXEC the DBMS_STATS procedure directly, it's running as an anonymous block, and those always run with invoker's rights - honouring roles.

If you want the procedure to be able to be ran by a user without the ANALYSE ANY role then you can set the AUTHID to be DEFINER

CREATE OR REPLACE PROCEDURE dev.gatherStats
AUTHID DEFINER IS
BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;