Abstract
Computing systems have become more complex and there is a plethora of systems in heterogeneous and autonomous platforms, from mainframes to mobile devices, which need to interoperate and lack effective management. This complexity has demanded huge investments to enable these systems to work properly. It is necessary to invest on software acquisition and installation: management, administration, and update. These costs compound the Total Cost of Ownership (TCO), which tends to increase exponentially according to the software complexity. Information Technology (IT) focuses mainly on providing information services in order to achieve simplicity, agility, large access to information, and competitivity. Database Management Systems (DBMS) are part of the IT infrastructure in large, medium, and even small enterprises.
TopBackground
Manual DBMS tuning requires a collection and analysis of some performance metrics by the DBA. Examples of such metrics include hit ratio in data cache, number of I/O executed in a given table, etc. After collection and analysis of these metrics, the DBA may decide whether it is necessary to adjust the DBMS. If an intervention is due, then the DBA needs to know which components are likely to be adjusted and what are the impacts of such adjustments.
In order to adjust the DBMS to improve performance, the DBA may act in several components such as memory buffers, index creation, SQL tuning, etc. In this chapter we focus on index and memory tuning, due to their importance in the overall performance of the DBMS.
Memory buffers consist of memory areas which are used for a given purpose. DBMS uses them, for example, to store the query execution plans, for both data and SQL statement caching, as an area for data sorting, and for storing session data. The tuning of these buffers may improve the performance of the submitted queries. For example, by increasing the memory buffer size, the number of physical I/O operations may be reduced, which results in performance gains.
Key Terms in this Chapter
Self-Manageable Database: DBMS which is able to self-management, that is, it is able to execute the DBA´s tasks automatically
Memory Buffer: Area of memory which is alocated for a specific destination, such as data caching, session caching, program caching, sorting, and temporary area
Cache Hit Ratio: Is a ratio of buffer cache hits to total of requests, that is, the probability that a data block will be in memory on a subsequent block re-read
Self-Tuning Database: DBMS which is able to automatically administrate and tune its performance.
Cache Miss Ratio: A ratio of the number of times a DBMS cannot find a given data in the memory.
Database Administrator (DBA): Profissional responsible for DBMS administration.
Database Management Systems (DBMS): Software responsible for management of data in a corporation.
Index: Data structure which enables fast access to data stored in disks.