Optimization of parallel query execution plans in XPRS

In this paper, we describe our approach to optimization of query execution plans in XPRS, a multiuser parallel database system based on a shared memory multiprocessor and a disk array. The main difficulties in this optimization problem are the compile-time unknown parameters such as available buffer size and number of free processors, and the enormous search space of possible parallel plans. We deal with these problems with a novel two phase optimization strategy which dramatically reduces the search space and allows run time parameters without significantly compromising plan optimality. In this paper we present our two phase optimization strategy and give experimental evidence from XPRS benchmarks that indicate that it almost always produces optimal or close to optimal plans.

[1]  Wei Hong,et al.  Exploiting inter-operation parallelism in XPRS , 1992, SIGMOD '92.

[2]  Günter von Bültzingsloewen.  Optimizing SQL queries for parallel execution , 1989, SGMD.

[3]  Guy M. Lohman,et al.  Index scans using a finite LRU buffer: a validated I/O model , 1989, ACM Trans. Database Syst..

[4]  Michael Stonebraker,et al.  The Case for Shared Nothing , 1985, HPTS.

[5]  Randy H. Katz,et al.  A case for redundant arrays of inexpensive disks (RAID) , 1988, SIGMOD '88.

[6]  Philip S. Yu,et al.  Integration of Buffer Management and Query Optimization in Relational Database Environment , 1989, VLDB.

[7]  Patricia G. Selinger,et al.  Access path selection in a relational database management system , 1979, SIGMOD '79.

[8]  Leonard D. Shapiro,et al.  Join processing in database systems with large main memories , 1986, TODS.

[9]  David J. DeWitt,et al.  A performance evaluation of four parallel join algorithms in a shared-nothing multiprocessor environment , 1989, SIGMOD '89.

[10]  David J. DeWitt,et al.  GAMMA - A High Performance Dataflow Database Machine , 1986, VLDB.

[11]  D. Rotem,et al.  Processor scheduling for multiprocessor joins , 1989, [1989] Proceedings. Fifth International Conference on Data Engineering.

[12]  Michael Stonebraker,et al.  A performance comparison of two architectures for fast transaction processing , 1988, Proceedings. Fourth International Conference on Data Engineering.

[13]  David J. DeWitt,et al.  Benchmarking Database Systems A Systematic Approach , 1983, VLDB.

[14]  Karen Ward,et al.  Dynamic query evaluation plans , 1989, SIGMOD '89.

[15]  David J. DeWitt,et al.  Tradeoffs in Processing Complex Join Queries via Hashing in Multiprocessor Database Machines , 1990, VLDB.

[16]  Jim Gray,et al.  The convoy phenomenon , 1979, OPSR.

[17]  Michael Stonebraker,et al.  The Design of XPRS , 1988, VLDB.

[18]  Hongjun Lu,et al.  Design and evaluation of parallel pipelined join algorithms , 1987, SIGMOD '87.

[19]  M. C. Murphy,et al.  Execution plan balancing , 1991, [1991] Proceedings. Seventh International Conference on Data Engineering.

[20]  Tom W. Keller,et al.  Data placement in Bubba , 1988, SIGMOD '88.

[21]  Hansjörg Zeller,et al.  An Adaptive Hash Join Algorithm for Multiuser Environments , 1990, VLDB.