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
);
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);
