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.
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:
ics.SelectTo Java method,
SELECTTO XML tag, or
ics:selectto JSP tag
selectrow command of the
ics.CatalogManager Java method, the
CATALOGMANAGER XML tag, and the
ics:catalogmanager JSP tag
ics.SQL Java method,
EXECSQL XML tag, or
ics:sql JSP tag
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
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.
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 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:
Imagefile, and so on.
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
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
When you configure the delivery system, be sure to add resultset caching properties for all of your
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
Resultset caching over inCache is enabled when the following conditions are met:
linked-cache.xml configuration file is placed in the application server's classpath (
rsCacheOverInCache property (in
futuretense.ini) is set to
You can then switch between the inCache and hash table frameworks by setting the
rsCacheOverInCache property to either
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.
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."
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>
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
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.
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.