32 System Data: WebCenter Sites Database

This chapter contains information about the dynamic tables in the WebCenter Sites database and how they grow. DBAs can use this information to determine how to size the WebCenter Sites database appropriately.

This chapter contains the following sections:

This chapter also contains information about how to purge inactive data from the visitor tables.

32.1 Cache Management Tables

WebCenter Sites delivers the CacheManager, a page caching utility that manages both the WebCenter Sites page cache and the Satellite Server caches.

Because cached pages need to expire both when their freshness date expires and when an asset that the page refers to in some way is changed, the CacheManager keeps track of expiration times as well as the dependencies that exist between the pages and pagelets stored in the cache. It stores this information in the SystemPageCache and SystemItemCache tables.

Every Sites system uses a CacheManager, which means that these tables grow dynamically on any system—development, management, or delivery. The cache-tracking tables grow at the following rate:

Table Number of Rows
SystemPageCache One row for every cached page.

When a page expires, the row is removed.
SystemItemCache One row for each asset, pagelet, or other item that is referenced by a cached page in the SystemPageCache table. For example, if a cached page was created from a page asset that has associations to three article assets, there would be four rows for that cached page.

32.2 Approval System Tables

The WebCenter Sites approval system keeps track of each asset that has been approved, the dependencies that approved assets have on other assets, and the targets for which assets are approved. It stores this information in the ApprovedAssets and ApprovedAssetDeps tables.

These tables have the potential to grow very large on a management system, but are not used on a delivery system. The approval system tables grow at the following rate:

Table Number of Rows
ApprovedAssets One row for each asset that has been approved, for each target destination. That is, if an asset has been approved for two destinations, that asset has two rows in this table.
ApprovedAssetDeps One row for each asset dependency for each approved asset, for each target destination.

For example, if an approved asset is dependent on four other assets, it has four rows in this table. If that same asset is approved to two destinations, it has one row each for each dependency for each destination.

32.3 Publishing System Tables

The WebCenter Sites publishing system keeps track of when assets were published, and where they were published to. It stores this information in the PubKey and the PublishedAssets tables.

As the number of assets in your Sites management system increases, so does the number of rows in these tables. These tables grow at the following rate:

Table Number of Rows
PubKey
  • For mirror publishing: one row for every asset that is mirrored, for each target destination.

  • For export publishing: one row for each page that is created during the export. That is, if 14 assets are rendered into 1 page, the table holds 1 row—not 14—for the entire group of assets.
PublishedAssets
  • For mirror publishing: one row for every asset that is mirrored, for each target destination.

  • For export publishing: one row for each asset that is exported. To continue the preceding example, if 14 assets are rendered onto 1 page, the table holds 1 rows (one for each asset) for that page.

32.4 Workflow Tables

The workflow system keeps track of all the assets that are involved in a workflow process at any given time. It stores this information in the Assignment and WorkflowObject tables.

As the number of assets that are placed in a workflow process increases, so does the number of rows in these tables. These tables grow at the following rate:

Table Number of Rows
Assignment One row for each workflow assignment. For example, if an asset is assigned to four users during the course of a workflow process, that asset has four rows in the table.

These rows are not deleted when the workflow process is completed for the asset.
WorkflowObjects One row for each asset in workflow.

When an asset leaves workflow, the row is deleted.

32.5 Basic Asset Tables

A basic asset type has one primary storage table. For example, the primary storage table for the article asset type is named Article; the primary storage table for the HelloArticle asset type is named HelloArticle.

As the number of assets of a single type increases, so does the size of the table that holds assets of that type. The primary storage table for a basic asset has one row for each asset of that type.

32.6 Flex Asset Tables

Each asset type in a flex family has several database tables. The three types of tables in any flex family that can potentially grow quite large are as follows:

  • The primary storage table for the flex asset type. For example, the primary storage table for the Avisports sample site asset type named article is AVIArticle.

  • The _AMap tables for flex asset or flex parent asset types. (For example, AVIArticle_AMap.)

  • The _Group tables for flex parent asset types. (For example, ArticleCategory_Group.)

  • The _Mungo table for the flex asset type. (For example, AVIArticle_Mungo.)

  • The _Mungo table for the flex parent asset type. (For example, ArticleCategory_Mungo.)

  • The Mungo_Blobs table

These types of tables grow at the following rate:

Table Number of Rows
FlexAssetType (for example: AVIArticle) One row for every asset of this type.
FlexAssetType_AMap

(for example: AVIArticle_AMap)

 One row per attribute value—whether the attribute value is inherited or directly assigned—for each of the assets of that type.
FlexAssetType_Group

(for example: ArticleCategory_Group)

 One row per ancestor relationship between flex parent asset and flex asset—includes rows for grandparent, great-grandparent, and so on, relationships.
FlexAssetType_Mungo

(for example: AVIArticle_Mungo)

 One row for every attribute value for each asset of this type.

In some cases, this equation can result in more than 10 million rows.
FlexParent_Mungo(for example: ArticleCategory_Mungo) One row for every attribute value for every parent asset of this type.
Mungo_Blobs One row for every attribute value saved for an attribute of type blob.

