Selecting an Oracle Autonomous Data Warehouse Database Service Name
Selecting the correct prebuilt database service name is key to connecting to Oracle Autonomous Data Warehouse. Learn about the different prebuilt database service names and which one you should choose.
What are the prebuilt database service names?
databasename_high- Highest resources, lowest concurrency. Queries run in parallel.databasename_medium- Fewer 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. Click Database Connections in the Oracle Cloud Infrastructure Console to see the strings.
Description of the illustration adw1.png
About 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 Oracle Autonomous Data Warehouse 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.
| 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 | 20 (.63 × number of ECPUs) | 60 (1.89 × number of ECPUs) |
| low | Up to 4800 (150 x number of ECPUs) | Up to 4800 (150 x number of ECPUs) |
Choosing the Optimal Database Service Name for Oracle Analytics
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 Oracle Analytics dashboard for a single user.
The low service name works well for most Oracle Autonomous Data Warehouse workloads with Oracle Analytics, 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.
Note: Connecting to a database that's part of Oracle Fusion Analytics Warehouse (Fusion Analytics) requires using the low service name to allow for the maximum number of concurrent queries.
Monitoring queued statements
If the maximum number of concurrent queries limit is reached, the excess queries are queued. Oracle Autonomous Data Warehouse provides a metric to check for queued statements.
Select Database Actions and Database Dashboard in the Oracle Autonomous Data Warehouse page of Oracle Cloud Infrastructure Console.
Description of the illustration adw2.png
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.
Description of the illustration adw3.png
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.
Description of the illustration adw4.png
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 Oracle Autonomous Data Warehouse in Oracle Analytics
In Oracle Analytics, when you define the Oracle Autonomous Data Warehouse connection using the instance wallet, the high service name is selected by default. Change the name to low or medium to avoid limiting the number of concurrent connections.
