Run a Suite of Queries to Populate the Cache

To maximize potential cache hits, one strategy is to run a suite of queries to populate the cache.

The following are some recommendations for the types of queries to use when creating a suite of queries with which to seed the cache.

• Common prebuilt queries. Queries that are commonly run, particularly ones that are expensive to process, are excellent cache seeding queries. Queries whose results are embedded in dashboards are good examples of common queries.

• SELECT lists with no expressions. Eliminating expressions on SELECT list columns expands the possibility for cache hits. A cached column with an expression can only answer a new query with the same expression; a cached column with no expressions can answer a request for that column with any expression. For example, a cached request such as:

  SELECT QUANTITY, REVENUE...
  

  can answer a new query such as:

  SELECT QUANTITY/REVENUE... 
  

  but not the reverse.

• No WHERE clause. If there is no WHERE clause in a cached result, then it can be used to answer queries that satisfy the cache hit rules for the select list with any WHERE clause that includes columns in the projection list.

In general, the best queries to seed cache with are queries that heavily consume database processing resources and that are likely to be reissued. Be careful not to seed the cache with simple queries that return many rows. These queries (for example, SELECT * FROM PRODUCTS, where PRODUCTS maps directly to a single database table) require very little database processing. Their expense is network and disk overhead, which are factors that caching doesn't alleviate.

When Oracle Analytics Cloud refreshes semantic model variables, it examines business models to determine if they reference those semantic model variables. If they do, Oracle Analytics Cloud purges all cache for those business models. See How Semantic Model Changes Affect the Query Cache.