Ability to set connection pools at report level (Classic Analysis)

Nishith Jain

Bulk of our reporting is via OAC classic analysis. We have created multiple subject areas that is used for ad-hoc reporting by hundreds of users. We are currently moving our data warehouse from on-prem Oracle 19c to ADW. One of the issues with ADW is that is requires the customer to determine whether the rpd connection pools will connect to ADW via a low connection (No parallel query execution) of a medium connection (customer defined concurrency and parallel execution). We have found some of existing queries run better with once connection vs the other.

While we can create additional connection pools to our rpd (example: one for medium, one for low adw connection) but we do not have a way to switch the report from one connection pool to another. We would like to request a mechanism to override the connection pool in the classic analysis report. Perhaps Oracle can add a box in the advanced tab where one can enter the name of the connection pool (like we can with direct database requests). This way the report will use the default connection pool for the subject area, but will override it with the user specified one if a value is found in the advanced tab.

This is a very important functionality for us because ADW does not have the ability to use the best connection on it's own and requires the OAC developer to figure out which connection is better for different queries. This will help a lot of Oracle's customers that want to move to OAC and ADW.