Overview of Temporary Tables Created By BC4J

An Oracle technical white paper
April 2002

Contents

Introduction

Alongside its core object/relational persistence feature for entity objects, BC4J has a facility to store collections of temporary data. This persistent collections facility (PCOLL) is used by the BC4J framework to temporarily store :

• Pending changes in application modules

  This allows the pending changes in an application module to be logically retained without physically dedicating an application module instance to each client.

• Overflow query result rows when a users scrolls through very large result sets.

  This insures that the application does not run out of memory when an a misguided user query returns too many rows and they decide to scroll through them all. Instead the extra rows "spill-over" into a temporary persistent store without using real-memory.

In order to manage this pending state, BC4J creates a set of tables that hold the temporary information in BLOB values. This document describes these tables and how they are used.

Controlling Where the Temporary Tables Are Created

The BC4J framework recognizes a configuration property named  jbo.server.internal_connection to give the developer control over what database connection/schema should be used for the creation of the PCOLL temporary tables described below. If the value of this configuration parameter is not set by the developer (which is the default situation) then the framework will create the temporary tables using the credentials of the current application database connection. To keep the temporary information separate, it will use a different connection instance from the connection pool, but the database credentials will be the same as the current user.

Since the framework creates temporary tables and possibly a sequence, the implication of not setting a value for the  jbo.server.internal_connection is that the current database user must have CREATE TABLE, CREATE INDEX, and CREATE SEQUENCE privileges. Since this is often not desireable, it is recommended to always supply an appropriate value for the  jbo.server.internal_connection property, providing the credentials for a "utility" schema where default tables can be created.

Valid values for the  jbo.server.internal_connection property in your configuration are:

•  jdbc:oracle:thin:someuser/somepassword@localhost:1521:ORCL
•  YourJ2EEDataSourceName

Overview of Temporary Tables Created

This section describes the different tables that the BC4J framework will create for temporary storage and briefly explains their use. Note that the structure of and use of these table could change as any time and developers are warned against depending on any aspect of their current structure or content.

Database Objects Used By Both Mechanisms

The PCOLL_CONTROLTable

The  PCOLL_CONTROL table maintains the list of the persistent collection storage tables that the BC4J runtime has created and functions as a concurrency control mechanism. When a table named TABNAME is in use for storing some active sessions pending state, the corresponding row in  PCOLL_CONTROL is locked.

The columns of this table are used as follows:

•  TABNAME

  Name of the persistent collection storage table. For example, values in the  TABNAME column might be like  PS_TXN,  PS_Mypackage9Module, etc., as described below.

•  ROWCREATEDATE

  Timestamp of when this row was created in  PCOLL_CONTROL.

•  CREATEDATE

  Timestamp of when the persistent collection storage table named in the  TABNAME column was created.

•  UPDATEDATE

  Timestamp of when the most recent persistent collection operation was performed successfully on the storage table named in  TABNAME.

Database Objects Used For Application Module State Management

The PS_TXN Table

The  PS_TXN table stores snapshots of pending changes made to BC4J application module instances. The table manages the B-Tree storage of rows. The snapshot information is stored as an XML document that encodes the unposted changes in an application module instance. Only pending data changes are stored in the snapshot, along with information about the current state of active iterators (i.e. "current row" pointers information). The value of the COLLID column corresponds to the value returned by the  ApplicationModule.passivateState() method.

The PS_TXN_SEQ Sequence

This sequence is used to assign the next persistent snapshot Id for Application Module pending state management. If the  PS_TXN table contains any rows at the time this sequence is created, the sequence is created so that is  STARTS WITH the integer that is one greater than the  MAX(COLLID) value from the rows in  PS_TXN.

Database Objects Used by the "Row Cache Spill-Over" Feature

The PS_AppModuleName Table

The  PS_AppModuleName table is a storage table for rowsets in an instance of the  AppModuleName application module that have "spilled over". If multiple sessions execute queries that spill over, multiple tables will be created for the application module instances in question with names like  PS_AppModuleName_1,  PS_AppModuleName_2, etc. When an existing table

The table is created using the storage clause  STORAGE (MAXEXTENTS UNLIMITED).

