Visual Evaluation of SQL Plan Cache Algorithms
Caching optimized query plans reduces the time spent optimizing SQL queries at the cost of increased memory consumption. Different cache eviction strategies, such as LRU(-K) or GD(F)S exist that aim at increasing the cache hit ratio or reducing the overall cost of cache misses. A comprehensive study on how different workloads and tuning parameters influence these strategies does not yet publicly exist. We propose a tool that enables both such research as well as performance tuning for DBAs by visualizing the effects of changed parameters in real time.
KeywordsQuery plan caching Cache eviction Query execution
- 1.May, N., et al.: SAP HANA - the evolution of an in-memory DBMS from pure OLAP processing towards mixedworkloads. In: Datenbanksysteme für Business, Technologie und Web (BTW) (2017)Google Scholar
- 2.Attaluri, G.K., Wisneski, D.J.: Method and system for transparently caching and reusing query execution plans efficiently. US Patent 6,466,931, October 2002Google Scholar
- 3.Leis, V., et al.: How good are query optimizers, really? In: Proceedings of the VLDB Endowment, November 2015Google Scholar
- 4.Microsoft: Execution Plan Caching and Reuse. https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx. Accessed 01 Mar 2018
- 5.Oracle: Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g. https://docs.oracle.com/cd/E25178_01/bi.1111/e10541/configfileref.htm. Accessed 01 Mar 2018