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.

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.

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.

Tune Initialization Blocks in Oracle Analytics Server (OAS)

This section applies only to Oracle Analytics Server.

A significant change to Oracle Analytics in 2022 is that session variable initialization blocks are run in parallel. The NUM_INIT_BLOCK_THREADS_PER_USER parameter governs the number of initialization block threads that can run in parallel. It's defined in the SECURITY section of the NQSCONFIG.INI file, as shown in this example:

[SECURITY]
NUM_INIT_BLOCK_THREADS_PER_USER = 4;

NUM_INIT_BLOCK_THREADS_PER_USER specifies the number of parallel threads used to run initialization blocks for a user. The default value is 4, sufficient if the initialization blocks are optimized. Increase the NUM_INIT_BLOCK_THREADS_PER_USER value to 8 and restart the BI Server if session variable initialization blocks take longer than expected.

The maximum value is 16.

Be sure to optimize initialization blocks for all instances to provide the best log in and visualization experience. Once optimized, initialization block usage tracking can assist in pinpointing bottlenecks in your instances. See NQSConfig.INI File Configuration Settings.

For more information on usage tracking for Oracle Analytics Server, see Track Usage.