KB485011: Best practice for increasing performance when publishing Intelligent cubes against Oracle with Gateway Agent enabled and disabled

Description 

Starting in Strategy 2021 Update 1, you can connect to the on-premise database from the Strategy cloud via Gateway Agent. 

However, Intelligent cube publishing performance may be affected with the agent enabled. 
The following table summarizes the test results of some test scenarios against Oracle 19c.
When using the default array size of 60,000 (60KB) and publishing an Intelligent cube with a cube size of 1.5 GB, the Data Transfer from Datasource(s) time is 77 seconds with the agent disabled. However, with the agent enabled, the time increases to 659 seconds. 
To improve the cube publish performance for Oracle, we tried using different ArraySize values and found a more suitable range against the use case. 
The following line chart illustrates the test results. 

The green line represents the Data Transfer from Datasource(s) time against different array sizes with agent disabled. 
The red line represents the Data Transfer from Datasource(s) time against different array sizes with agent enabled. 
From the test results, we know the following: 
 

• For agent disabled, the data transfer time is better when the array size is in the range of 800KB to 800MB. The performance improved 200% compared to the default value (60KB) and the max value (4GB).
• For agent enabled, the data transfer time is better when the array size is in the range of 8MB to 800MB. The performance with agent enabled is almost the same as having agent enabled.
• Other gateways may also have a similar driver option which has a similar function as the array size of Oracle, such as fetch size of PostgreSQL and MaxRespSize of Teradata.
• The value of the driver option may need to be adjusted according to the real usage scenario. Please try using different values and find the most suitable one.

Test Configuration 

• The instance type of the Strategy Intelligence server is 32 vCPUs 256 GiB of memory
• The test gateway is Oracle 19c with 32 CPU 244 GiB of memory
• The test cube size if approximately  2.5 GB. 
• The Data Transfer from Datasource(s) time is the time that is affected the most by the agent. The statistics can be found in Administration > System Monitors > Caches > Intelligent Cubes. Right-click Intelligence Cube Report and choose Quick View.