Initialization Blocks and Usage Tracking

This topic covers best practices for working with initialization blocks and usage tracking in Oracle Analytics.

Enable usage tracking

Once the initial round of optimizing session variable initialization blocks is complete, enable usage tracking for initialization blocks to understand their performance better. See Initialization Block Maintenance.

Table names for usage tracking in Oracle BI Enterprise Edition (OBIEE) are shown in this topic. The actual names can be anything, but they should identify each table's contents, such as PHYSICAL, LOGICAL, or INITBLOCK.

Enable usage tracking in the System Settings and restart the Business Intelligence (BI) Server to start usage tracking. See Usage Tracking Options.

Description of the illustration ceal_usage_tracking_init_block_table.jpg

Monitor initialization blocks

After enabling usage tracking, log in to Oracle Analytics. All session variable initialization blocks not deferred run at login and populate the initialization block usage tracking table.

Log in to SQL Developer, SQL*Plus, or another SQL entry tool. Run the following query against the initialization block usage tracking table using a SESSION_ID obtained from a session log (for example, -1883570176).

SELECT USER_NAME, SESSION_ID, BLOCK_NAME,
TO_CHAR(START_TS,'DD-MM-YYYY HH24:MI:SS') START_TIME,
TO_CHAR(END_TS,'DD-MM-YYYY HH24:MI:SS') END_TIME
FROM USAGE_TRACKING.S_NQ_INITBLOCK
WHERE SESSION_ID = -1883570176
ORDER BY SESSION_ID, START_TS, END_TS

Don't filter using the USER_NAME. Instead, note the START_TIME and a USER_NAME of a row from a session log that you're interested in.

Filter the query using the SESSION_ID associated with the USER_NAME and optionally, the START_TIME.

This query result looks like this:

Description of the illustration ceal_init_block_usage_tracking_query.jpg​

Usage tracking for initialization blocks makes the following three system calls during a user session:

• SIGNING ON
• SIGNED ON
• SIGNED OFF

Note:

If you're not investigating specific login problems, ignore the three blocks named SIGNING ON, SIGNED ON, and SIGNED OFF. SESSION_ID can be a negative number. There are two different USER_NAME values for the same SESSION_ID; BISystemUser and the actual user name.

Initialization blocks can be specified as deferred. These run as needed using the original user name. All session variable initialization blocks not deferred run at login and populate the initialization block usage tracking table. They run using the BISystemUser user name.

The table above shows that the SIGNING ON and SIGNED ON calls take twelve seconds. Then the SIGNED OFF call shows that the user logged off after twenty one minutes of activity. Administrators can see exactly how long things are taking and where corrective action is needed.