39 Working with Resultset Caching and Queries

Resultset caching is another means of improving the performance of your system. You can create queries that allow CatalogManager to cache resultsets accurately and flush those resultsets from the cache.

Topics:

39.1 About Resultset Caching and Queries

The resultset cache is maintained by the CatalogManager servlet. You or your system administrators set up resultset caching on all three systems (development, management, and delivery). Whenever the database is queried, WebCenter Sites serves a resultset, either cached or uncached. Resultset caching reduces the load on your database and improves the response time for queries.

The wcs_properties.json file provides global properties that set the size and timeout periods for all resultsets. You can add table-specific properties to the wcs_properties.json file that override the default settings on a table-by-table basis. These custom properties enable you to fine-tune your systems for peak performance.

Resultset caching reduces the load on your database and improves the response time for queries. Be sure to take the following steps:

  • Set the default resultset caching properties in the wcs_properties.json file to values that make sense on each of your systems (development, management, testing, and delivery).

  • Add table-specific resultset caching properties to the wcs_properties.json file to fine-tune the performance of all of your systems (development, management, testing, and delivery).

  • Provide the correct table name for all of your queries so the resultsets are cached correctly and can be flushed correctly.

39.2 Caching Frameworks

By default, WebCenter Sites stores resultsets in the inCache framework. You have the option to switch to caching in hash table.

See Switching Between Caching Frameworks.

When resultset caching over inCache is enabled, the System Tools node (on the Admin tab of the Admin interface) displays the resultset over inCache tool, which provides statistical information about resultset caches and their contents. Note that resultset caching over inCache functions independently of page and asset caching over inCache. For more information about the inCache framework, its caching models, and system tools, see Working with Cache Management - Resultset Cache in Oracle Fusion Middleware Administering Oracle WebCenter Sites.

39.3 Database Queries

There are several ways you can query the WebCenter Sites database for information. See some examples here.

  • Use the ics.SelectTo Java method, SELECTTO XML tag, or ics:selectto JSP tag.

  • Use the selectrow command of the ics.CatalogManager Java method, the CATALOGMANAGER XML tag, and the ics:catalogmanager JSP tag.

  • Use the ics.SQL Java method, EXECSQL XML tag, or ics:sql JSP tag.

  • Use the ics.CallSQL Java method, CALLSQL XML tag, or ics:callsql JSP tag.

  • Use the Search forms in the WebCenter Sites interface.

  • Use a query asset.

  • Use a SEARCHSTATE XML or JSP tag (flex assets only).

39.4 How Resultset Caching Works

When you query the database, the resultset from the query is cached—if resultset caching is enabled. The resultset cache is either a hash table or the inCache framework, based on how you configured the rsCacheOverInCache property in the wcs_properties.json file.

Table-specific properties override the default properties and enable you to fine-tune your systems for peak performance.

Default properties are resultset caching properties in the wcs_properties.json file that are assigned to all tables.

These properties are used for querying the database. If someone runs the same query and the data in the table remains unchanged since the last time the query was run, WebCenter Sites serves the information from the resultset cache rather than querying the database again. Serving a resultset from cache is always faster than performing another database lookup.

The resultsets are organized by the name of the table that was associated with the query that generated the resultset. In other words, resultsets are cached against a table name.

Each time a table is updated (from either the WebCenter Sites interface or through a CatalogManager command in your custom elements), all the resultsets in the cache for that table are flushed. Resultsets are cached in the context of a single Java VM. Although Java VMs do not share resultsets, WebCenter Sites sends a signal to all the Java VMs in a cluster to flush the resultsets when they become invalid, while the synchronization feature has been enabled on all servers in the cluster.

39.5 Reducing the Load on the Database

Resultset caching reduces the load on your database in many ways. For example, at the time of serving it doesn’t need a database connection, it’s enabled only when there are uncached resultsets, etc.

  • Serving a cached resultset does not open a database connection. WebCenter Sites attempts to obtain a resultset from the cache before it contacts the database. If the correct resultset exists, no contact is made with the database.

  • When resultset caching is enabled but the appropriate resultset is not cached, WebCenter Sites obtains the resultset, stores it in the cache as an object, and then releases the database connection.

  • When resultset caching is not enabled, WebCenter Sites cannot close the database connection until either the online page is completely rendered or the uncached resultset is explicitly flushed from the scope with a flush tag. When this occurs, your available database connections can be quickly used up (even on a relatively simple page).

As a general rule, resultset caching should be enabled for all of your database tables. Although there are times when you might have to limit either the number of resultsets that are cached or the length of time that they are cached for, it is rarely a good idea to disable resultset caching altogether.

