System Statistics

What is system statistics ?

System statistics describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer.

Why gather SYSTEM STATISTICS ?

When choosing an execution plan, then opitmizer estimate the I/O and CPU resources required for each query.
System statistics enable the query optimizer to more accurately estimate I/O and CPU costs, enabling the query optimizer to choose a better execution plan.

It is important to gather system statistics ?

System statistics give accurate cost for sql query so optimizer take good decision.
If you have good statistics then query take good decision and database performance is increase.

Many Oracle Guru's and Oracle Corp. Also recommended to gather system statistics.

How Gather System Statistics ?

Through DBMS_STATS package we can gather system statistics

Step1
SQL> exec dbms_stats.gather_system_stats('Start');

Step2
SQL>--Wait for some time ...it will 1 hr minimum or 2 hr or whole day according database load.

Step3
SQL exec dbms_stats.gather_system_stats('Stop');


Keep in mind.
1. User must granted DBA privilege role for gather system statistics.
2. After gather system statistics... Unlike table, index, or column statistics, Oracle does not invalidate already parsed SQL statements when system statistics get updated. All new SQL statements are parsed using new statistics.
3. Always Gather System statistics During Heavy Peak Load.


Where check gather system statistics data information ?

After Gather system statictics query sys.aux_stats$ view.

SQL> select pname, pval1 from sys.aux_stats$;

PNAME PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS 1
CPUSPEEDNW 904.86697
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM .995
MREADTIM 1.701
CPUSPEED 1268
MBRC 16
MAXTHR
SLAVETHR

13 rows selected.

Note:
CPUSPEEDNW,IOSEEKTIM,IOTFRSPEED is New column comes with Oracle 10g. and these columns already populated before gather system statistics.


SREADTIM

Single block read time is the average time to read a single block randomly.

MREADTIM

Multiblock read is the average time to read a multiblock sequentially.

MBRC

Multiblock count is the average multiblock read count sequentially.