Question

1. What is database tuning? Explain some of the tuning methods used by DBAs to improve performance.
2. What types of things affect performance of a database? Explain how those things are identified and managed.
3. Explain database statistics and why they are important. How do you obtain database statistics?

Answer 1

Database tuning:

It refers to different activities that help in optimizing the database performance by homogenizing different tasks. The focus here is to maximize the system resource use and make the work be done in an efficient manner and rapidly.

Methods for database tuning:

1. Statistics collection:

This one is a very common and most important way to tune databases. In this approach statistics are generated by estimating the cost of plan for execution. All the activities are performed by DBA on a regular basis using the data about schema objects.

2. Optimization:

This method is used with queries. The execution decision for queries is made as per the data gathered from statistics. The optimizer helps in determining the optimal plan in the given environment for statements.

3. Index management:

It is important to know if index is used or full scan selection is used. Indexing improves database performance but it is important to understand if the right kind of index has been used or not. The indexes have been used to improve the overall performance tremendously.

Things that affect database's performance and their management:

1. Workload:

The workload that has been requested from the database helps in defining the demands from it. It includes batch jobs, online transactions, temporary queries, etc. There are also certain utilities that affect the workload. It also fluctuates from time to time and can be predicted based on the monthly or weekly processing of data.

2. Throughput:

It refers to the overall capability to process data by the system. For a database to have good throughput, it is essential that the operating system and processor work well. It includes I/O speed, parallel capabilities, CPU speed, etc. It can't be based on predictions or assumptions, it depends on the capacity figures.

3. Resources:

The resources refer to the software and hardware devices and tools used with the database. It can also be disk, microcode, memory, cache controllers, etc. It affects the optimization and performance of the database. One can ensure the best performance by making sure that resources are up to date.

4. Contention:

It occurs when the workload is more. It is the condition where attempts are made to access one resource by many components. Due to the conflict in the access capabilities the workload is affected as well. To prevent it one can use a locking mechanism for the database queries. When one resource is used by one component, it can be locked so that it is not used by any other.

Database statistics:

These are used for analysis purposes. It is also used in online analytical processing. It is all about collecting data regarding database objects. It can be tables, processors, indexes, processor speed, space, memory available, etc. It helps in understanding the system performance and how it can be optimized.

Importance of database statistics:

There are so many decisions made by DBA and the database itself as per the statistics. Cost based scenarios and optimizers make use of a lot of information about database objects to arrive at a specific decision. These statistics help in designing algorithms and offering all kinds of best options for the queries.

Ways to obtain database statistics:

Database statistics can be obtained by DBA manually or by DBMS automatically. There are many DBMS vendors that help in analysing SQL commands and collect statistics. They offer their own routines to find statistics. Some make use of surveys, observations, experiments, etc. It can also be obtained by focus groups, interviews, and case studies.