Optimization of dynamic query evaluation plans
SIGMOD '94 Proceedings of the 1994 ACM SIGMOD international conference on Management of data
Efficient mid-query re-optimization of sub-optimal query execution plans
SIGMOD '98 Proceedings of the 1998 ACM SIGMOD international conference on Management of data
Eddies: continuously adaptive query processing
SIGMOD '00 Proceedings of the 2000 ACM SIGMOD international conference on Management of data
LEO - DB2's LEarning Optimizer
Proceedings of the 27th International Conference on Very Large Data Bases
Robust query processing through progressive optimization
SIGMOD '04 Proceedings of the 2004 ACM SIGMOD international conference on Management of data
Cost-based query transformation in Oracle
VLDB '06 Proceedings of the 32nd international conference on Very large data bases
SQL memory management in Oracle9i
VLDB '02 Proceedings of the 28th international conference on Very Large Data Bases
Automatic SQL tuning in oracle 10g
VLDB '04 Proceedings of the Thirtieth international conference on Very large data bases - Volume 30
Efficient and scalable statistics gathering for large databases in Oracle 11g
Proceedings of the 2008 ACM SIGMOD international conference on Management of data
Dynamic plan generation for parameterized queries
Proceedings of the 2009 ACM SIGMOD International Conference on Management of data
| Hi-index | 0.00 |
The role of a query optimizer in a database system is to find the best execution plan for a given SQL statement based on statistics about the objects related to the tables referenced in the statement. These statistics include the tables themselves, their indexes, and other derived objects. Statistics include the number of rows, space utilization on disk, distribution of column values, etc. Optimization also relies on system statistics, such as the I/O bandwidth of the storage sub-system. All of this information is fed into a cost model. The cost model is used to compute the cost whenever the query optimizer needs to make a decision for an access path, join method, join order, or query transformation. The optimizer picks the alternative that yields the lowest cost. The quality of the final execution plan depends primarily on the quality of the information fed into the cost model as well as the cost model itself. In this paper, we discuss two of the problems that affect the quality of execution plans generated by the query optimizer: the cardinality of intermediate results and host variable values. We will give details of the solutions we introduced in Oracle 11g. Our approach establishes a bridge from the SQL execution engine to the SQL compiler. The bridge brings valuable information to help the query optimizer assess the impact of its decisions and make better decisions for future executions of the SQL statement. We illustrate the merits of our solutions based on experiments using the Oracle E-Business Suite workload.