Introduction
Connecting Oracle Analytics Cloud (OAC) to an Oracle Autonomous Data Warehouse (ADW) is described in the documentation, but how do you select the proper prebuilt database service name? This article describes each prebuilt database service name in Oracle Autonomous Data Warehouse (ADW) and offers suggestions for choosing the best one for Oracle Analytics Cloud.
ADW prebuilt database service names
ADW provides three database service names for connections in the following format:
- databasename_high
- databasename_medium
- databasename_low
These names are contained in the tnsnames.ora file in the Oracle wallet. Click Database Connections in the Oracle Cloud Infrastructure Console to see the strings:
Three database service names are described in the documentation as follows:
- HIGH: Highest resources, lowest concurrency. Queries run in parallel.
- MEDIUM: Less resources, higher concurrency. Queries run in parallel.
- LOW: Least resources, highest concurrency. Queries run serially.
Which one is best for OAC?
Consumer Groups in Oracle Resource Manager
The database service names are mapped to consumer groups in the Resource Manager that limit the number of simultaneous connections and queries that can run in ADW at the same time (concurrency) and the maximum number of parallel processes that are allowed per query (parallel_degree_limit). These limits are based on the number of ECPUs or OCPUs licensed and whether auto-scaling is enabled.
The following table shows sample concurrent connection values for a database with 32 ECPUs with ECPU auto-scaling disabled and enabled. The calculations are contained in the documentation.
| Database Service Name | Number of Concurrent Queries with ECPU Auto Scaling Disabled | Number of Concurrent Queries with ECPU Auto Scaling Enabled |
| high | 3 | 9 |
| medium | 8 (.25125 × number of ECPUs) | 24 (.75375 × number of ECPUs) |
| low | Up to 2400 (75 x number of ECPUs) | Up to 2400 (75 x number of ECPUs) |
Choosing the optimal database service name for OAC
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.
Monitoring queued statements
If the maximum number of concurrent queries limit is reached, the excess queries are queued. ADW provides a metric to check for queued statements.
Select Database Actions and Database Dashboard in the Oracle Autonomous Database Warehouse page of Oracle Cloud Infrastructure Console.
Select Performance Hub, and select SQL Monitor Tab to see the queued statement status, which is displayed as a grey clock. In this example, three queries are running with the high service name, one is queued, and one query is running with the medium service name. The queued statement executes when one of the three queries running with the high service name completes.
Monitoring parallelism
If the parallel degree limit is exceeded, you see the degree of parallelism (DOP) downgrade in the SQL monitor report. The degree of parallelism downgrade reason of 353 means that the Resource Manager downgraded the statement due to the maximum degree of parallelism limit.
For Oracle Database version 18 and higher, the downgrade reason codes are described in the following table:
| ID |
Reason Codes |
| 352 |
DOP downgrade due to adaptive DOP |
| 353 |
DOP downgrade due to resource manager max DOP |
| 354 |
DOP downgrade due to insufficient number of processes |
| 355 |
DOP downgrade because slaves failed to join |
Resource Manager CPU Wait Event
A session waiting to be allocated CPU by the Resource Manager increments the resmgr:cpu quantum wait event. To reduce the occurrence of this wait event, verify that the low or medium service name is being used for the OAC connection or increase the number of CPUs allocated to ADW.
To see the number of waits and the average wait time, review Foreground Wait Events in the Automatic Workload Repository (AWR) report for the resmgr:cpu quantum wait event.
In this example, there were a total of 272 waits, waiting on average 588.91 milliseconds each for a total wait time of 160 seconds. It was determined that the reason was that the high database service name was being used for the OAC connection. These wait periods disappeared once the customer switched to the medium service, and the periodic slowness of their dashboard was resolved.
Tip when creating a connection to ADW in OAC
In OAC, when you define the ADW connection using the instance wallet, the high service name is selected by default. Be sure to change the name to low or medium to avoid limiting the number of concurrent connections.
Call to action
Become familiar with the various prebuilt ADW database service names and how the choice impacts the degree of parallelism and concurrency in OAC. It’s recommended to use the low or medium service name to ensure sufficient concurrency and avoid unnecessary errors.