1. Start with Performance Hub
Open Database → Performance Hub. Check:
- Average Active Sessions by wait class. Lots of CPU means compute-bound. Lots of Scheduler means sessions queue for resources (the service or CPU limit).
- Top SQL by database time over the problem window.
- SQL Monitoring for long-running statements, showing where time is spent in the plan.
2. Queuing vs CPU
-- Sessions waiting on the resource manager (queued)
SELECT consumer_group_name, current_queued_sessions, queued_time, cpu_wait_time
FROM v$rsrc_consumer_group
ORDER BY queued_time DESC;
current_queued_sessions > 0 on HIGH or MEDIUM means statements wait for a concurrency slot. Each service allows a different amount of concurrency:
| Service | Concurrency | Use for |
|---|---|---|
tpurgent / tp | High | OLTP application sessions |
low | High, little parallelism | Reports, batch with many small queries |
medium | Limited | Medium analytics |
high | Very limited, uses full parallelism | A few big analytic queries |
An application pointed at _high is the most common cause of "the database is slow but CPU isn't high".
3. Scale compute
# Manual: change the ECPU count (online)
oci db autonomous-database update --autonomous-database-id $ADB --compute-count 16
# Auto scaling: use up to 3x the base ECPUs when needed
oci db autonomous-database update --autonomous-database-id $ADB --is-auto-scaling-enabled true
Auto scaling helps with bursts. A sustained load above the base ECPU count means you should raise the base instead.
4. Find and fix bad SQL
SELECT sql_id, executions, ROUND(elapsed_time/1e6) AS elapsed_s, ROUND(cpu_time/1e6) AS cpu_s,
buffer_gets, SUBSTR(sql_text, 1, 80) AS sql_text
FROM v$sqlstats
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>', NULL, 'ALLSTATS LAST'));
- Automatic indexing can be turned on for OLTP workloads:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT'); - Check for plan changes after statistics refreshes. SQL plan management can pin a good plan.
- Make sure the app uses bind variables. Thousands of unique literal statements waste CPU on parsing.
5. Storage and IO
Autonomous Database storage grows automatically, but check the storage chart for unexpected growth (for example, a forgotten staging table). Smart Scan handles large scans well. Many small random reads on unindexed lookups are still a SQL problem.
Tip
Before you scale up permanently, check the app's connection service and pool size. Fixing the service mapping is free; extra ECPUs aren't.