Why the Same SQL Is Slower on Standby Than Primary؟

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:

1. What Oracle is warning you about (conceptual meaning)
2. Why standby query performance is inherently different from primary
3. Deep dive into standby query SCN advance wait
4. How to diagnose a “bad” query on standby
5. 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:

1. Determine its query SCN.
2. Ensure the block version it reads is consistent as of that SCN.
3. If the block contains changes from transactions not committed at that SCN:
• Roll them back using undo.
4. 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

Oracle Database Smart Flash Cache

 Database Smart Flash Cache

What is it? When Use? How to Use? What are Pros & Cons

Overview

The database buffer cache, also called the buffer cache, is a memory area in the system global area (SGA) of the database instance. It stores copies of data blocks that are read from data files. A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users that are concurrently connected to a database instance share access to the buffer cache. The goals of the buffer cache are to optimize physical I/O and keep frequently accessed blocks in the buffer cache.

https://www.linkedin.com/feed/update/urn:li:activity:7400843852112191488

1. Purpose of Flash Cache

The Database Smart Flash Cache was designed as a second-level buffer cache, specifically to extend read caching, not to replace datafiles or redo mechanisms.

Its role:

• Keep clean (unmodified) copies of database blocks.
• Serve future read requests faster than from disk.
• Avoid extra complexity and write synchronization.

  Database Smart Flash Cache (flash cache) lets you use flash devices to increase the effective size of the buffer cache without adding more main memory. Flash cache can improve database performance by storing the database cache's frequently accessed data stored into flash memory instead of reading the data from magnetic disk.

When the database requests data, the system first looks in the database buffer cache. If the data is not found, the system then looks in the Database Smart Flash Cache buffer.

 If it does not find the data there, only then does it look in disk storage. You must configure a flash cache on either all or none of the instances in an Oracle Real Application Clusters (RAC) environment.

If you enable Database Smart Flash Cache, the flash buffer area consists of a DEFAULT flash LRU chain and a KEEP flash LRU chain.

Without Database Smart Flash Cache, when a process tries to access a block and the block does not exist in the buffer cache, the block is first read from disk into memory (physical read).

When the in-memory buffer cache gets full, a buffer is evicted out of the memory based on an LRU mechanism.

With Database Smart Flash Cache, when a clean in-memory buffer ages out, the database writer process (DBWn) writes the content to the flash cache in the background, and the buffer header remains in memory as metadata in either the DEFAULT or KEEP flash LRU list, depending on the value of the FLASH_CACHE object attribute.

The KEEP flash LRU list maintains the buffer headers on a separate list to prevent the regular buffer headers from replacing them.

This means that the flash buffer headers belonging to an object that is specified as KEEP tend to stay in the flash cache longer.

If the FLASH_CACHE object attribute is set to NONE, the system does not retain the corresponding buffers in the flash cache or in memory.

When a buffer that was already aged out of memory is accessed again, the system checks the flash cache. If the buffer is found, it reads it back from the flash cache, which takes only a fraction of the time of reading from the disk.

The consistency of flash cache buffers across RAC is maintained in the same way as by Oracle RAC Cache Fusion. Because the flash cache is an extended cache and direct path I/O totally bypasses the buffer cache, this feature does not support direct path I/O.

Note that the system does not put dirty buffers in flash cache because it may have to read buffers into memory to checkpoint them because writing to flash cache does not count for checkpoint.

 

 

 2. Oracle Buffer Cache Design Recap

In the SGA buffer cache, every data block can be in one of two states:

Block statuses:

• Dirty: The block's data has been modified but not yet written to disk. The Database Writer (DBW) process flushes dirty blocks from memory to disk to free up space. Modified in memory; not yet written to disk (requires redo).
• Clean: The data in the block is the same as the copy on disk. safe to discard or cache elsewhere. 
• Free: The buffer is empty and available for a new block to be read into it. 
• Pinned: The buffer is currently being accessed by a session and cannot be removed from the cache. 

Oracle ensures write consistency and recovery through the redo/undo system, not by mirroring dirty buffers in flash.

3. Flash Cache Contains Only “Clean” Buffers

When the buffer cache in SGA becomes full and Oracle needs to age out some blocks, the Database Writer (DBWR) process does this:

1.     If a block is dirty, it’s written to datafiles on disk (via redo).

2.     If the block is clean, it can be safely moved to flash cache.

This means:

The flash cache only ever holds clean, read-only copies of data blocks that already exist safely on disk.

No block in flash cache is ever “owned” by the database — it’s just a read-optimized replica.

Why Oracle Keeps It Read-Only:

Data Consistency

If Oracle allowed writes to flash cache, it would have to:

