Objectives:

1. Gather and analyse data for database performance and tuning.
2. Configure and tune a database for optimal performance

Consider you are working as a DBA in a certain large organization. In order to fine-tune the database performance, you need to gather statistics of the database during a certain period of time e.g. between few hours or few days etc.

i) Explain the installation steps and the key decisions you would take during installation of any utility package of your choice to collect statistics useful for measuring performance of database system. Your solution must also include the steps to generate the report having performance data between two snapshots of a database. The installation and usage procedure must be demonstrated in the college lab in Week 12th as part of the viva. Screenshots must be included in the solution.

ii) Using the report generated in Task-2a, interpret any two areas/statistics of your choice. Necessary screenshot of the chosen area/statistics must be included in the solution.

3a The web link to a research paper "Study of Performance Tuning Techniques" is given in the college Moodle web page:

With the aid of examples, discuss how SQL tuning can be done to improve performance of a database.
The solution must include the screenshots of the SQL commands and brief explanation for each of the commands.
The given research paper is for your reference only and your answer should not be limited to this paper. You may refer various other resources and provided references for the same. Do not simply copy and paste the information from the given research paper.

Note: The solution must be given in your own words with relevant examples not exceeding 2 pages.

3b Discuss how you would tune the physical structures (data files and log files) of a database.

Note: The solution must be in your own words not exceeding one and half page.