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

Part Number E14111-03
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
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 database partitions.

Note:

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.

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

1,000,000

150 megabytes

2,000,000

300 megabytes

5,000,000

750 megabytes

10,000,000

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.

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.

    Note:

    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


Note:

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.

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 fail.

Caution:

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.