·         Keep redo/undo consistency for flash blocks too.

·         Handle recovery logic after instance crashes.

·         Maintain write ordering between DRAM, flash, and disk.

This would make the system as complex as having a second redo layer — defeating the simplicity of caching.

Performance Efficiency

Flash cache is optimized for random reads, not for frequent writes:

·         Writes wear out SSDs faster (limited program/erase cycles).

·         Write amplification reduces performance and SSD lifespan.

·         Oracle already writes to redo logs and datafiles; adding another write path would double the I/O.

By making flash cache read-only, Oracle avoids:

·         Additional write latency

·         Redundant I/O

·         SSD wear

Recovery Simplicity

If the instance crashes:

·         The SGA is lost.

·         Flash cache remains on SSD, but blocks there are just optional cache.

·         No need to recover it — Oracle simply repopulates it automatically on restart.

Because no dirty blocks are stored there, it’s safe to discard flash cache without data loss.

Compare:

You can think of the flash cache as a "read-only mirror shelf" under your main memory:

·         The SGA buffer cache is your active workspace — you can read and write there.

·         The flash cache is a backup of pages you recently used — if you need them again, you grab them quickly.

·         But you never “write” to that shelf — it’s just a faster copy of what’s already in your database files.

 

Related Parameters:

DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE are Oracle Database initialization parameters that enable a second-level cache (called the Database Smart Flash Cache) — located on fast SSD or flash storage.

This mechanism was introduced in Oracle 11g (Enterprise Edition) for Linux and Solaris platforms, primarily to improve performance of read-intensive workloads by extending the Database Buffer Cache beyond DRAM.

DB_FLASH_CACHE_FILE

Specifies the file(s) that will store the flash cache — typically a raw device, ASM diskgroup, or file on an SSD.

Example:

DB_FLASH_CACHE_FILE = '/u01/oradata/flash_cache1', '/u02/oradata/flash_cache2';

You can specify up to 16 file names for flash memory devices.

For example, if there are three flash raw devices:

DB_FLASH_CACHE_FILE = /dev/raw/sda, /dev/raw/sdb, /dev/raw/sdc

Specifying this parameter without also specifying the DB_FLASH_CACHE_SIZE initialization parameter is not allowed.

 

If your flash cache consists of one flash cache device, you can dynamically change this parameter to 0 for that flash cache device (disabling the flash cache) after the database is started. You can then reenable the flash cache by setting this parameter for the device back to the original value when the database was started. Dynamic resizing of DB_FLASH_CACHE_SIZE or reenabling flash cache to a different size is not supported.

If your flash cache includes multiple flash cache devices, you can dynamically change the parameter to 0 for a particular flash cache device (turning it off) after the database is started. You can then reenable that flash cache device by setting this parameter for the device back to the original value it had when the database was started (turning it back on).

For example, to turn off the /dev/raw/sdb flash cache device:

db_flash_cache_file = /dev/raw/sda, /dev/raw/sdb, /dev/raw/sdc

db_flash_cache_size = 32G, 0, 64G

 

And, to turn the /dev/raw/sdb flash cache device back on again:

 

db_flash_cache_file = /dev/raw/sda, /dev/raw/sdb, /dev/raw/sdc

db_flash_cache_size = 32G, 32G, 64G

 

DB_FLASH_CACHE_SIZE

Range of values

0 to (DB_BLOCK_SIZE * 256 MB) If DB_BLOCK_SIZE = 2 KB, then 0 to 512 GB If DB_BLOCK_SIZE = 4 KB, then 0 to 1 TB If DB_BLOCK_SIZE = 8 KB, then 0 to 2 TB If DB_BLOCK_SIZE = 16 KB, then 0 to 4 TB If DB_BLOCK_SIZE = 32 KB, then 0 to 8 TB

You can specify up to 16 file sizes, for each of the flash memory devices specified with DB_FLASH_CACHE_FILE. For example, if there are three flash raw devices, you can specify the sizes of each device as follows:

db_flash_cache_file = /dev/raw/sda, /dev/raw/sdb, /dev/raw/sdc

db_flash_cache_size = 32G, 32G, 64G

You can configure multiple flash cache files (up to 16), usually for different devices.

 

Conceptual Architecture

Memory hierarchy after enabling Smart Flash Cache:

      

 

 

 

 

 

 

 

 

 

 

Buffer Cache (SGA): first-level cache for data blocks.

Flash Cache: second-level cache for less frequently accessed blocks.

Disk Storage: last level of data retrieval.

 

 

Benefits

1.     Improved Performance Without Adding RAM

o    Acts as an extension to buffer cache using fast SSD storage.

