How to check Database Maintenance and Setup Oracle?

Below are selects to check database(DB) maintenance status.

1.

​​​​​[SELECT t.table_name, 
         t.last_analyzed, 
         t.degree, 
         t.num_rows,
         t.sample_size, 
         t.avg_row_len, 
         ROUND ( (t.num_rows * t.avg_row_len) / (1024 * 1024 * 1024), 1) 
            "Required Gb", 
         ROUND ( (s.blocks * (select value from v$parameter where name = 'db_block_size')) / (1024 * 1024 * 1024), 1)"Allocated Gb"  
    FROM user_tables t, user_segments s 
   WHERE   t.table_name = s.segment_name 
ORDER BY 8 desc]

• Statistics should be gathered on all tables daily
• If the Degree column does not =1 then parallelism is being used. 
  • Use of parallelism found to cause performance problems
  • Have DBA set degree in the object to nonparallel (1)
• Re-Organization should be considered for those with large discrepancy between allocated and used GB
• Re-Organizaiton especially important where full scans are performed on the object
• The t.sample_size indicates the number of rows that were used for stats calculation.  The sample size should equal the num_rows to confirm 100% sampling was used.

2.

[select DBMS_STATS.GET_PARAM('AUTOSTATS_TARGET') from dual]

Oracle DBMS_STATS parameter should be set to ORACLE not AUTO

• With DBMS_STATS set to ORACLE, setup separate database maintenance job to gather statistics on all tables and cascade to indexes daily.
• If dbms_stats is set to AUTO, oracle will make its own assessment of statistics parameters which have been less than optimal for WMS.

--Change to 'ORACLE'
exec dbms_stats.set_param('AUTOSTATS_TARGET','ORACLE');

--Verify
select dbms_stats.get_param ('AUTOSTATS_TARGET') from dual;

3. 

[Select table_name, column_name, count(*)
     From user_histograms
     Group by table_name, column_name
     Having count(*) > 2]

If multiple rows returned, the database is setup to use histograms. 

• Oracle 11 and prior, use of histograms should be disabled and old histograms should be deleted.  In systems running Oracle 11 and prior, intermittent  system-wide locking can occur when using histograms

4.

[select index_name, last_analyzed, last_ddl_time , degree 
from user_indexes i, user_objects o
where i.index_name = o.object_name
and i.index_type not like '%LOB%']

NOTE: Any changes considered should be fully tested in a TEST instance to verify changes are done correctly and expected results are achieved.

Tables with a high number of inserts or deletes may need to have indexes and statistics maintained more often.