[Mondrian] result set caching, MySQL and Oracle

[Matt Campbell]

Julian had mentioned to me a few times how much faster the test suite is with MySQL caching.  Everyone else may have already known this, but I just found out that it's not enabled by default.  You can see how much space is allocated with the following.

SHOW VARIABLES LIKE 'query_cache_size'

You can set it with

SET GLOBAL query_cache_size = NNN

I bumped mine from 0 to 32 meg, and that dropped my test run time from around 16-17 minutes to just under 6 minutes after the first run.  Very nice.

Oracle 11 also supports result set caching.  You can determine your current settings with

                     SELECT name, value, isdefault
                    FROM   v$parameter
    WHERE  name LIKE 'result_cache%'

And change allocated size with

ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY

The other key thing to be aware of is that by default Oracle will only use caching if there is a hint in the query (or I think with a connection param).  To force it to always try to use result set caching set the following parameter:

ALTER SESSION SET result_cache_mode = FORCE



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130211/5ad73fa8/attachment.html