An Oracle technical white paper
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.
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:
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.
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:
Name of the persistent collection storage table. For example, values in the
TABNAME column might be like
PS_Mypackage9Module, etc., as described below.
Timestamp of when this row was created in
Timestamp of when the persistent collection storage table named in the
TABNAME column was created.
Timestamp of when the most recent persistent collection operation was performed successfully on the storage table named in
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
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
table is a storage table for rowsets in an instance of the
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
, etc. When an existing table
The table is created using the storage clause
STORAGE (MAXEXTENTS UNLIMITED).
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:
In addition, a new column named
keycont (of SQL type
BLOB) is added to the corresponding PCOLL storage table.
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.
SET SERVEROUTPUT ON
-- Start the job tomorrow at 2am
firstRun := TO_DATE(TO_CHAR(SYSDATE+1,'DD-MON-YYYY')||' 02:00',
-- Submit the job, indicating it should repeat once a day
dbms_job.submit(job => jobId,
-- Run the BC4J Cleanup for Session State
-- to cleanup sessions older than 1 day (1440 minutes)
what => 'bc4j_cleanup.session_state(1440);',
next_date => firstRun,
-- When completed, automatically reschedule
-- for 1 day later
interval => 'SYSDATE + 1'
dbms_output.put_line('Successfully submitted job. Job Id is '||jobId);
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".
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).
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:
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.
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:
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().
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.