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.
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
- MBRC ----> Average blocks read per Multi block read.
- MAXTHR ----> Maximum I/O throughput
- SREADTIM ---->Single block read time in milliseconds
- MREADTIM -----> Multiblock read time in ms
1- Oracle Dcoumentation
2- OTN threads