The concepts of memory consumption and return on consumption (ROC) are used as the basis of memory allocations. A global optimization strategy using simulated annealing is developed which minimizes the average response time over all queries under the constraint that the total memory consumption rate has to be less than the buffer size. It selects the optimal join method and memory allocation for all queries simultaneously. By analyzing the way that the optimal strategy makes memory allocations, a heuristic threshold strategy is proposed. The threshold strategy is based on the concept of ROC. As the memory consumption rate by all queries is limited by the buffer size, the strategy tries to allocate the memory so as to make sure that a certain level of ROC is achieved. A simulation model is developed to demonstrate that the heuristic strategy yields performance that is very close to the optimal strategy and is far superior to the conventional allocation strategy.>

Vertical partitioning can be used to enhance the performance of relational database systems by reducing the number of disk accesses. The authors identify the key parameters for capturing the behavior of an access plan and propose a two-step methodology consisting of a query analysis step to estimate the parameters and a binary partitioning step which can be applied recursively. The partitioning uses an integer linear programming technique to minimize the number of disk accesses. Significant performance benefit would be achieved for join if the partitioned (inner) relation could fit into the memory buffer under the inner-outer loop join method, or if the partitioned relation could fit into the sort buffer under the sort-merge join method, but not the original relation. For cases where a segment scan or a cluster index scan is used, vertical partitioning of the relation with the algorithm described is still often found to lead to substantial performance improvement.>

In a distributed database environment, the site assignment of relations is a critical issue. When the joint operations in a query involve relations over multiple sites, the site to carry out the joint operation can have a significant impact on the performance. Based on the query descriptions and arrival frequency to each site, a methodology is developed to assign relations and determine joint sites simultaneously. The methodology first decomposes queries into relation steps and then makes site assignments based on either a linear integer programming technique to minimize the amount of intersystem communication while balancing resource utilizations across systems, or a heuristic technique to minimize average response time under similar resource constraints.>

An integrated strategy for choosing the sites where relations are to be stored while simultaneously determining where joint operations are to take place is considered. Based on the transaction characteristic and arrival frequency to each site, a methodology to formulate the strategy is developed to assign relations to systems and determine joint sites so as to minimize the amount of intersystem communication while simultaneously balancing resource utilization among systems. The methodology first decomposes queries into relation steps and then makes site assignments based on linear integer-programming techniques.>

In a relational database environment, transaction response time is likely to be affected by the time required to read the necessary data from secondary storage (disk). In cases where segment scans are used to a significant extent, vertical partitioning of the relation can result in a decrease in the number of disk accesses. The issue is how to set up the criterion for partitioning. In this paper, an optimal binary partitioning algorithm which can be recursively applied is developed. The algorithm is based on an integer linear programming technique to minimize the number of disk accesses. Performance analysis is provided to study the situation when partitioning can be beneficial and quantify the performance impact. This can also be used to demonstrate the superiority of the proposed algorithm as compared with a previously proposed partitioning scheme.

The demand for larger transaction rates and the inability of single-system-based transaction processors to keep up with demand have resulted in the growth of multi-processor-based database systems. The focus here is on coupling in a locally distributed system through multi-system data sharing in which all systems have direct access to the data. This paper addresses the following questions; i) How does a workload running on a single system today perform if migrated to a multi-system? ii) What are the multi-system locking design issues that limit multi-system performance and what is the maximum number of systems that may be effectively coupled? iii) Can alternative locking designs increase the number of systems that may be effectively coupled? Our analysis is based on traces from large mainframe systems running IBM's IMS database management system. We have developed a hierarchical modeling methodology that starts by synthesizing a multi-system IMS lock trace and a reference trace from single-system traces. The multisystem traces are used in trace-driven simulations to predict lock contention and database I/O increase in multi-system environment and to generate workload parameters. These parameters are used in event-driven simulation models to examine the overall performance under different system structures. Performance results are presented for realistic system parameters to determine the performance impact of various design parameters. Lock contention is found to be the critical factor in determining the coupling effectiveness and the effect of alternative locking design to reduce lock contention is studied. The limit on coupling is explored and the analysis indicates that, for this workload, on the order of 6 to 12 systems may be effectively coupled through data sharing, depending on system structure and locking design.

In a multi-system database system with a function request shipping approach, the databases are partitioned among the multiple systems and a facility is provided to support the shipping of database requests among the systems. This is in contrast to a data sharing multi-system approach in which all systems have direct access to the shared database. The performance of the function request shipping approach is examined and compared with the data sharing approach. The emphasis is on generic issues that affect the function shipping approach. Trace data from large mainframe systems running IBM's IMS database management system are used to illustrate the issues of partitioning. A methodology is presented for partitioning the databases and routing transactions among the systems so as to minimize the fraction of remote function calls, while balancing the load among systems. Estimates of the resulting remote function calls, mirror transaction setups, and multi-system two-phase commits, are obtained. A simulation model and approximate analysis are used to examine the overall system performance, to study the effect of various workload parameters and design alternatives and to compare the function shipping and data sharing approaches.

In a multisystem database system with a function request shipping approach, the databases are partitioned among the multiple systems and a facility is provided to support the shipping of database requests among the systems. This is in contrast to a data sharing multisystem approach in which all systems have direct access to the shared database. The performance of the two approaches is compared, emphasizing generic issues that affect the function shipping approach. A methodology is presented for partitioning the databases and routing transactions among the systems so as to minimize the fraction of remote function calls, while balancing the load among systems. Estimates of the resulting remote function calls, mirror transaction setups, and multisystem two-phase commits are obtained. Results of simulation and approximate analysis are presented.

The performance of multiple systems sharing a common data base is analyzed for an architecture with concurrency control using a centralized lock engine. The workload is based on traces from large mainframe systems running IBM's IMS database management system. Based on IMS lock traces the lock contention probability and data base buffer invalidation effect in a multi-system environment is predicted. Workload parameters are generated for use in event-driven simulation models that examine the overall performance of multi-system data sharing, and to determine the performance impact of various system parameters and design alternatives. While performance results are presented for realistic system parameters, the emphasis is on the methodology, approximate analysis technique and on examining the factors that affect multi-system performance.