Collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.

# Fixed objects stats 

1. Check fixed object stats 

             select count(1) from tab_stats$; 

2. Gather fixed objects stats

           exec dbms_stats.gather_fixed_objects_stats;

3. Check fixed object stats 
              select count(1) from tab_stats$; 


# Gather system stat  

execute dbms_stats.gather_system_stats('Start');
-- few hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

# Flush shared pool

Alter system flush shared_pool;

 Roll back if the change is not successful:


1. exec dbms_stats.delete_fixed_objects_stats();
2. exec DBMS_STATS.DELETE_SYSTEM_STATS;

---------------------------------------------------------------------------
http://www.dba-oracle.com/t_dbms_stats_gather_system_stats.htm

The dbms_stats.gather_system_stats procedure is especially useful for multi-mode Oracle shops that run OLTP during the day and DSS at night.  You invoke the dbms_stats.gather_system_stats procedure as an elapsed time capture, making sure to collect the statistics during a representative heavy workload:

execute dbms_stats.gather_system_stats('Start');
-- one hour delay during high workload
execute dbms_stats.gather_system_stats('Stop');

The data collection mechanism of the dbms_stats.gather_system_stats procedure works in a similar fashion to my script that measures I/O times to optimizer the optimizer_index_cost_adj parameter.  The dbms_stats.gather_system_stats also related to the under-documented  _optimizer_cost_model parameter and your db_file_multiblock_read_count setting.

The output from dbms_stats.gather_system_stats is stored in the aux_stats$ table and you can query it as follows:

select pname, pval1 from sys.aux_stats$;

Here are the data items collected by dbms_stats.gather_system_stats:

No Workload (NW) stats:

CPUSPEEDNW - CPU speed
IOSEEKTIM - The I/O seek time in milliseconds
IOTFRSPEED - I/O transfer speed in milliseconds
Workload-related stats:

SREADTIM  - Single block read time in milliseconds
MREADTIM - Multiblock read time in ms
CPUSPEED - CPU speed
MBRC - Average blocks read per multiblock read (see db_file_multiblock_read_count)
MAXTHR - Maximum I/O throughput (for OPQ only)
SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)   
The dbms_stats.gather_system_stats procedure is very similar to my script for setting optimizer_index_cost_adj, where I compare the relative costs of sequential and scattered read times:

select 
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1, 
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) / 
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
   (
      sum(b.total_waits) / 
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) / 
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5 
from 
   dba_hist_system_event a, 
   dba_hist_system_event b
where 
   a.snap_id = b.snap_id
and 
   a.event_name = 'db file scattered read'
and 
   b.event_name = 'db file sequential read';


SELECT module, sql_text, username, disk_reads_per_exec, buffer_gets,   
       disk_reads, parse_calls, sorts, executions, rows_processed, hit_ratio,   
       first_load_time, last_load_time, sharable_mem, persistent_mem,  runtime_mem, cpu_time, cpu_time/1000 as cputime_per_execution,   
       elapsed_time
FROM    
    (SELECT module, sql_text ,     
            u.username,
            round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec,
            s.disk_reads, s.buffer_gets, s.parse_calls, s.sorts, s.executions ,
            s.rows_processed,     
            100 - round(100 *  s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio,
            s.first_load_time ,  s.last_load_time,   sharable_mem ,     persistent_mem ,     runtime_mem, 
            cpu_time,     elapsed_time,     address,     hash_value   
     FROM   sys.v_$sql s, sys.all_users u    
     WHERE  s.parsing_user_id=u.user_id
     --and module = 'JDBC Thin Client'
        and UPPER(u.username) like '%E%' 


set lines 1000 pages 50000
set markup html on
spool active.html



SELECT *
FROM   (SELECT Substr(a.sql_text,1,50) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, 
               a.buffer_gets, 
               a.disk_reads, 
               a.executions, 
               a.sorts,
               a.address
        FROM   v$sqlarea a
        ORDER BY 2 DESC)
WHERE  rownum <= &&1;

SELECT module, sql_text, username, disk_reads_per_exec, buffer_gets, 
 disk_reads, parse_calls, sorts, executions, rows_processed, hit_ratio, 
 first_load_time, last_load_time, sharable_mem, persistent_mem, runtime_mem, cpu_time, cpu_time/1000 as cputime_per_execution, 
 elapsed_time 
 FROM 
 (SELECT module, sql_text , 
 u.username, 
 round((s.disk_reads/decode(s.executions,0,1, s.executions)),2) disk_reads_per_exec, 
 s.disk_reads, s.buffer_gets, s.parse_calls, s.sorts, s.executions , 
 s.rows_processed, 
 100 - round(100 * s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio, 
 s.first_load_time , s.last_load_time, sharable_mem , persistent_mem , runtime_mem, 
 cpu_time, elapsed_time, address, hash_value 
 FROM sys.v_$sql s, sys.all_users u 
 WHERE s.parsing_user_id=u.user_id 
 --and module = 'JDBC Thin Client' 
 and UPPER(u.username) like '%E%' 
 ORDER BY cpu_time desc, last_load_time desc) WHERE rownum <= 50; 

--------------------------------------------------------------------------------------


Collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. 

 exec dbms_stats.gather_fixed_objects_stats;

 start the system start gathering

 EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

 select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

 The stop the gathering after  2 to 3 days
  
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

set lines 180 pages 1000
col client_name for a40
col attributes for a60
select client_name, status,attributes,service_name from dba_autotask_client
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/

select client_name, status,attributes,service_name from dba_autotask_client
/

## Enabling
BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
/