Note:

Never disable resultset caching on the ElementCatalog table. If you do, the performance of your system will suffer greatly, especially if you are using JSP in any of your elements.

39.6 Specifying the Table Name

Always remember to associate a table name with a query to cache the resultset against that table. Then, whenever you update the table through the WebCenter Sites interface or through your own custom elements, CatalogManager flushes all the resultsets associated with that table.

The way that the table name is specified for a resultset depends on the type of query you are running. The following sections describe the most commonly used methods for querying the database and how you specify the table name for such a query.

39.6.1 SELECTTO

When you use the ics.SelectTo Java method, SELECTTO XML tag, or ics:selectto JSP tag, you must specify the name of the table with a FROM parameter (clause). For example:

<SELECTTO FROM="EmployeeInfo"
  WHERE="name"
  WHAT="*"
  LIST="MatchingEmployees"/>

In this case, EmployeeInfo is the name of the table that is being queried and is the name of the table that the resultset is cached against. Whenever the EmployeeInfo table is updated, CatalogManager flushes all the resultsets cached against it.

39.6.2 EXECSQL

EXECSQL lets you execute an inline SQL statement. You specify the table or tables that you want to cache the resultset against using the TABLE parameter. If you specify multiple tables (by using a comma-separated list), the resultset is cached against the first table in the list. Note that this means the resultset is cached based on the resultset cache settings specified for the first table, including timeout and maximum size.

CatalogManager deletes outdated resultsets as the specified tables are updated.

For example, the following query caches the resultset against the article table:

<EXECSQL SQL="SELECT article.headline, images.imagefile FROM article,images WHERE article.id='FTX1EE17FWB' AND images.id='FTK9384FWW'" LIST="sqlresult" TABLE="article,images"/>

39.6.3 CALLSQL

When you use the ics.CallSQL Java method, CALLSQL XML tag, or ics:callsql JSP tag to invoke a SQL query that is stored in the SystemSQL table, the table name is set by the query's entry (row) in the SystemSQL table.

The SystemSQL table has a deftable column that identifies the table name that the resultset from the query should be cached against. You can specify multiple tables by putting a comma-separated list of tables in the deftable column. The first table in the list is the table that the query is cached against.

Each query stored in the table must have a value in the deftable column. If it does not, CatalogManager cannot store the resultsets accurately, which means they cannot be flushed when it is necessary. Note that the table name must identify an existing table. If you enter the name of a table that does not exist yet or if you misspell the name of the table, the resultset cannot be cached correctly.

39.6.4 Search Forms in the WebCenter Sites Interface

The Search forms that you use to look for assets in the WebCenter Sites interface search by asset type. The resultsets from the search form queries are stored against the primary storage table for assets of that type.

For example, for the avisports sample site asset type named Article, those resultsets are cached against the AVIArticle table, for page assets it is the Page table, and so on.

39.6.5 Query Asset

Query assets can return assets of only one type. When you create a query asset, you specify what kind of asset the query asset returns in the Result of Query field: articles, imagefiles, and so on.

When that query asset is used on a page in the online site, WebCenter Sites stores the resultset against the table name of the primary storage table for the asset type that the query asset returns, for example, Article or Imagefile.

39.6.6 SEARCHSTATE

The SEARCHSTATE XML and JSP tags create a set of search constraints that are applied to a list or set of flex assets (created with the ASSETSET tags). A constraint can be either a filter (restriction) based on the value of an attribute or based on another searchstate (called a nested searchstate).

You can use the SEARCHSTATE and ASSETSET tags to extract and display flex assets or flex parent assets (not definitions or flex attributes) on your online pages for your visitors.

WebCenter Sites caches the resultsets of searchstates against the _Mungo table for the flex asset type. For example, if the searchstate returns the avisports sample site flex asset named article, the resultset is cached against the AVIArticle_Mungo table.

When you configure the delivery system, be sure to add resultset caching properties for all of your _Mungo tables.

39.7 Flushing the Resultset Cache

In most cases, data is written to the database through the CatalogManager API, which flushes the resultset cache when it is appropriate to do so. CatalogManager can flush all the resultsets cached against that table. Or, it can flush the resultsets cached against the tables that are written to. There are more possibilities for you to learn about.

