What is database tuning? Expla

Database tuning:

Itrefers to different activities that help in optimizing the databaseperformance by homogenizing different tasks. The focus here is tomaximize the system resource use and make the work be done in anefficient manner and rapidly.

Methods for databasetuning:

1. Statisticscollection:

Thisone is a very common and most important way to tune databases. Inthis approach statistics are generated by estimating the cost ofplan for execution. All the activities are performed by DBA on aregular basis using the data about schema objects.

2. Optimization:

Thismethod is used with queries. The execution decision for queries ismade as per the data gathered from statistics. The optimizer helpsin determining the optimal plan in the given environment forstatements.

3. Index management:

It isimportant to know if index is used or full scan selection is used.Indexing improves database performance but it is important tounderstand if the right kind of index has been used or not. Theindexes have been used to improve the overall performancetremendously.

Things that affect database’s performanceand their management:

1. Workload:

Theworkload that has been requested from the database helps indefining the demands from it. It includes batch jobs, onlinetransactions, temporary queries, etc. There are also certainutilities that affect the workload. It also fluctuates from time totime and can be predicted based on the monthly or weekly processingof data.

2. Throughput:

Itrefers to the overall capability to process data by the system. Fora database to have good throughput, it is essential that theoperating system and processor work well. It includes I/O speed,parallel capabilities, CPU speed, etc. It can’t be based onpredictions or assumptions, it depends on the capacityfigures.

3. Resources:

Theresources refer to the software and hardware devices and tools usedwith the database. It can also be disk, microcode, memory, cachecontrollers, etc. It affects the optimization and performance ofthe database. One can ensure the best performance by making surethat resources are up to date.

4. Contention:

Itoccurs when the workload is more. It is the condition whereattempts are made to access one resource by many components. Due tothe conflict in the access capabilities the workload is affected aswell. To prevent it one can use a locking mechanism for thedatabase queries. When one resource is used by one component, itcan be locked so that it is not used by any other.

Database statistics:

Theseare used for analysis purposes. It is also used in onlineanalytical processing. It is all about collecting data regardingdatabase objects. It can be tables, processors, indexes, processorspeed, space, memory available, etc. It helps in understanding thesystem performance and how it can be optimized.

Importance of databasestatistics:

Thereare so many decisions made by DBA and the database itself as perthe statistics. Cost based scenarios and optimizers make use of alot of information about database objects to arrive at a specificdecision. These statistics help in designing algorithms andoffering all kinds of best options for the queries.

Ways to obtain databasestatistics:

Database statistics can be obtained by DBAmanually or by DBMS automatically. There are many DBMS vendors thathelp in analysing SQL commands and collect statistics. They offertheir own routines to find statistics. Some make use of surveys,observations, experiments, etc. It can also be obtained by focusgroups, interviews, and case studies.