Use Usage Tracking with Initialization Blocks

This topic covers tips 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 Maintain Initialization Blocks.

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

Monitor Initialization Blocks

After enabling usage tracking, log in to Oracle Analytics. All session variable initialization blocks that aren't deferred run when you log in and populate the initialization block usage tracking table.

Using 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 the USER_NAME of the 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.

The following figure shows the query result in a table.

Description of the illustration ceal_init_block_usage_tracking_query.jpg​

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

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.

You can specify initialization blocks as deferred. These run as needed using the original user name. All session variable initialization blocks that aren't deferred run when a user logs in and populate the initialization block usage tracking table. They run using the BISystemUser user name.