System Statistics : A Quick Look

1).Till 8i ANALYZE command was used to collect statistics.However ANALYZE has following Problems 

       1).Doesn't support External Tables.
       2).Not good for partitioned Table. As it gathers stats for only segment and index level.

2).From 9i Its recommended to use dbms_stats.With dbms_stats,You can export, import, lock, unlock, get, set, gather and delete at database/dictionary/table/schema/index/system level.

3).Till 8i the CBO's Cost Estimations were based on I/O Cost Model.

4).Single Block and Multi Block reads are equally costly. Though in reality single block reads are faster. This gave unfair advantage to the Full Table Scans.To fix this we used optimizer_index_caching and optimizer_index_cost_adj is used.

5).New Cost model was introduced in 9i. Known as "CPU Cost Model".Wherein it gets performance of CPU and I/O Subsystem.

6).9i has no System Stats were available by default.10g has.

7).There are 2 types of System Statistics

        1).No workload Stats(Artificial Application Load).

        2).Workload Stats(Real Application Load) 

8).The main difference is method used to measure I/O Performance.You can still use I/O Cost Model in 10g by using _optimizer_cost_model to IO

9).System Stats are stored in AUX_STATS$ table. 
 
   SQL>SELECT pname, pval1, pval2   FROM sys.aux_stats$  WHERE sname = 'SYSSTATS_INFO';
 
       For Pname – Status can be completed / badstat 

10).SYSSTATS_MAIN set contains system statistics themselves.
    
   SQL>SELECT pname, pval1   FROM sys.aux_stats$  WHERE sname = 'SYSSTATS_MAIN';
 
11).Single set of statistics per database.So if a RAC machine has different configuration.Need to decide whether you need enable system statistics.

12).You Gather system statistics using below command,
 
   SQL>dbms_stats.gather_system_stats(gathering_mode => 'noworkload');

13).When using “noworkload” make sure the system is idle. As database uses synthetic benchmark. In 10g even if you delete the system statistics. Database will gather the statistics during next start-up.

14).Gathering statistics usually takes less than one minute.

15).With workload Statistcs,you use 

         1).exec dbms_stats.gather_system_stats(gathering_mode => 'start')
         2).Do actual work.  
         3).dbms_stats.gather_system_stats(gathering_mode => 'stop')

16).To avoid manually taking the ending snapshot, it is also possible to set the parameter gathering_mode

   SQL> dbms_stats.gather_system_stats(gathering_mode => 'interval', interval => 30)

17).You can also manually the set the statistics. This is helpful if you have different workload for in different period and you want to average the values.Say for example, 

   SQL>dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 772);

18).The CPU cost is defined as number of single-block reads that can be performed per second.

19).Workload statistics are preferred to “noworkload” when both of them are available.

20).Query optimizer performs several sanity checks that could disable or partially replace workload statistics.For example if mreadtim is less than sreadtime CBO re-computes again.

Courtesy: Troubleshooting Oracle Performance by Christian Antognini.