It has been a very busy few months and the appliance team moves apace and is continuously subjected to requests for new applications and Ingres releases.
With the latest release of Ingres one of the new features is cached dynamic query plans; that will benefit any application that uses prepared dynamic queries.
Ingres has long had the ability to cache query plans for stored procedures and from queries in embedded SQL programs using the REPEATED keyword. The latter has meant that queries are identified and tagged in the application. For applications that are written using a driver for example, ODBC, JDBC, .NET and PHP it hasn’t been possible, until now, to use store a query plan for reuse. This feature benefits queries that take a long time to optimize and compile (for a particular value of long) and obviously the more times the same query is executed the better the benefit of caching as the optimization and compilation time is amortized over the number of executions.
As this is the first implementation it is disabled by default and the scope is severely limited. For now it is implemented only for selects and for queries that require greedy enumeration.
To give an example; the application that I am using generates queries for its underlying database and I have no control over it. These queries although complex return a very low volume of results. Looking at the query plans there are between 50 and 80 candidate tables and indices. Without greedy enumeration these queries don’t even compile, not only that but these queries are executed hundreds of times. With greedy enumeration the query must complete compilation, before that there is only a partial plan so setting join op time out won’t help.
So here is how you can control caching:
CBF - ii.hostname.dbms.*.cache_dynamic: ON or OFF
SQL - server control: set [no]cache_dynamic - alter the current server setting including overriding the CBF parameter.
- session control: set session [no]cache_dynamic - enable/disable query caching within a session
- status: select dbmsinfo(’cache_dynamic’) - Y or N
- status imadb: select rqp_text from ima_qsf_rqp shows query text for cached text objects for dynamic cursors.
Until cached dynamic query plans I had to suffer in silence. I’d be interested if anyone else finds the feature a benefit, if you do let me know.
Post a Comment