This topic will be of great interest and immense useful to those who are involved in oracle performance tuning.
The methods of Collecting statistics
There are three means by which you can collect statistics required to tune the DB.
They are VIEWS,UTILITIES and TOOLS.
1)DICTIONARY and DYNAMIC views
Oracle server displays all system statistics in the V$SYSSTAT view and uses many other views for performance and troubleshooting information.
You can find culumative totals since the instance started and is often unhelpful.If the instance is often rarely shutdown the statistics may cover a long period and have little meaning.
2)UTLBSTAT and UTLESTAT Utilities
Using this utilities to gather performance figures over a well defined period say during the busiest time of day or end of the Month and produce a report.
Using oracle GUI tools -Diagonostics and Tuning Packs-which addresses many oracle Performance management areas such as Graphical monitoring analysis and automated tuning of oracle Database.
The Steps to Ponder before Using the UTLBSTAT and UTLESTAT
.To gather performace over a period of defined time.
.Produce a Hard Copy report
.Run the script from server manager connected as SYSDBA.
.Set TIMED_STATISTICS to TRUE.
i)What is UTLBSTAT and UTLESTAT stand for ?
Utility to begin statistics and Utility to end statistics.This are basically predefined SQL scripts comes along with oracle .
ii)Where are this Scripts located in the Oracle to execute from SVRMGR ?
iii)What is Report.txt file ?
This is the default file created after executing the UTLESTAT script in the end.
iv)What statistics details does the Report.txt contains ?
.Library cache statistics
.Wait events statistics
.Rollback contention statistics
.Buffer Busy Wait statistics
.Dictionary cache statistics
.I/O statistics per datafile/tablespace
.Period of measurement
v)In which Tablespace should the scripts be executed ?
The script connects as SYSDBA and creates tables in SYS's default TS SYSTEM.Before running the script create a New TS for this purpose and change SYS's default TS to this new one.
Ater finishing running the both the scripts change once again SYS's default TS to SYSTEM.
Steps in gathering statistics
1)While first executing the UTLBSTAT script from the above path already mentioned....
2)The script creates BEGIN and END tables and takes the snapshot of data from the dynamic performance tables(V$xxx) to collect initial statistics and stores these in the BEGIN tables.
Steps in Ending statistics gathering
1)Execute the UTLESTAT as mentioned from the above path.
2)The script takes a new snapshot of data from the dynamic performace tables (V$xxx) to collect final statistics and stores these in the END tables.
3)The script creates DIFFERENCE tables where it stores the values of the substraction of the results of the initial statisctics from the final statistics.
4)The script drops all the temporary views and tables.
5)The script generates a report by selecting the data from the DIFFERENCE tables.
Based on the Report.txt file information you can understand which areas the tuning is required .