Performance Tuning Oracle Databases on Azure: Best Practices, Tools, and Optimization Techniques
Performance Tuning Oracle Databases on Azure
Article Overview
Most performance work fails for the same reason: it starts with a guess. Someone adds an index, bumps a memory parameter, or throws more CPU at a database because those are the levers within reach — before knowing what the database was actually waiting on. This guide takes the opposite approach. It starts from the evidence — wait events, AWR, and ASH — and only then moves to the fix, whether that is CPU and ECPU sizing, Exadata-aware storage design, indexing, or rewriting a bad statement. Everything is code-first: the queries to find the problem, read the report, and prove the fix. It is written for Oracle Database@Azure specifically, where the Exadata storage engine changes several tuning instincts you brought from on-premises, but the method applies to any Oracle database.
⚠️ Before you run anything
Every query below is illustrative and safe to read from, but some (SQL Tuning Advisor, plan baselines, gathering stats) change database state. Run them on a non-production copy first, understand what each one does, and note that AWR and ASH require the Diagnostics Pack — check your licensing. Placeholders like schema and SQL_ID values must be replaced with your own.
1.A Method, Not a Guess
The difference between a senior DBA and a lucky one is method. Random changes sometimes work, but you cannot repeat them, and you cannot explain them. A disciplined tuning loop looks the same every time and always begins with a question the database can answer for itself: what were you waiting on?
Tuning is not about knowing every parameter. It is about asking the database where its time goes, fixing the biggest consumer, and proving the fix — then doing it again.
The one rule that prevents most bad tuning
Change one thing at a time and measure between changes. Bundling five changes into one deployment feels efficient and is the reason nobody can ever explain why performance moved. If a change does not shrink the wait you targeted, revert it — a change that does not help is not neutral, it is added risk and complexity for nothing.
2.Start With Wait Events
A database session is either working (on CPU) or waiting (for I/O, a lock, a latch, a log flush). The wait interface tells you exactly which, and that single fact points at the fix faster than anything else. Before touching a parameter, ask what the database is waiting on right now.
2.1 What is happening this second
-- Every active session, what it is doing, and what it waits on
SELECT s.sid, s.serial#, s.username, s.status,
s.sql_id, s.event, s.wait_class,
s.seconds_in_wait AS secs
FROM v$session s
WHERE s.status = 'ACTIVE'
AND s.type = 'USER'
AND s.username IS NOT NULL
ORDER BY s.wait_class, secs DESC;2.2 Where time has gone, by wait class
Wait classes group the noise into a handful of categories. If the dominant class is User I/O, you have a storage or SQL problem; if it is Concurrency, a contention problem; if most time is on CPU, a parsing or logic problem. This one query orients the whole investigation.
-- Proportion of DB time spent in each wait class since startup
SELECT wait_class,
ROUND(time_waited/100) AS seconds,
ROUND(100 * time_waited /
SUM(time_waited) OVER (), 1) AS pct
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle')
ORDER BY time_waited DESC;2.3 A field guide to the common waits
| Wait event | Class | Usually means |
|---|---|---|
cell smart table scan | User I/O | Exadata Smart Scan is offloading a full scan — often a good sign, not a problem |
cell single block physical read | User I/O | Index-driven single-block reads; heavy volume can mean a chatty plan or missing index |
direct path read | User I/O | Large scans bypassing the buffer cache — expected for big analytic queries |
log file sync | Commit | Sessions waiting on commit; often too-frequent commits or redo throughput |
enq: TX - row lock contention | Application | Sessions blocking each other on the same rows — an application design issue |
buffer busy waits / gc buffer busy | Concurrency | Hot blocks; in RAC, cross-instance contention on the same block |
library cache: mutex X | Concurrency | Parsing pressure, frequently from literal SQL that should be using bind variables |
The Exadata twist on wait analysis
On Exadata, seeing cell smart table scan dominate is often good — it means the storage cells are filtering data before it reaches the database nodes, which is exactly what you are paying for. The instinct from on-premises to eliminate full scans by adding indexes can be actively wrong here: a full scan that offloads is frequently faster than an index range scan that does not. Read the wait in the context of the platform, not the habit.
3.Reading an AWR Report
The Automatic Workload Repository takes periodic snapshots of the database's state; an AWR report is the diff between two of them. It is the single most useful artifact in Oracle performance work — but only if you read it in the right order. Most people open one and drown. Here is the order that turns it into a diagnosis.
3.1 Generate a report between two snapshots
-- Find the snapshot IDs around the problem window
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN
TIMESTAMP '2026-07-27 09:00:00' AND TIMESTAMP '2026-07-27 11:00:00'
ORDER BY snap_id;
-- Take a manual snapshot on demand if you need a fresh boundary
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
-- Generate the HTML report between two snap IDs (run via SQL*Plus)
-- @?/rdbms/admin/awrrpt.sql
-- ... or programmatically for a specific range:
SELECT output FROM TABLE(
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid => (SELECT dbid FROM v$database),
l_inst_num => 1,
l_bid => 4820, -- begin snap
l_eid => 4824)); -- end snap3.2 The reading order that works
- DB Time vs elapsed time (top of report). If DB Time hugely exceeds elapsed wall-clock time, many sessions were active at once — a busy system. If DB Time is low, the database is not your bottleneck; look at the application or network.
- Top Timed Events. This is the heart of it — the events consuming the most DB time, ranked. Your tuning target is almost always at the top of this list.
- Load Profile. Per-second and per-transaction rates — logical reads, physical reads, parses, commits. Spikes here explain the top events.
- SQL sections. SQL ordered by elapsed time, by CPU, by reads. This is where a single bad statement reveals itself.
- Instance efficiency and advisories. Useful context, but do not start here — a 99% buffer hit ratio tells you nothing about whether the right work is being done.
3.3 Query the AWR history directly
You do not always need the HTML report. The DBA_HIST views let you pull exactly what you want — here, the top SQL by elapsed time across a window.
-- Top SQL by total elapsed time between two snapshots
SELECT sql_id,
ROUND(SUM(elapsed_time_delta)/1e6, 1) AS elapsed_s,
SUM(executions_delta) AS execs,
ROUND(SUM(elapsed_time_delta)/1e6 /
NULLIF(SUM(executions_delta),0), 3) AS s_per_exec,
ROUND(SUM(buffer_gets_delta) /
NULLIF(SUM(executions_delta),0)) AS gets_per_exec
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 4820 AND 4824
GROUP BY sql_id
ORDER BY SUM(elapsed_time_delta) DESC
FETCH FIRST 15 ROWS ONLY;-- Top foreground wait events across the same window
SELECT event_name,
ROUND(SUM(time_waited_micro_delta)/1e6, 1) AS waited_s,
SUM(total_waits_delta) AS waits
FROM dba_hist_system_event
WHERE snap_id BETWEEN 4820 AND 4824
AND wait_class <> 'Idle'
GROUP BY event_name
ORDER BY SUM(time_waited_micro_delta) DESC
FETCH FIRST 12 ROWS ONLY;Read Top Timed Events first, always
Ninety percent of AWR analysis is: open the report, go straight to Top Timed Events, identify the largest consumer of DB time, then jump to the SQL section to find the statement responsible. Everything else is supporting evidence. Resist the urge to read top to bottom — the instance-efficiency percentages near the top are the least actionable numbers in the whole report.
⚠️ Ratios lie; time does not
The classic trap is tuning to a ratio — buffer cache hit ratio, parse ratio — instead of to time. A database can have a 99.9% hit ratio and still be slow because it is doing millions of unnecessary logical reads very efficiently. Always tune to where DB time is spent, never to a percentage that feels like it should be higher.
4.ASH — the Last Hour in Detail
AWR aggregates over a snapshot interval, which is perfect for trends and useless for "what happened at 10:47 when everything froze for ninety seconds." Active Session History takes a snapshot of what every active session is doing once a second, which lets it rebuild a brief, sharp incident that AWR would simply average out of existence.
4.1 What was the database doing during the spike?
-- Top wait events during a specific five-minute window
SELECT event,
COUNT(*) AS samples,
ROUND(100 * COUNT(*) /
SUM(COUNT(*)) OVER (), 1) AS pct
FROM v$active_session_history
WHERE sample_time BETWEEN
TIMESTAMP '2026-07-27 10:45:00' AND TIMESTAMP '2026-07-27 10:50:00'
GROUP BY event
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;4.2 Which SQL and which objects were responsible
-- The SQL burning the most active samples in the window
SELECT h.sql_id,
COUNT(*) AS samples,
ROUND(SUM(CASE WHEN session_state='ON CPU' THEN 1 ELSE 0 END)
* 100 / COUNT(*), 1) AS pct_on_cpu
FROM v$active_session_history h
WHERE sample_time > SYSDATE - INTERVAL '30' MINUTE
AND sql_id IS NOT NULL
GROUP BY h.sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;
-- The objects behind those waits (hot segments)
SELECT o.owner, o.object_name, o.object_type,
COUNT(*) AS samples
FROM v$active_session_history h
JOIN dba_objects o ON o.object_id = h.current_obj#
WHERE sample_time > SYSDATE - INTERVAL '30' MINUTE
AND h.current_obj# > 0
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;When to reach for ASH instead of AWR
Use AWR for "the batch window is 20 minutes slower than last week" — a trend across time. Use ASH for "users saw a freeze at 10:47" — a specific, short event. ASH is also the right tool when a problem has already passed: the samples are retained in memory and flushed to DBA_HIST_ACTIVE_SESS_HISTORY, so you can investigate after the fact without having reproduced it.
5.CPU and ECPU Optimization
On Oracle Database@Azure, CPU is provisioned as ECPUs (or OCPUs on some shapes), and understanding the unit matters because it is what you scale and what you pay for. Before adding any, though, the discipline is the same as everywhere else: make sure you have a genuine CPU problem, not a self-inflicted one.
5.1 The ECPU unit and elastic scaling
An ECPU is Oracle's elastic unit of compute; as a rule of thumb, four ECPUs line up with a single OCPU. The advantage of the model on this platform is that you can add ECPUs to a VM cluster online, without downtime — so a proven CPU shortage is a fast fix, and you are not forced to over-provision "just in case."
| Concept | What it is | Why it matters for tuning |
|---|---|---|
| OCPU | An Oracle CPU unit (physical core equivalent) | The traditional sizing unit on some shapes |
| ECPU | Oracle's elastic CPU unit; roughly four ECPUs to one OCPU | Fine-grained, online scaling — add capacity without downtime |
| Online scaling | Add/remove ECPUs on a running cluster | A verified CPU bottleneck is a minutes-long fix, not a maintenance window |
5.2 Do you actually have a CPU problem?
Before scaling, confirm the CPU is doing necessary work. Two databases can both peg the CPU — one because it is genuinely busy, one because it re-parses the same statement a million times. Only the first is fixed by adding cores.
-- How much DB time is CPU vs waiting? (high CPU% = compute-bound)
SELECT stat_name, ROUND(value/1e6, 1) AS seconds
FROM v$sys_time_model
WHERE stat_name IN ('DB time','DB CPU','background cpu time')
ORDER BY value DESC;
-- Parse vs execute: heavy parsing burns CPU doing no useful work
SELECT name, value FROM v$sysstat
WHERE name IN ('parse count (total)','parse count (hard)',
'execute count','CPU used by this session');
-- If hard parses are a large fraction of executions, you have a
-- bind-variable problem, not a CPU-capacity problem. Fix the SQL first.5.3 Control CPU with Resource Manager before buying more
Often the issue is not too little CPU but the wrong work consuming it — a reporting query starving the transactional workload. Resource Manager lets you allocate CPU by consumer group so critical work is protected under load, frequently removing the need to scale at all.
-- Give OLTP 70% and reporting 30% of CPU when the system is saturated
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => 'DAYTIME_PLAN', comment => 'Protect OLTP under load');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'DAYTIME_PLAN', group_or_subplan => 'OLTP_GROUP',
comment => 'Transactional', mgmt_p1 => 70);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'DAYTIME_PLAN', group_or_subplan => 'REPORT_GROUP',
comment => 'Reporting', mgmt_p1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'DAYTIME_PLAN', group_or_subplan => 'OTHER_GROUPS',
comment => 'Everything else', mgmt_p1 => 0, mgmt_p2 => 100);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAYTIME_PLAN';Scale as the last step, not the first
Adding ECPUs online is genuinely easy on this platform — which is exactly why it is tempting to reach for it before doing the work. The right order is: eliminate wasted CPU (bind variables, bad plans, unnecessary logical reads), then protect critical work with Resource Manager, and then, if the CPU is genuinely and fully used on necessary work, scale up. Scaling to paper over a parsing problem just means paying more to run inefficient code faster.
6.Storage Design on Exadata
This is where tuning on Oracle Database@Azure diverges most from what you knew on-premises or on a generic cloud VM. The storage is Exadata, and Exadata is not passive disk — the storage cells run code. Several classic tuning moves become unnecessary, and a few become counterproductive. Design with the platform, not against it.
6.1 Smart Scan — let the storage do the work
With Smart Scan, the row filtering and column selection happen inside the storage cells themselves — a full table scan hands back just the rows and columns the query asked for, rather than moving entire blocks up to the database nodes and filtering there. This is why, on Exadata, a full scan is often the fast path — the opposite of the on-premises instinct.
-- Is Smart Scan actually happening? Compare bytes eligible vs returned
SELECT name, value/1024/1024 AS mb
FROM v$sysstat
WHERE name IN (
'cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes returned by smart scan',
'cell physical IO interconnect bytes');
-- A large "eligible" with a much smaller "returned by smart scan"
-- means the cells are filtering effectively — offload is working.
-- Per-SQL: confirm a statement is offloading
SELECT sql_id, child_number, io_cell_offload_eligible_bytes,
io_interconnect_bytes, io_cell_offload_returned_bytes
FROM v$sql
WHERE sql_id = '&your_sql_id';6.2 Storage indexes and HCC
Two more Exadata features change the storage calculus:
- Storage indexes are automatic in-memory structures the cells maintain; they remember the range of values held in each region of storage and skip any region whose range cannot satisfy the predicate. You do not create or manage them — but well-clustered data makes them far more effective, which is a reason to care about load order.
- Hybrid Columnar Compression (HCC) stores data column-major in compression units, giving large compression ratios for data that is queried more than it is updated. For archival and analytic tables it cuts both storage and the volume of I/O a scan must do.
-- Apply HCC to a large, scan-heavy, rarely-updated table
ALTER TABLE sales.fact_orders_archive
MOVE COLUMN STORE COMPRESS FOR QUERY HIGH;
-- QUERY LOW / QUERY HIGH -> favour scan speed (warehouse queries)
-- ARCHIVE LOW / ARCHIVE HIGH -> favour maximum compression (cold data)
-- Check the compression type actually in effect
SELECT table_name, compression, compress_for
FROM dba_tables
WHERE owner = 'SALES' AND table_name = 'FACT_ORDERS_ARCHIVE';6.3 ASM disk groups
Storage is presented through Automatic Storage Management, typically with +DATA for datafiles and +RECO for the fast recovery area. ASM spreads I/O evenly across the storage automatically, so the on-premises art of hand-placing datafiles on specific spindles is gone — another manual tuning task the platform removes. Keep an eye on free space and imbalance rather than placement.
-- Disk-group free space and any rebalance in progress
SELECT name, state, type,
ROUND(total_mb/1024) AS total_gb,
ROUND(free_mb/1024) AS free_gb,
ROUND(100*(total_mb-free_mb)/total_mb, 1) AS pct_used
FROM v$asm_diskgroup
ORDER BY name;🔧 The on-premises habits to unlearn
On Exadata you generally stop doing several things you used to: hand-placing datafiles (ASM balances I/O), adding indexes purely to avoid full scans (Smart Scan makes scans cheap), and micromanaging buffer cache for large scans (they use direct-path reads and the cells do the filtering). The mental shift is from "avoid I/O" to "let the storage eliminate I/O for you."
7.Indexing That Earns Its Keep
Indexes are not free. Every index speeds some reads and slows every insert, update, and delete that touches its columns, and it consumes space and buffer cache. On Exadata the bar is even higher, because Smart Scan makes full scans cheap enough that some indexes you would have created on-premises are no longer worth their maintenance cost. The goal is the smallest set of indexes that serves the access patterns you actually have.
7.1 When an index helps — and when a scan wins
| Access pattern | Usually best |
|---|---|
| Fetch a few rows by a selective key (OLTP lookup) | B-tree index — a scan would waste effort |
| Aggregate over a large fraction of a big table | Full scan with Smart Scan — an index would be slower |
| Low-cardinality columns in a data warehouse | Bitmap index (read-mostly tables only) |
| Multi-column filter always used together | Composite index in the right column order |
| Query covered entirely by indexed columns | Covering composite index — avoids table access altogether |
7.2 Find the indexes you do not need
Unused indexes are pure cost. Turn on monitoring, let a full business cycle run, then drop the ones nothing touched.
-- Ask Oracle to track index usage (19c+ populates this automatically)
SELECT u.name AS index_name, u.total_access_count,
u.total_exec_count, u.last_used
FROM dba_index_usage u
JOIN dba_indexes i ON i.index_name = u.name
WHERE i.owner = 'SALES'
ORDER BY u.total_access_count; -- zero access over a full cycle = candidate to drop
-- Before dropping, make it invisible and confirm nothing regresses
ALTER INDEX sales.idx_orders_legacy INVISIBLE;
-- ... run the workload; if nothing slows down over a full cycle ...
DROP INDEX sales.idx_orders_legacy;7.3 Test a new index safely with invisibility
Invisible indexes are the safest way to add one: the index is maintained but the optimizer ignores it until you opt in, so you can test its effect on one session without risking the whole system.
-- Create the index invisible so it cannot affect production plans yet
CREATE INDEX sales.idx_orders_cust_date
ON sales.orders (customer_id, order_date) INVISIBLE;
-- Try it in your session only
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
-- ... run the target query, check the plan uses it and improves ...
-- Happy? Make it visible for everyone:
ALTER INDEX sales.idx_orders_cust_date VISIBLE;⚠️ The index that made everything slower
Adding an index to speed one query can slow a hundred writes and even push the optimizer into a worse plan elsewhere. On a heavily-inserted table, a new index is a tax on every insert forever. Always weigh the read you are speeding against the writes you are slowing, test with invisibility first, and on Exadata ask the extra question: would Smart Scan have made a full scan fast enough to not need this index at all?
8.SQL Tuning in Practice
Most database performance problems are a handful of bad statements, not a mis-set parameter. Find them, understand their plan, and fix the cause — usually a missing bind variable, a stale statistic, or a query written in a way the optimizer cannot handle well.
8.1 See the real execution plan
EXPLAIN PLAN shows what the optimizer would do; DBMS_XPLAN.DISPLAY_CURSOR shows what it actually did, including real row counts versus estimates — which is where most plan problems reveal themselves.
-- Run the query with hints to capture actual execution statistics
SELECT /*+ GATHER_PLAN_STATISTICS */
customer_id, SUM(amount)
FROM sales.orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id;
-- Then show the plan with estimated vs ACTUAL rows side by side
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
-- The tell: a step where E-Rows (estimated) and A-Rows (actual) differ
-- by orders of magnitude means the optimizer is working from bad
-- cardinality — usually stale or missing statistics.8.2 The two most common root causes
Missing bind variables. Literal SQL (WHERE id = 12345 then WHERE id = 12346) forces a hard parse for every value, flooding the shared pool and burning CPU. Bind variables let one parsed plan serve every execution.
-- Symptom: many near-identical statements differing only in literals
SELECT sql_text, executions
FROM v$sql
WHERE sql_text LIKE 'SELECT%FROM sales.orders WHERE order_id =%'
FETCH FIRST 5 ROWS ONLY;
-- If you see hundreds of these differing only by number, that is the problem.
-- Fix in application code: use a bind, not a literal
-- Bad : "SELECT ... WHERE order_id = " + id
-- Good: "SELECT ... WHERE order_id = :id" (then bind id)
-- Emergency-only server-side mitigation (prefer fixing the app):
ALTER SYSTEM SET cursor_sharing = FORCE; -- use with caution, test firstStale statistics. The optimizer plans from statistics; if they no longer reflect the data, it makes bad choices. Refresh them when data has shifted significantly.
-- Which tables have stale stats?
SELECT table_name, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner = 'SALES' AND stale_stats = 'YES';
-- Regather for one table with a good sample and histograms as needed
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SALES',
tabname => 'ORDERS',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => 8);
END;
/8.3 Let the SQL Tuning Advisor propose a fix
For a genuinely tricky statement, the SQL Tuning Advisor analyses it and can recommend a better plan, an index, or a SQL profile. Treat its output as advice to review, not a command to obey.
DECLARE
t VARCHAR2(64);
BEGIN
t := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'a1b2c3d4e5f6g',
task_name => 'tune_orders_agg');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_orders_agg');
END;
/
-- Read the recommendations before applying anything
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_orders_agg') FROM dual;Fix the cause, not the plan
It is tempting to force a good plan with a hint or a profile and move on. Sometimes that is right — but first ask why the optimizer chose badly. Usually it is stale statistics or literal SQL, and fixing that repairs a whole class of statements at once, not just the one in front of you. A hint fixes one query; fresh statistics fix the reason a hundred queries were about to go wrong.
9.Plan Stability
The worst performance incidents are often not slow queries but suddenly slow queries — a statement that ran in fifty milliseconds for a year and now takes thirty seconds because the optimizer picked a new plan overnight. SQL Plan Management (SPM) exists to stop that: it lets you lock in known-good plans so the optimizer can propose new ones but cannot silently adopt a worse one.
9.1 Capture and fix a good plan as a baseline
-- Load the current (good) plan for a statement as an accepted baseline
DECLARE
n PLS_INTEGER;
BEGIN
n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'a1b2c3d4e5f6g');
DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || n);
END;
/
-- See baselines and their status for a statement
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%sales.orders%';
-- New plans the optimizer finds are captured but NOT used until you
-- verify they are at least as fast and mark them accepted (evolve).
-- To pin one plan and reject all others, mark it FIXED:
-- DBMS_SPM.ALTER_SQL_PLAN_BASELINE(..., attribute_name=>'FIXED', attribute_value=>'YES');Baselines before a big change
The highest-value time to capture baselines is right before something that could move plans: a database upgrade, a major stats refresh, or an optimizer parameter change. Lock the plans that work today, make the change, and let SPM protect you from the handful of statements that would otherwise regress — while still allowing genuinely better plans to be adopted after verification.
10.Memory and PGA
Memory tuning on Exadata is less hands-on than it once was, but two things still reward attention: giving the SGA enough room to avoid needless physical reads, and giving the PGA enough room to keep sorts and hash joins in memory instead of spilling to temp.
10.1 Are sorts and hashes spilling to disk?
A sort or hash join that does not fit in the PGA spills to temporary storage — a multi-pass operation that is dramatically slower than a single-pass, in-memory one. This is one of the highest-value things to check on an analytic workload.
-- One-pass and multi-pass executions are the ones spilling to temp
SELECT name, value
FROM v$sysstat
WHERE name LIKE 'workarea executions%';
-- PGA advice: what cache hit % you would get at different PGA sizes
SELECT ROUND(pga_target_for_estimate/1024/1024) AS pga_mb,
estd_pga_cache_hit_percentage AS hit_pct,
estd_overalloc_count AS overalloc
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;
-- Sessions currently using large work areas (active sorts/hashes)
SELECT s.sid, s.username, w.operation_type,
ROUND(w.actual_mem_used/1024/1024,1) AS mem_mb,
w.tempseg_size/1024/1024 AS temp_mb
FROM v$sql_workarea_active w
JOIN v$session s ON s.sid = w.sid
ORDER BY w.actual_mem_used DESC;10.2 SGA sizing from evidence
-- Buffer cache advice: estimated physical reads at different cache sizes
SELECT size_for_estimate AS cache_mb,
estd_physical_read_factor AS read_factor,
estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT' AND block_size = 8192
ORDER BY size_for_estimate;
-- If a larger cache barely reduces reads (read_factor flattens),
-- adding SGA will not help — the reads are necessary, tune the SQL instead.Let PGA spills, not hit ratios, guide memory work
The single most useful memory signal is work-area spills: if large sorts and hash joins are going multi-pass to temp, more PGA yields a real, measurable speed-up. Chasing an already-high buffer cache hit ratio, by contrast, almost never does. Tune memory where the advisories show a real reduction in work, not where a ratio merely looks improvable.
11.Ongoing Monitoring
Tuning is not a project you finish; it is a property you maintain. The goal of monitoring is to see a regression as a trend before users see it as an outage — and on this platform that means combining Oracle's own diagnostics with the Azure monitoring stack the rest of your estate already uses.
| Layer | What to watch, and with what |
|---|---|
| In-database, real time | Active sessions and waits via v$session, v$active_session_history — the live pulse |
| In-database, historical | AWR trends, SQL performance over time via DBA_HIST views — is the batch window creeping up? |
| Automatic diagnosis | ADDM findings on each snapshot — Oracle proposing where to look next |
| Infrastructure metrics | CPU, memory, storage, and network in Azure Monitor alongside OCI metrics |
| Alerting and correlation | Azure Monitor alerts and Sentinel to catch regressions and correlate across tiers |
11.1 Let ADDM point you at the next problem
-- ADDM analyses each AWR snapshot pair and ranks findings by impact
SELECT task_name, execution_end
FROM dba_advisor_tasks
WHERE advisor_name = 'ADDM'
ORDER BY execution_end DESC
FETCH FIRST 5 ROWS ONLY;
-- Read the most recent ADDM findings, ranked by benefit
SELECT DBMS_ADDM.GET_REPORT(task_name => '&latest_addm_task') FROM dual;11.2 A simple daily health query
A short script you can schedule and diff day over day is worth more than an elaborate dashboard nobody opens. Track a few numbers that move when something is wrong.
-- Top 5 wait events over the last 24h, and DB time on CPU vs waiting
SELECT event_name,
ROUND(SUM(time_waited_micro_delta)/1e6) AS waited_s
FROM dba_hist_system_event e
JOIN dba_hist_snapshot s USING (snap_id, dbid, instance_number)
WHERE s.begin_interval_time > SYSDATE - 1
AND e.wait_class <> 'Idle'
GROUP BY event_name
ORDER BY waited_s DESC
FETCH FIRST 5 ROWS ONLY;Trend it, do not just watch it
A single day's numbers tell you little; the same numbers across two weeks tell you everything. Capture a handful of key metrics daily — top waits, DB CPU, average response of your critical statements — and watch the direction. A batch window that grows two percent a day is invisible on any given morning and an incident by month end. Catching the slope is the whole game.
12.Tuning Myths and Pitfalls
| Myth / reflex | The reality on Oracle Database@Azure |
|---|---|
| "A full table scan is always bad" | On Exadata, Smart Scan often makes a full scan the fastest path — faster than an index range scan |
| "Add an index to fix a slow query" | Sometimes; but it taxes every write and may be unnecessary given Smart Scan. Test invisibly first |
| "A high buffer cache hit ratio means healthy" | It can mean the database efficiently does unnecessary work. Tune to time, not ratios |
| "Add CPU to fix high CPU" | Only if the CPU does necessary work. Fix parsing and bad plans first, then scale |
| "Gather stats more often to be safe" | Over-gathering churns plans and burns resources; gather when data has actually shifted |
| "Set every memory parameter as large as possible" | Let advisories and PGA spills guide sizing; oversizing wastes capacity without helping |
| "cursor_sharing = FORCE fixes literal SQL" | A blunt mitigation with side effects — fix binds in the application; use FORCE only as a stopgap |
| "Tune everything to be fast" | Tune the biggest consumer of DB time. Effort on anything else is motion without progress |
Almost every tuning mistake is the same mistake: acting before measuring. The database always knows where its time went — ask it before you change anything.
13.Frequently Asked Questions
Where do I start when a database is "slow"?
With wait events, not a parameter. Run the wait-class query to see whether time goes to I/O, concurrency, commits, or CPU, then generate an AWR report for the slow window and read Top Timed Events. The biggest consumer of DB time is your target — everything else is a distraction until that is fixed.
Do I need AWR and ASH licenses?
Yes — AWR and ASH are part of the Diagnostics Pack, which is licensed separately. Confirm your entitlement before relying on them. Where they are not licensed, you can still use the base v$ views (v$session, v$system_wait_class, v$sql) for real-time analysis.
Should I really avoid adding indexes on Exadata?
Not avoid — scrutinise. Selective OLTP lookups still want B-tree indexes. But before adding an index to speed a large scan, check whether Smart Scan already makes the scan fast enough, because the index will tax every write forever. Test new indexes invisibly and drop unused ones.
How do I add CPU, and when should I?
ECPUs scale online on the VM cluster, so it is quick and needs no downtime. But scale last, not first: eliminate wasted CPU (bind variables, bad plans) and protect critical work with Resource Manager before adding capacity. Adding cores to run inefficient code is expensive and treats the symptom.
Why did a query that was fast for months suddenly get slow?
Almost always a plan change, usually triggered by stale statistics or new data volumes. Use DBMS_XPLAN.DISPLAY_CURSOR to compare estimated versus actual rows, refresh statistics if they are stale, and use SQL Plan Management to lock in the good plan so it cannot silently regress again.
What is the single most useful tuning artifact?
The AWR report's Top Timed Events section, backed by ASH for short incidents. Between them they tell you where time actually went, which is the only sound basis for deciding what to change.
How is tuning here different from on-premises Oracle?
The method is identical; the storage instincts change. Exadata Smart Scan, storage indexes, and HCC make full scans and large analytics much cheaper, so some indexing and I/O-avoidance habits from on-premises no longer apply — and ASM removes manual datafile placement. Tune with the storage engine, not around it.
14.Key Takeaways
The short version
• Measure before you change. Start every problem at wait events, then AWR Top Timed Events, then ASH for short incidents. The database knows where its time went — ask it.
• Fix the biggest consumer of DB time, one change at a time, and verify the wait shrank before the next change.
• Scale CPU last. ECPUs add online, but eliminate wasted CPU and protect critical work with Resource Manager before buying more.
• Design storage with Exadata, not against it. Smart Scan makes full scans cheap, HCC cuts scan I/O, and ASM removes manual placement — unlearn some on-premises habits.
• Index deliberately. Every index taxes writes; test new ones invisibly, drop unused ones, and ask whether Smart Scan removes the need entirely.
• Most SQL problems are binds or stale stats. Fix the cause and you repair a whole class of statements; lock good plans with SPM before big changes.
• Monitor the slope. Trend a few key metrics daily and catch the two-percent-a-day regression before it becomes an outage.
Performance tuning has a reputation for being arcane, but the discipline underneath it is simple and unchanging: ask the database where its time goes, fix the largest consumer, prove the fix, and repeat. Everything in this guide — the AWR reading order, the ECPU sizing, the Exadata storage instincts, the indexing restraint, the SQL diagnostics — is in service of that loop.
What makes Oracle Database@Azure distinctive is mostly the storage: Exadata does work that used to be yours, which means some of your hardest-won on-premises habits become unnecessary or even counterproductive. Lean into that. Let Smart Scan eliminate I/O, let ASM balance it, let the platform scale CPU online when the evidence demands it — and spend your own effort where it still matters most: finding the one statement, the one missing bind, the one stale statistic that is costing you the most time. Measure first, and the rest follows.
All SQL, package calls, and parameters are illustrative, use placeholder values, and must be validated against your own database versions and current Oracle documentation before use — option names, view columns, and package signatures change between releases. AWR and ASH require the Oracle Diagnostics Pack; confirm your licensing. Test everything on a non-production copy first. This article is independent commentary and is not affiliated with, endorsed by, or sponsored by Oracle or Microsoft.
Comments