Skip Headers
Oracle® Fusion Middleware Oracle WebCenter Analytics Administrator's Guide (for Oracle WebCenter Interaction)
10g Release 4 (

Part Number E14112-04
Go to Documentation Home
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

4 Managing the Oracle WebCenter Analytics Database

This chapter describes how to size your Oracle WebCenter Analytics database and how to archive and restore the database partitions.


Events are recorded in the database in batches, not at the moment they occur. The default batch size is 100. To change the batch size edit the BATCH_INSERT_SIZE value in the ASSYS_CONFIG table.

Sizing the Oracle WebCenter Analytics Database

This section provides information and recommendations that should help you manage the size and growth of your Oracle WebCenter Analytics database to, which should increase performance. It includes the following topics:

Overview of Oracle WebCenter Analytics Database Growth

The majority of growth in the Oracle WebCenter Analytics database occurs in the fact tables that are delivered with Oracle WebCenter Analytics. The rest of the delivered Oracle WebCenter Analytics tables -- including dimension tables -- generate negligible growth in the Oracle WebCenter Analytics database. This section provides details on the growth of fact and dimension tables.

Fact Table Growth

Fact tables capture event parameter data of types Date, Integer, and Float. Of the delivered Oracle WebCenter Analytics fact tables, the ASFACT_PAGEVIEWS, ASFACT_PORTLETUSES, and ASFACT_PORTLETVIEWS tables grow at the fastest rate. For this reason, you should closely monitor these tables. For a complete list of delivered fact tables, see Working with Delivered Fact Tables.

Custom events that you create and register using the Event Registration section of Analytics Administration are also stored in fact tables. If you expect any of your custom events to generate large volumes of data (in the range of one million events per year), we recommend that you monitor their tables, and perhaps archive older partitions. For guidelines on archiving and restoring partitions, see Archiving and Restoring Partitions.

Both Oracle and SQL Server databases grow at approximately the same rate: every one million events that are stored in the database use approximately 150 megabytes of disk space. The following table lists numbers of events and their corresponding estimated database sizes.

Table 4-1 Numbers of Events and Corresponding Estimated Database Sizes

Number of Events Estimated Database Size


150 megabytes


300 megabytes


750 megabytes


1.5 gigabytes

Use these size estimates to calculate your own database growth requirements. As the number of events in your system continues to grow, query performance ultimately starts to decline. For this reason, in high-volume environments you should monitor the growth of your database and take appropriate measures to prevent performance degradation.

Dimension Table Growth

Oracle WebCenter Analytics uses dimensions to capture event parameter data of type String. Dimension tables do not grow as quickly as delivered fact tables because dimension data does not change at nearly the same rate as events occur.

Note that if an object is removed from the application on which Oracle WebCenter Analytics is reporting, the record for that object's dimension data remains in the Oracle WebCenter Analytics database. For this reason, Oracle WebCenter Analytics continues to report the events that occurred on this object before it was removed from the application.

Restricting Portlet View Data

Portlet views are the most heavily-generated events in the system. For this reason, we recommend that you do not collect portlet view data continuously, which results in tripling the growth rate of your database. Instead, you should only "sample" portlet views periodically. To do this, enable the Capture portlet views/response times option for a limited period of time, such as one or two weeks. You enable this option in Analytics Administration's Runtime Settings page. For details, see Configuring Runtime Settings.

If you decide to continuously collect portlet view data, you should aggressively archive older data partitions to prevent the database from reaching its capacity too quickly.

Portlet view data is reported on the Portlet Metrics - Views console page. Response time data is reported on the Portlet Metrics - Response Time console page.

Tuning the Oracle WebCenter Analytics Database - Oracle

To obtain the best performance with Oracle, we recommend that you:

  • Edit the create_analytics_tablespaces.sql script and include appropriate sizing information for your database. The create_analytics_tablespaces.sql script is located in a platform-specific subdirectory within the Oracle WebCenter Analytics database's host computer.


    The default values in the create_analytics_tablespaces.sql script are acceptable for a development or staging database. You should, however, change these values accordingly in a production environment.

  • Move the ANALYTICSTABLE tablespace to a different drive than the one used for the ANALYTICSINDEX tablespace.

  • Configure the Oracle settings described in this table:

Table 4-2 Oracle Settings and Configuration Recommendations

Oracle Setting Configuration

Buffer Cache

Increase to 250 megabytes

PGA Aggregate Target

Increase to 150 megabytes

Data Block Size

Increase to 16K


The recommendations in this table are for use with the Oracle WebCenter Analytics database only. You might want to change these configurations slightly to more appropriately suit your environment.

Working with Delivered Fact Tables

The following table lists the fact tables that are delivered with Oracle WebCenter Analytics. Every fact table name begins with the prefix ASFACT_, which helps you identify fact tables when working with the Oracle WebCenter Analytics database.

Table 4-3 Fact Tables Delivered with Oracle WebCenter Analytics

Fact Table Description


document views


Knowledge Directory views


user logins


page views


portlet uses


portlet views


Publisher item views



For more detailed descriptions of these tables -- and all tables that are delivered with Oracle WebCenter Analytics -- see the Oracle WebCenter Analytics Database Schema document.

Archiving and Restoring Partitions

This section provides guidelines for archiving and restoring the partitions within your Oracle WebCenter Analytics database. For details on using Analytics Administration's Partition Settings page, see Configuring Partition Settings.

To maintain a steady size of your Oracle WebCenter Analytics database and keep your queries performing quickly, we recommend archiving partitioned data that is greater than six months old. You can identify partitioned tables by their date/year suffix. For example: _08_2006. You archive and restore partitions using database commands. See documentation for database for more details. It is up to your organization to decide the media to which you want to back up your data.

After archiving or restoring partitions, you must refresh the database views by clicking Finish on the Partition Settings page. If you do not refresh the database views, Oracle WebCenter Analytics reports will fail.


Never remove the current fact table, which is not partitioned. Also, never remove database views. Instead, use the scrolling view window to set the number of partitions that are accessible to Oracle WebCenter Analytics reports. Additionally, if you delete archived partitions from your database, you will not be able to restore them. As a result, customers will no longer have access to the deleted data.