SQL Tuning : Gather statistics

export_fixed_objects_stats
import_fixed_objects_stats

Workload Statistics
multiblock reads ( mreadtim)
sequential read I/O time ( seriatim)
Both are updated by dbms_stats.gather_system.stats procedure

Noworkload Statistics : cpuspeednw, ioseektim, and iofrspeed
Workload Statistics : sreadtim, mreadtim, cpuspeed, mbrc, maxthr and slavethr updated by dbms_stats.gather_system.stats procedure
If Both are available CBO will used Workload Statistics

select
 sname,
 pname,
pvall
from
 sys.aux_stats$

 sreadtim : Single block read time in millisecond
 mreadtim : Multiple block read time in millisecond
 cpuspeed : CPU speed
 mbrc : Average block read per multiblock read
 maxthr :  Maximum I/O throughput
 slavethr : Slave throughput


dbms_stats.gather_system.stats procedure  is important to choose TFS vs Index scan

execute bms_stats.gather_system.stats( start)
-- one hour delay during high worklaod
execute bms_stats.gather_system.stats( stop)

or
dbms_stats.gather_system_stats('INTERVAL', interval=60)