Objectives of Tuning the System:
The objectives of tuning a system can be either anyone of the two below.
1)To reduce the response time for end users of the system. Take it as a special care if your database response time is not optimal.
2)To reduce the resources used to process the same work. Take it as special care if you have limited hardware resource.
We can achieve both our objectives in three ways.
A)Reduce the Workload: SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.
B) Balance the Workload: Systems often tend to have peak usage in the daytime when real users are connected to the system, and low usage in the nighttime. If noncritical reports and batch jobs can be scheduled to run in the nighttime and their concurrency during day time reduced, then it frees up resources for the more critical programs in the day.
C) Parallelize the Workload: Queries that access large amounts of data (typical data warehouse queries) often can be parallelized. This is extremely useful for reducing the response time in low concurrency data warehouse. However, for OLTP environments, which tend to be high concurrency, this can adversely impact other users by increasing the overall resource usage of the program.