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 statisticsStep1SQL> exec dbms_stats.gather_system_stats('Start');Step2SQL>--Wait for some time ...it will 1 hr minimum or 2 hr or whole day according database load.Step3SQL 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------------------------------ ----------STATUSDSTARTDSTOPFLAGS 1CPUSPEEDNW 904.86697IOSEEKTIM 10IOTFRSPEED 4096SREADTIM .995MREADTIM 1.701CPUSPEED 1268MBRC 16MAXTHRSLAVETHR13 rows selected.Note: CPUSPEEDNW,IOSEEKTIM,IOTFRSPEED is New column comes with Oracle 10g. and these columns already populated before gather system statistics.SREADTIMSingle block read time is the average time to read a single block randomly.MREADTIMMultiblock read is the average time to read a multiblock sequentially.MBRCMultiblock count is the average multiblock read count sequentially.