For example:

  • If you use WebCenter Sites Explorer to add a row to a table (the SiteCatalog table or the ElementCatalog table, for example), CatalogManager flushes all the resultsets cached against that table.

  • If you use a form in the WebCenter Sites interface to add or edit an asset, a source, a category, a workflow process, a user, an ACL, etc., CatalogManager flushes the resultsets cached against the tables that are written to.

  • If you use CatalogManager commands in an element of your own to update a single table, Catalog Manager automatically flushes the resultsets cached against that table.

  • If you use CatalogManager commands in an element of your own to update multiple (joined) tables, Catalog Manager automatically flushes the resultsets cached against the joined tables.

  • If you use the CALLSQL tag to execute a SQL statement that is stored in the SystemSQL table, Catalog Manager automatically updates the resultsets cached against the table or tables specified in the deftable column.

39.8 Switching Between Caching Frameworks

You can switch between the inCache and hash table frameworks by setting the rsCacheOverInCache property to either true or false in the wcs_properties.json file.

Resultset caching over inCache is enabled when the following conditions are met:

  • The linked-cache.xml configuration file is placed in the application server's classpath (WEB-INF/classes directory).

  • The rsCacheOverInCache property (in wcs_properties.json) is set to true.

39.9 About Resultset Caching Strategy and Properties

Before you configure resultset caching for your database, discuss with your team and database administrators how you should configure the database tables’ properties to maximize the performance of the delivery system. The default properties control the table resultset caches. Table-specific properties enable you to fine-tune your systems for peak performance.

These topics describe the process of planning and using resultset caching properties for all tables and specific tables.

39.9.1 Planning Your Resultset Caching Strategy

Before you configure resultset caching for your database, create a spreadsheet of all the tables in your WebCenter Sites database, assemble a team of developers and database administrators, and discuss what the settings should be for all of your systems (development, management, testing, and delivery). One strategy is to identify default properties for a large group of similar tables, and then add table-specific properties for the exceptions. To fine-tune your delivery system for the best performance possible, however, it is likely that you will create custom properties for each table in the database.

Note:

If you set the com.fatwire.logging.cs.cache.resultset property, debugging messages about the resultset cache are written to the WebCenter Sites log file. Set the property in logging-config.xml.

39.9.2 Default Properties

This table describes resultset caching properties in WebCenter Sites wcs_properties.json that are assigned to all tables. The properties control the table resultset caches while no table-specific caching properties are assigned to the tables. The same properties are valid for resultset caching in both inCache and hash tables. Use the Property Management Tool, in the Admin interface, to change the values of the properties in the WebCenter Sites wcs_properties.json file. For information about using the Property Management Tool, see Introducing WebCenter Sites Tools and Utilities.

Table 39-1 Default Properties That Control the Resultset Cache

property description

cc.cacheResults

Specifies the default number of resultsets to cache in memory. Note that this does not mean the number of records in a resultset, but the number of resultsets.

Important: Unless you are debugging, do not set this property to 0 or -1. If you do, the WebCenter Sites interface will fail to save assets properly. (Setting this property to 0 or -1 disables resultset caching for all tables that do not have their own caching properties configured.)

cc.cacheResultsTimeout

Specifies the number of minutes to keep a resultset cached in memory.

Setting this property to -1 means there is no timeout value for tables that do not have their own caching properties configured.

cc.cacheResultsAbs

Specifies how expiration time in the resultset cache is calculated.

  • If this property is set to true, the expiration time is absolute. For example, if cc.cacheResultsTimeout is set to 5 minutes, then 5 minutes after the resultset was cached, it is flushed from the cache.

  • If this property is set to false, the expiration time is based on its idle time. For example, if cc.cacheResultsTimeout is set to 5 minutes, the resultset is flushed from the cache 5 minutes after the last time it was requested, rather than 5 minutes since it was originally cached.

39.9.3 Table-Specific Properties

Table-specific properties override the default properties and enable you to fine-tune your systems for peak performance.CatalogManager uses the default properties described in Table 39-1 and checks the wcs_properties.json file to determine if it contains any table-specific resultset caching properties.

You can create three resultset caching properties for each table in the WebCenter Sites database. Table-specific properties work in the same way as the default properties (described in Table 39-1).

Syntax for table-specific properties is as follows:

cc.<tablename>CSz=<number of resultsets>
cc.<tablename>Timeout=<number of minutes>
cc.<tablename>Abs=<true or false>

Note:

If an asset type is enabled for revision tracking, and you want to cache the resultsets of asset versions, use the properties above, but add _t after <tablename>:

cc.<tablename>_tCSz=<number of resultsets>
cc.<tablename>_tTimeout=<number of minutes>
cc.<tablename>_tAbs=<true or false>

See Resultset Caching Properties in the Oracle Fusion Middleware Property Files Reference for Oracle WebCenter Sites.

Open the Property Management Tool and add table-specific properties for each table that you want to control. See Property Management Tool.