The CatalogManager servlet (and its API) maintains the resultset cache on your WebCenter Sites systems. This chapter describes resultset caching and how to create queries that allow CatalogManager to accurately cache resultsets and to then flush those resultsets from the cache. 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 a cached resultset or an uncached resultset. Resultset caching reduces the load on your database and improves the response time for queries.
The futuretense.ini file provides global properties that set the size and timeout periods for all resultsets. You can add table-specific properties to the futuretense.ini 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.
This chapter contains the following sections:
By default, WebCenter Sites stores resultsets in the inCache framework. You have the option to switch to caching in hash tables, as described in "Section 14.7, "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 comprehensive information about the inCache framework, its caching models, and system tools, see the Oracle Fusion Middleware WebCenter Sites Administrator's Guide.
There are several ways to query the WebCenter Sites database for information. For example:
With the ics.SelectTo Java method,SELECTTO XML tag, or ics:selectto JSP tag
With the selectrow command of the ics.CatalogManager Java method, the CATALOGMANAGER XML tag, and the ics:catalogmanager JSP tag
With the ics.SQL Java method, EXECSQL XML tag, or ics:sql JSP tag
With the ics.CallSQL Java method, CALLSQL XML tag, or ics:callsql JSP tag
Through the Search forms in the WebCenter Sites interface
With a query asset
With a SEARCHSTATE XML or JSP tag (flex assets only)
When the database is queried, the resultset from the query is cached if resultset caching is enabled (by the properties described in Section 14.8.2, "Default Properties" and Section 14.8.3, "Table-Specific Properties"). Then, 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 resultset cache is either a hash table or the inCache framework, depending on how the rsCacheOverInCache property in futuretense.ini is configured. 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, as long as the synchronization feature has been enabled on all servers in the cluster.
Resultset caching reduces the load on your database in two ways:
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 need 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.
There must always be a table name associated with a query so that the resultset can be cached against that table. Then, whenever that table is updated through the WebCenter Sites interface or 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.
This section contains the following topics:
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.
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 will be cached against the first table in the list. Note that this means the resultset will be 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"/>
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.
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 Burlington Financial sample site asset named article, those resultsets are cached against the Article table; for page assets, it is the Page table; and so on.
Query assets can return assets of one type only. When you create a query asset, you specify what kind of asset the query asset returns in the Result of Query field: articles, or 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: Article or Imagefile, and so on.
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 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 GE Lighting sample site flex asset named product, the resultset is cached against the Products_Mungo table.
When you configure the delivery system, be sure to add resultset caching properties for all of your _Mungo tables.
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. 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 and so on, 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.
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 futuretense.ini) is set to true.
You can then switch between the inCache and hash table frameworks by setting the rsCacheOverInCache property to either true or false.
This section describes the process of planning and using resultset caching properties for all tables and specific tables.
This section contains the following topics:
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 a large group of similar tables for which you can use the default properties, and then add table-specific properties for the exceptions. To 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 on that system, at the very least, 50 to 100 of them.
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 either the commons-logging.properties file, or in log4j.properties, depending on which logging framework you are using.)
Table 14-1 describes resultset caching properties in futuretense.ini that are assigned to all tables. The properties control the tables' resultset caches as long as no table-specific caching properties are assigned to the tables. The same properties are valid for resultset caching in both inCache and hash tables. To change these properties, open the futuretense.ini file with the Property Editor utility and modify them. For information about using the Property Editor, see Chapter 8, "WebCenter Sites Tools and Utilities."
Table 14-1 Default Properties That Control the Resultset Cache
| property | description |
|---|---|
|
|
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. Caution: Unless you are debugging, do not set this property to |
|
|
Specifies the number of minutes to keep a resultset cached in memory. Setting this property to |
|
|
Specifies how expiration time for resultsets in the resultset cache is calculated.
|
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 14-1 and checks the futuretense.ini 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 14-1).
Syntax for table-specific properties is the following:
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 wish 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>
For more information about resultset caching, see the Oracle Fusion Middleware WebCenter Sites Property Files Reference.
Open the futuretense.ini file in the Property Editor utility and add table-specific properties for each table that you want to control. For information about using the Property Editor utility, see Chapter 8, "WebCenter Sites Tools and Utilities."
Resultset caching reduces the load on your database and improves the response time for queries. Be sure to do the following:
Set the default resultset caching properties in the futuretense.ini file to values that make sense on each of your systems (development, management, testing, and delivery).
Add table-specific resultset caching properties to the futuretense.ini 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.