Categories
Select Proper Prebuilt Database Service Name While Connecting OAC To ADW
We must choose the optimal database service name for OAC To ADW Connection as this impacts the degree of parallelism and concurrency in OAC.
ADW provides three database service names for connections in the following format:
- databasename_high (Highest resources, lowest concurrency. Queries run in parallel.)
- databasename_medium (Less resources, higher concurrency. Queries run in parallel.)
- databasename_low (Least resources, highest concurrency. Queries run serially.)
These names are contained in the tnsnames.ora file in the Oracle wallet.
The largest number of simultaneous queries that can run for the high database service is three without auto-scaling and nine with auto-scaling enabled. This limit can be reached by three users connected to the high database service name running one query each or three reports in one OAC dashboard for a single user.
The low service name works well for most ADW workloads with OAC, but to utilize parallel queries, select the medium service name.
The parallel degree limit for the low service name is one, meaning no parallelism. If you're connected to the low service name, even if the parallel degree is specified at the table or index level, the degree of parallelism is reduced to one and the query doesn't run in parallel. The parallel degree limit (per query) for medium and high equals two times the number of licensed CPUs.
Hence in OAC, It is recommended that when you define the ADW connection using the instance wallet, make sure to change the name to low or medium to avoid limiting the number of concurrent connections (the high service name is selected by default). It will ensure sufficient concurrency and avoid unnecessary errors.
Refer below blog for more details :
Choosing the proper Oracle Autonomous Data Warehouse database service name for Oracle Analytics Cloud