Initialization Block Maintenance

When you use Data Gateway or Remote Data Connector with Oracle Analytics Cloud there is a small overhead on the duration of each query execution.

You won’t notice the additional duration when you run a report because it’s less than 0.3 seconds. However, you might experience performance issues if you run lots of (the number depends on your performance requirements) initialization blocks whenever someone signs-in because initialization block queries are executed serially. Using the deferred option for initialization blocks doesn’t fix the issue, because initialization blocks are executed when you open the first dashboard page.

This is an issue if you use Oracle BI Applications, because it runs more than two hundred initialization blocks. For Oracle Analytics Cloud, the best way to avoid these performance issues is to reduce the number of initialization blocks.

Here are our recommendations on how you can reduce the number of session initialization blocks:

Disable all the initialization blocks that you don’t really need.
For example, in Oracle BI Applications, disable initialization blocks that refer to Oracle BI Applications modules that you don’t use anymore.
Except when there is precedence rule, merge all the row_wise initialization blocks that use the same connection pool and return the same data type using UNION ALL between their queries.
For example:
```
Init block 1: query1

Init block 2: query2

Merged init block: query1 union all query2
```
Disable all the initialization blocks that select a hardcoded value from dual or W_DUAL_G, and put the hardcoded value in default initializer of the corresponding variable.
Merge the remaining initialization blocks that select data from dual into a single select statement.
For Oracle BI Applications customers, disable all the initialization blocks used to retrieve Oracle Human Capital Management custom attributes name and values if the corresponding attribute isn’t used. (if the standard default value ‘HIDE’ is the current value for these variables) or if you don’t use Oracle Human Capital Management as an Oracle BI Applications data source (there are one hundred initialization blocks like that in Oracle BI Applications):
```
HR xxx Attribute yyy
```
Merge together all the remaining initialization blocks that aren’t row_wise and use the same connection pool. For example:
Init block 1 is based on query1: select colA from tableA where….

Init block 2 is based on query2: select colB from tableB where….

You can merge them into a single initialization block using a query such as:
```
Select MAX(colA), MAX(colB) from (

select cola as cola, null as colB from tableA where….

Union all

Select null, colB from tableB where…) tmp
```

You can do as many unions as required to retrieve all the variables from the same connection pool in one single query.

This isn’t easy to implement or maintain, and you risk making mistakes when you create the queries and assign all the variables to a single initialization block. If you carefully implement and maintain your initialization block queries and variables, you can significantly reduce the time it takes to sign-in and display the first dashboard page.