o    Ideal when RAM is limited or expensive.

2.     Reduced I/O Latency

o    Frequently accessed blocks read from SSD instead of spinning disks.

o    SSD random read latency is far lower than HDD.

3.     Increased Effective Buffer Cache

o    Allows a larger working set of data to be cached between DRAM and disk.

4.     Easy to Implement

o    Requires only parameter changes and restart.

o    No change in application logic.

5.     Cost-Effective

o    SSDs are cheaper per GB than DRAM, providing a good cost/performance balance.

 

 When to Use:

Best suited for:

• Systems where adding DRAM is not possible (hardware limits or cost).
• Read-heavy workloads: OLTP, reporting, analytics with frequent block reads.
• Databases with large working sets that don’t fit entirely in memory.
• Systems using HDD or slower SAN/NAS storage backends.

Not useful for:

• Databases already fully cached in DRAM.
• Write-intensive workloads (Flash cache is read-only extension).
• Systems with already-fast NVMe or all-flash storage (limited gain).

 

Pros and Cons

Category	Advantages	Disadvantages
Performance	Faster reads, extended cache	No benefit for writes
Cost	Cheaper than adding DRAM	SSD wear-out over time
Setup	Simple to enable	Requires database restart
Platform	Supported on Linux/Solaris	Not supported on Windows
Maintenance	Transparent to applications	Must monitor SSD health and space

 

Example Configuration

ALTER SYSTEM SET DB_FLASH_CACHE_FILE = '/u01/flash/flashcache1', '/u02/flash/flashcache2' SCOPE=SPFILE;

ALTER SYSTEM SET DB_FLASH_CACHE_SIZE = 64G, 64G SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

To verify:

SHOW PARAMETER flash_cache;

SELECT * FROM V$FLASHFILESTAT;

 

Monitoring

You can monitor the usage and efficiency with:

SELECT NAME, VALUE FROM V$SYSSTAT

WHERE NAME LIKE '%flash%';

Example metrics:

• flash cache hits
• flash cache read misses
• flash cache write (should be near zero, since cache is read-only)

 

 

 

Key Internals

• Works as an L2 buffer cache (not write-back).
• Blocks are aged out from the buffer cache and copied to flash cache.
• When a block is requested, Oracle checks DRAM first, then flash cache, then disk.
• Controlled by internal Least Recently Used (LRU) mechanism extended to flash.

Tips & Best Practices

• Place flash cache files on dedicated SSDs (avoid OS-level interference).
• Don’t oversize it — the benefit flattens beyond working-set size.
• If using ASM, prefer separate diskgroups for flash cache.
• Combine with Automatic Memory Management (AMM) or ASMM carefully — flash cache doesn’t replace shared pool or PGA tuning.

 

Summary

Parameter	Purpose	Typical Use
DB_FLASH_CACHE_FILE	Path to SSD-based flash cache file(s)	/u01/flash/flashcache1 , /dev/raw/sdc
DB_FLASH_CACHE_SIZE	Defines size of each flash cache file	64G
Benefit	Adds L2 cache using SSDs	Improves read-heavy workloads
Limitation	Read-only, Linux/Solaris only	No effect on writes

 

Affects With vs. Without Smart Flash Cache

 

Scenario	I/O Path	Typical Latency	Description
Without Flash Cache	DRAM → HDD (Disk I/O)	3–10 ms	Each cache miss in SGA goes directly to slow disk
With Flash Cache Enabled	DRAM → Flash SSD → HDD	0.1–0.5 ms	Cache misses in SGA often satisfied from fast SSD before hitting disk

 

 

Wait Event Impact

Wait Event	Effect without Flash Cache	Effect with Flash Cache
db file sequential read	High waits on HDD latency	Reduced sharply (SSD hit instead)
db file scattered read	Slower table/index scans	Faster reads for range scans
read by other session	Common in shared buffer thrash	Reduced due to L2 cache hits

 

Real-World Impact (Observed by Oracle and Customers)

• OLTP workloads:
  10–30% reduction in transaction response time.
  Significant drop in “buffer busy” and “db file sequential read” waits.
• DSS/Reporting workloads:
  Queries with large scans benefit less unless frequently repeated.
  But flash cache still reduces random read latency.
• Mixed workloads:
  Most improvement for repeat queries or re-used lookup blocks.

 

Caveats

Issue	Explanation
Write-heavy workloads	Usually No benefit — flash cache is read-only
All-Flash Storage systems	Marginal improvement — already sub-ms
Low cache hit ratio	If you’re working set rarely repeats, gains are small
PGA / Sorts / Temp I/O	Flash cache doesn’t affect PGA or temp tablespace I/O