An algorithmic approach for performance tuning of a relational database system using dynamic SGA parameters

Abstract

In twenty-first century, spatial database are becoming complex in nature due to the diversity of resources to generate and collect these datasets. Efficient query handing and high performance is the key requirement for success for the expert systems (like GIS) using these spatial databases. However, relational database management systems (RDBMS) used for storing spatial datasets are struggling for auto-tuning, self-diagnosis and self-healing. To overcome these challenges for spatial database and RDBMS, we have identified 250 plus dynamic parameters, which are responsible for managing SGA (system global area) of a running instance of any RDBMS. These parameters can be controlled at runtime and allocation and deallocation of memory to the various components of SGA can be managed by changing the values of these parameters. In this research work, the data is collected related to the system parameters and the resources utilized by the system. The collected data is automatically analyzed to find the relation between parameters and resources. Based upon the relations (direct or inverse) the decision will be taken by the developed utility to enhance overall system performance. In this work a framework, related algorithms, and utility tool to perform above-said steps for improving performance are designed and implemented. This research work will help to get fast and efficient handling of spatial datasets, which will directly affect the performance of expert system to take quick decisions.