The PS_AppModuleName_kyTable

This table is created to manage key information. It is created the first time a PCOLL row is created with a non empty key. This is the way BC4J persists the view-row-to-entity-row HashMap.

The following indexes are also created on this table:

•  PS_AppModuleName_ki
•  PS_AppModuleName_kj

In addition, a new column named  keycont (of SQL type  BLOB) is added to the corresponding PCOLL storage table.

Cleaning Up the Tables

JDeveloper9i supplies the  bc4jcleanup.sql script in the  ./BC4J/bin directory to help with periodically cleaning up the PCOLL temporary tables. Running the script in SQL*Plus will create the BC4J_CLEANUP PL/SQL package. This package has four procedures:

•  PROCEDURE Session_State( olderThan DATE )

  This procedure cleans-up application module session state storage for sessions older than a given date.

•  PROCEDURE Session_State( olderThan_minutes INTEGER )

  This procedures cleans-up application module session state storage for sessions older than a given number of minutes.

•  PROCEDURE Persistent_Collections( olderThan DATE )

  This procedure cleans-up persistent collection storage for large-rowset "spillover" for collections last accessed before a given date.

•  PROCEDURE Persistent_Collections( olderThan_days NUMBER )

  This procedure cleans-up persistent collection storage for large-rowset "spillover" for collections last accessed a given number of days ago.

You can schedule periodic cleanup of your BC4J temporary persistence storage by submitting an invocation of the appropriate procedure in this package as a database job. You can use an anonymous PL/SQL block like the following to schedule the execution of  bc4j_cleanup.session_state() to run starting tomorrow at 2:00am and each day thereafter to cleanup sessions whose state is over 1 day (1440 minutes) old.

Controlling The Use of Temporary Tables At Runtime

Application Module Pending Changes

The application module pending changes "snapshots" are created automatically by the BC4J Application Module Pool when application modules are checked into the pool in "Stateful Mode".

When the  jbo.dofailover configuration parameter is true (the default), the application module pending state is snapshotted whenever an application module is return to the pool in "Stateful" mode. When  jbo.dofailover is false, then the application module pending state is snapshotted only when high request volume forces the pool to reuse an existing AM instance for a client other than the one that last used it.

If application modules checked in using "Stateless" mode, then there is no need to snapshot the pending state since stateless application modules do not store any pending state across usages.

Also, while generally only used for backward compatibility with JDeveloper 3.x, the use of application modules from the pool in "Reserved" mode does not use the Application Module pending change snapshot facility since the AM instance is dedicated completely to a single client (not conducive to scalable web applications, hence the reason this feature is not widely used in JDeveloper9i any more).

"Row Cache Spill-Over" Storage

The "Row Cache Overview" feature kicks in for a rowset that is caching its queried rows once the number of rows queried exceeds the product of the two configuration parameters:

•  jbo.pers.max.active.nodes (Defaults to 30)
•  jbo.pers.max.rows.per.node (Defaults to 70)

The product of these two parameters represents the maximum number of rows that will be cached in real memory before storing the overflow rows to temporary persistent storage using the PCOLL mechanism to avoid further memory growth. So, by default, this feature will come into play when a rowset has queried 30 * 70 = 2100 rows.

To minimize your use of the "Row Cache Spill-Over" feature, you can:

1. Traverse the rows in your view object in "Forward Only" mode whenever practical.

   Since Forward Only mode avoids view row caching, you never will exceed the limit of rows for those view objects' rowsets.

2. Enforce the use of appropriate query criteria to avoid large result sets.

   If you force the user to provide at least some sensible query criteria, you can avoid the situation of the user querying thousands of rows and scrolling through them start to finish.

To avoid using the "Row Cache Spill-Over" storage feature, you can:

1. Set the Max Fetch Size of your View Object to avoid fetching so many rows.

   You can do this at design time on the "Tuning" panel of the VO Editor, or at runtime with setMaxFetchSize().

2. Set the value of  jbo.pers.max.active.nodes to the value -1

   This effectively disables the feature, but leaves your application vulnerable to potentially large memory use if you have not taken the precautions above to prevent your end user from issuing queries that return thousands of rows and scrolling through them.