Gather stats database

set lines 100
col operation form a40 wrap head 'operation(on)'
col target form a1
spool show_auto_stat_runs.lst
select operation||decode(target,null,null,'-'||target) operation
      ,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time
      ,to_char(  end_time,'YYMMDD HH24:MI:SS.FF4') end_time
from dba_optstat_operations
order by start_time desc
/




EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_dictionary_stats;



set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where object_name in ('&object_name') and owner='&owner' order by object_type, object_name desc
/


To Gathers statistics for all objects in a schema

SQL> select owner, table_name,last_analyzed from dba_tables where owner='SCOTT' order by last_analyzed;

SQL> select ocwner, index_name, last_analyzed from dba_indexes where owner='SCOTT' order by last_analyzed;

SQL> exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', options => 'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL> select owner, table_name,last_analyzed from dba_tables where owner='SCOTT' order by last_analyzed;

SQL> select owner, index_name, last_analyzed from dba_indexes where owner='SCOTT' order by last_analyzed;


select client_name, status from dba_autotask_operation;

CLIENT_NAME                                      STATUS
---------------------------------------------- ------------
auto optimizer stats collection                ENABLED
auto space advisor                             ENABLED
sql tuning advisor                             ENABLED



missing index



select * from (
  select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and   us.name='&SCHEMA_NAME'
  and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;


To Gather statistics for tables in a schema

SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname => 'EMP',estimate_percent=> 100,cascade => true);


SELECT * FROM dba_autotask_schedule;



select m.TABLE_OWNER,
 m.TABLE_NAME,
 m.INSERTS,
 m.UPDATES,
 m.DELETES,
 m.TRUNCATED,
 m.TIMESTAMP as LAST_MODIFIED,
 round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
 t.num_rows as last_known_rows_number,
 t.last_analyzed
From dba_tab_modifications m,
 dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;