32.7 Visitor Tables (Oracle WebCenter Sites: Engage)

Oracle WebCenter Sites: Engage captures visitor information and stores it in the visitor data tables. These tables store information such as session IDs for visitors so that they can be linked with their previous sessions and values for the attributes that represent the data you are collecting.

As the number of visitors who visit your online site increases, so do the rows in these tables. These tables grow at the following rate:

Table Number of Rows
scratch One row for each visitor context session object that is created for a visitor.

Visitor context session objects are things like promotion lists, segment lists, shopping carts, and so on. There are at least five rows added to this table for each visitor in each session.
VMVISITOR One row for each visitor for each browser session.

Engage creates a unique visitor ID for each visitor for each session.
VMVISITORALIAS In database-centric visitor tracking, one row is created for each visitor for each browser session, even for returning visitors. This method creates one row per visit.

Memory-centric visitor tracking creates one row per visitor, regardless of the number of returning visits.

Note: A row holds the name/value pair of an alias and the visitor ID (also listed in VMVISITOR) that marks a session.
VMVISITORSCALARVALUE One row for each visitor attribute value (except for attributes of type binary) that is saved.

Note: This table is not populated when memory-based visitor tracking is enabled.
VMVISTORSCALARBLOB One row for each visitor attribute value of type binary (also referred to as scalar objects) that is saved.

Note: This table is not populated when memory-based visitor tracking is enabled.
VMz ------------ These are dynamically generated tables that are created when values for a history attribute are saved.

Engage creates one table for each history type and adds a row to the table each time a record of that type is saved.

This section contains the following topics:

32.7.1 Managing the Attribute Tables

Because the tables that hold attribute values can grow very quickly, you should purge inactive data from them regularly.

You use the following Engage XML object method or its JSP equivalent to delete inactive data from these tables:

<VDM.FLUSHINACTIVE STARTDATE="cutoffDate"/>

Inactive visitor data is data marked with a visitor ID that is not connected through an alias to data that you consider current. You set a cutoff date (STARTDATE) for Engage to use. All visitor data recorded before that date is deleted from the previously listed visitor tables unless it is linked through an alias with data recorded after that date.

There are several ways to use the VDM.FLUSHINACTIVE tag. For example:

Note that the value that you pass to the STARTDATE parameter must be in epoch time. You can use the WebCenter Sites DATE.CONVERT tag to obtain an epoch value for the date that you want to use. For example:

<DATE.CONVERT VARNAME="flushtime" 
   YEAR="four digit year" MONTH="number in the range 1-12"
   DAY="number in the range 1-31" 
   [HOUR="number in the range 0-11 where 0 is midnight" AMPM="am
   or pm" MINUTE="number in the range 0-59" TIMEZONE="timezone"]/>
<VDM.FLUSHINACTIVE STARTDATE="Variables.flushtime"/>

For more information about these tags, see the Oracle Fusion Middleware WebCenter Sites: Tag Reference.

32.7.2 Managing the Session Objects Table (scratch)

The scratch table can grow quickly because there are at least five session objects stored for each visitor in each session. Each object has a timestamp; you should purge old objects regularly, based on their timestamps.

You use the following Engage XML object method or its JSP equivalent to delete old session objects from the scratch table:

<SESSIONOBJECTS.FLUSH TIMESTAMP="cutoffTime"/>

Because session objects includes carts, you must set the cutoff time to represent the point at which you consider a cart to be abandoned.

Note:

This object method does not affect the VMSCALARBLOB table, which means that it does not delete carts that you have stored as a visitor attribute of type binary (a scalar object).

Note that the value that you pass to the STARTDATE parameter must be in epoch time. You can use the WebCenter Sites DATE.CONVERT tag to obtain an epoch value for the date that you want to use. For an example, see the code example for the VDM.FLUSHINACTIVE tag provided in the preceding section.

For more information about these tags, see the Oracle Fusion Middleware WebCenter Sites: Tag Reference.

32.7.3 Deleting Unnecessary .class Files

Oracle WebCenter Sites: Engage is a Java-based application and it generates Java .class files each time one of the following events occurs:

  • A segment, recommendation, or promotion is created.

  • A product is configured for a related items recommendation.

  • A segment, recommendation, or promotion is calculated or invoked by Engage.

Typically, old .class files are deleted when a segment, recommendation, promotion, or product is updated and are then replaced with new .class files. However, if the segment, recommendation, product, or promotion is in use when an updated version is published, Engage cannot delete the old .class file because it is locked.

The old .class files can build up, filling up the disk and using memory. Therefore, depending on how much development work you are doing and how frequently you publish to the delivery system, you must manually delete these .class files at a regularly scheduled time.

Complete the following steps to delete old .class files:

  1. Use the Property Editor to examine the vis.genclasspath property and note the directory name designated by that parameter. This is the directory where Engage stores .class files.

  2. During a quiet time on your site, shut down and restart each instance of JRE runtime that is running. This process releases any old .class files that the JRE runtime has locked.

  3. Using any file management tool, navigate to the directory that holds the .class files and delete the contents of this directory.

Engage regenerates any .class files that it needs when it needs them.