Gather statistics for table,index and schema

Statistics Types:-

1) System statistics :- 

exec dbms_stats.gather_system_stats('Start');

select * from sys.aux_stats$;

2) Optimizer statistics 

Below are the Optimizer Statistics ( tables, indexes and Schema):-

 -- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for a specific table

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname     => 'schema_name',
  tabname     => 'table_name',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade     => TRUE
);

-- Execute the following query to view statistics for a specific table

SELECT *

FROM DBA_TABLES

WHERE OWNER = upper('&schema_name')

AND TABLE_NAME = upper('&table_name');

------------------- Stats for Index -----------------------

-- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for a specific index

EXEC DBMS_STATS.GATHER_INDEX_STATS(

  ownname     => 'schema_name',

  indname     => 'index_name',

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

);

-- Execute the following query to view statistics for a specific index

SELECT *

FROM DBA_INDEXES

WHERE OWNER = upper('&schema_name')

AND INDEX_NAME = upper('&index_name');

-------------------------------------  Stats for SCHEMA ---------------------------

-- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for all objects in a schema

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(

  ownname     => 'schema_name',

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

  cascade     => TRUE

);

-- Execute the following query to view statistics for all objects in a schema

SELECT *

FROM DBA_TAB_STATISTICS

WHERE OWNER = upper('&schema_name');

----- Optimizer Statistics ----------

exec dbms_stats.gather_database_stats;

exec dbms_stats.gather.dictionary_stats;

***************************************************

select table_name from dba_tables where table_name in ('TABLE1','TABLE2');

select owner,segment_name  from dba_segments where segment_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5');

select * from dba_dependencies where name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5'); --- TABLE1,TABLE2,TABLE3,TABLE4,TABLE5

select (select host_name from v$instance),(select instance_name from v$instance),table_name, count(index_name) "indexes_count" from dba_indexes where table_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5') group by table_name;

--- READY MADE QUERYIES 

set serverout on;

begin 

for x in (select owner,segment_name  from dba_segments where segment_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5''))

loop

dbms_output.put_line('EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> '''||x.owner||''''||','||' tabname=>'||''''||x.segment_name||''''||','||'estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);');

end loop;

end ;

/

--EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE1',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE2',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE3',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE4    ',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);