Are you having trouble with
a specific Query in standby Database instead of the Primary response time?
Introduction:
Why the Same SQL Is Slower on Standby
Than Primary
When a SQL query runs slower on an Active Data Guard standby than
on the primary database, the first instinct is often to blame bad SQL, missing
indexes, or insufficient resources. Oracle explicitly warns that this
assumption is often wrong.
“A particular query response time or throughput is not guaranteed
to be the same on the standby system as it was on the primary.”
This is not a legal disclaimer or a vague recommendation—it is a
direct consequence of how Active Data Guard enforces read consistency while redo
apply is continuously running.
On a primary database, queries read data that is locally
consistent using undo that is immediately available. On a standby database,
however, every query must ensure that the data it sees is consistent with a
specific System Commit Number (SCN), and that SCN must already be processed by
redo apply. When redo apply lags—even slightly—queries may wait, sometimes
turning very short SQL statements into unexpectedly slow operations.
Understanding this difference is essential before attempting any
tuning. In many cases, the query itself is perfectly efficient—the delay is
caused by standby consistency mechanics, not poor SQL design.
What if a specific query does not meet your
requirements?
Consult with a performance engineer and follow the recommendations
in Database Performance Tuning Guide. A particular query response time or
throughput or report elapsed time is not guaranteed to be the same on the
standby system as it was on the primary. Analyze the system resources, SQL
plans, overall CPU work time and wait times. For example, you may see standby
query SCN advance wait is contributing to a much longer elapsed time in one of
your short queries. This wait increase is attributed to Active Data Guard redo
apply. If a query sees a certain row in a data block and needs to roll it back
because the transaction has not committed as of the query System Commit Number
(SCN), it needs to apply corresponding undo to get a consistent read for that
query. If the redo for the corresponding undo change has not been applied by
redo apply yet, the query needs to wait. The presence of such wait is itself
not an issue, and typically may be a couple of milliseconds, but it will vary
by workload and may be higher in Real Application Cluster database systems.
I’ll break this into 5 layers:
- What
Oracle is warning you about (conceptual meaning)
- Why
standby query performance is inherently different from primary
- Deep
dive into standby query SCN advance wait
- How
to diagnose a “bad” query on standby
- What
you can do about it (tuning & design actions)
What Oracle is really
saying
"A particular query
response time or throughput is not guaranteed to be the same on standby as on
primary."
This is not a generic disclaimer; it’s a hard
architectural reality:
|
Primary
|
Active Data Guard Standby
|
|
Foreground
sessions modify data
|
Foreground
sessions only read
|
|
No redo
apply
|
Continuous
redo apply
|
|
No SCN
synchronization waits
|
SCN must
be consistent with redo apply
|
|
Undo is
always locally present
|
Undo may
depend on redo being applied
|
On standby, your query
is competing with redo apply and consistency mechanisms, not just CPU/IO.
So, Oracle is telling you:
If a query is slower on standby, do not assume it is SQL
inefficiency, first analyze consistency waits and redo behavior.
Why standby queries behave
differently
On Primary:
A consistent read only needs:
- Local
undo
- Current
buffer cache
- No
coordination with redo
On Standby (Active Data Guard):
A query must:
- Determine
its query SCN.
- Ensure
the block version it reads is consistent as of that SCN.
- If
the block contains changes from transactions not committed at that SCN:
- Roll
them back using undo.
- But
that undo itself may not yet be available until redo apply
processes it.
So, a query may wait for redo apply to catch up enough to
make the undo visible.
That is where this wait comes from:
standby query scn advance
Deep dive: standby query
scn advance
What exactly does this wait mean?
It means:
“My query SCN is ahead of what redo apply has processed, so I must
wait for redo apply to advance.”
This usually happens when:
- Redo
generation is high on primary
- Redo
transport or apply is slightly lagging
- Query
needs a very recent consistent version
Timeline
illustration
Primary
SCN: 1000 ---- 1010 ---- 1020
Redo
applied: 990 ---- 995 ---- 1002
Query
wants SCN: 1015
↑
standby
query scn advance wait
Your query cannot see SCN 1015 yet, because redo apply
hasn’t reached that point.
Why rollback (undo) makes it worse
If your query reads a block that contains changes from an
uncommitted transaction:
- It
must rollback using undo
- But
that undo record itself is in redo
- If
redo apply hasn’t applied that undo yet → wait
Thus:
A short query can become slow if it hits blocks with recent
changes.
How to
diagnose a query that doesn’t meet requirements
Step 1: Confirm
it is a standby consistency wait
SELECT event, total_waits, time_waited_micro
FROM v$system_event WHERE event LIKE 'standby%';
Or session level:
SELECT sid, event, seconds_in_wait FROM v$session
WHERE event = 'standby
query scn advance';
Step 2 — Check
apply lag
SELECT name, value, unit
FROM v$dataguard_stats WHERE name IN ('apply lag','transport lag');
Even a few seconds of lag can affect “fresh data” queries.
Step 3 — Check
redo rate vs apply capacity
SELECT * FROM v$recovery_progress;
SELECT * FROM v$managed_standby;
Look for:
- MRP
lagging
- Single-threaded
apply in high redo environments
- IO
bottlenecks on standby redo logs or datafiles
Step 4 — Identify
affected SQL
SELECT sql_id, elapsed_time, executions
FROM v$sql ORDER BY
elapsed_time DESC;
Cross-check whether the same SQL is fast on primary but slow on
standby.
What you can do about it
A) Reduce redo apply pressure
- Use larger
Standby Redo Logs
- Ensure
fast storage for redo and undo
- Enable
parallel redo apply if needed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8;
B) Route queries intelligently
Not all queries should go to standby:
|
Query
Type
|
Good
for Standby?
|
|
Historical reporting
|
Yes
|
|
Fresh
transactional data
|
Maybe
|
|
“Just committed” lookups
|
Avoid
|
|
Operational
dashboards
|
⚠ Depends
|
C) Use Data Guard service classes
Create services with different lag tolerance:
srvctl add service -d DB -s RO_FAST -l PRIMARY
srvctl add service -d DB -s RO_LAG_TOLERANT -l PHYSICAL_STANDBY
D) Tune the SQL for fewer “hot blocks”
Queries that repeatedly hit frequently modified blocks
suffer more.
Example:
- Index
on monotonically increasing columns
- Hot
segments (queues, logs, audit tables)
Mitigations:
- Partitioning
table/ index/PK
- Reverse
key indexes
- Move
hot objects off standby usage
Summary
|
Point
|
Meaning
|
|
Standby
is not just a replica
|
It
enforces SCN consistency with redo
|
|
standby
query SCN advance
|
Query
waits for redo apply to reach needed SCN
|
|
Short
queries can be slow
|
If they
hit recently modified blocks
|
|
Not
a bug
|
It’s
fundamental to Active Data Guard
|
|
Solution
|
Tune
apply, route queries, redesign hot access
|
|
|
|
Key points:
Active Data Guard gives you correct data, not necessarily fast
data for every workload.
If you need low latency on fresh data, standby is the wrong place.
If you need offload of stable, historical, or analytical queries,
standby is ideal.
Although there are some another parameters and techniques to improve
Standby.
In the next post, I will speak about how to achieve to a minimum
synchronized standby database.
**************************
Alireza Kamrani
RDBMS/NoSQL Solution Architecture
