Gather System Statistics

This procedure gathers system statistics. Which is Important for Index and Tablespace. it’s very Good to use in SQL performance.

There are different Mode for Gather_system_stat like the following :

• NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS (‘noworkload’) after creation of the database and tablespaces. To fine tune system statistics for the workload use ‘START’ and ‘STOP’ or ‘INTERVAL’ options. If you gather both ‘NOWORKLOAD’ and workload specific (statistics collected using ‘INTERVAL’ or ‘START’ and ‘STOP’ ), the workload statistics will be used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.
   
• INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can use GATHER_SYSTEM_STATS (gathering_mode=>’STOP’) to stop gathering earlier than scheduled. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.
   
• START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim, mbrc.

For Example :

exec dbms_stats.gather_system_stats(‘Start’);exec dbms_stats.gather_system_stats(‘Stop’); 

 After run the system_stat all the information inserted in table called “aux_stats$”  Like the following :

• CPUSPEEDNW —–> CPU speed
• IOSEEKTIM ——>    I/O seek time in milliseconds
• IOTFRSPEED ——> I/O transfer speed in millisecond