How Oracle decides to reuse Shared SQL Area

How Oracle decides to reuse Shared SQL Area

Introduction:
In the previous post, I reviewed an comparison on SHARED SQL AREA vs. PRIVATE SQL AREA. In this topic, I review most internal steps to SQL ready for processing in Oracle Shared SQL Area and explain how Oracle can reuse statistics and informations gathered in the Shared SQL Area.

The process Oracle uses to decide whether to reuse an existing Shared SQL Area is a critical part of its performance tuning mechanism. It's all about avoiding the costly "hard parse" and instead performing a "soft parse."

Here's how Oracle makes that decision:

1. Hashing the SQL Statement
First, Oracle takes the entire SQL statement text and runs it through a hashing algorithm. This algorithm generates a numeric hash value that represents the SQL text.

2. Checking the Library Cache
Next, Oracle uses this hash value to quickly look up an entry in the Library Cache (which is part of the Shared Pool).

•  If no match is found for the hash value: The SQL statement is new. Oracle has no choice but to perform a hard parse.
This involves:

   1. Syntax Check: Verifying the SQL grammar.

    2. Semantic Check: Validating object names (like tables and columns) and user privileges.

   3. Optimization: Generating one or more potential execution plans and selecting the most efficient one.

    4. Creating the Shared SQL Area: Allocating memory in the Shared Pool to store the parsed statement and the chosen execution plan.

This is a CPU and resource-intensive process that Oracle tries to avoid.


• If a match for the hash value is found: This means one or more SQL statements with the same hash value already exist in the Shared Pool. Now, Oracle must perform additional checks to ensure it's an *exact* match.

3. The String-by-String Comparison

This is the most important step. Even if the hash values match, Oracle performs a character-by-character comparison of the new SQL statement against the one stored in the existing Shared SQL Area.

The SQL statements must be absolutely identical to be considered a match.

This includes:

Whitespace:
SELECT * FROM employees
is different from
SELECT  *      FROM        employees؛

Case (unless configured otherwise):
SELECT * FROM EMPLOYEES
is different from
select * from employees؛

Comments:
SELECT * FROM employees --my query
is different from
SELECT * FROM employees.

Object Names:
SELECT * FROM scott.emp
is different from
SELECT * FROM emp
(even if they resolve to the same table via a synonym).

If this character-by-character comparison fails, Oracle treats it as a new statement and performs a hard parse.

Note: This behavior can mostly related to Cursor Sharing parameter value.(see last section)

4. Environment and Security Check

If the text is identical, Oracle performs one final set of checks on the execution environment. The environment for the new statement must match the environment of the stored statement.

This includes:

Optimizer Settings: The OPTIMIZER_MODE (e.g., `ALL_ROWS`, `FIRST_ROWS`) must be the same.

Schema Objects: The underlying tables, indexes, and statistics must not have changed in a way that would invalidate the existing plan.

For example, if an index used by the plan was dropped, the plan is invalid.

Bind Variable Types: The data types of the bind variables must be compatible.

The Result: Soft vs. Hard Parse

Soft Parse (Match Found): If the SQL text is identical AND the environment checks pass, Oracle reuses the existing Shared SQL Area and its execution plan.
This is a soft parse. It's extremely fast as it skips the complex optimization step.
This is the desired outcome for a high-performance application.

Hard Parse (No Match): If any of the checks fail, Oracle performs a hard parse, creating a new Shared SQL Area.


This is why using bind variables is so critical for application performance.

Statements like:

SELECT * FROM products WHERE product_id = 101;
SELECT * FROM products WHERE product_id = 205;

...are considered two different statements by Oracle, leading to two separate hard parses and two Shared SQL Areas.

However, using a bind variable:

SELECT * FROM products WHERE product_id = :id;

...results in a single statement that can be shared and reused for any `product_id`, leading to one hard parse and many fast soft parses.
How CURSOR_SHARING Affects the SQL Parsing

Process The CURSOR_SHARING parameter does not influence the mandatory validation steps of a parse, but it is the deciding factor in the Soft Parse Decision; the process of reusing an existing cursor.

Hard Parse Prerequisites are Independent: The initial steps of a parse is Syntax Check (verifying grammar) and Semantic Check (validating objects and privileges) are mandatory and must be performed regardless of the CURSOR_SHARING setting.
These steps are not modified by the parameter.
The Soft Parse Decision is Directly Controlled: The core function of CURSOR_SHARING is to define what Oracle considers "identical" for the purpose of cursor reuse in the Shared Pool. This decision is made at the point of the String-by-String Comparison.

1- CURSOR_SHARING = EXACT (Default):

The comparison is performed on the raw SQL text.

Any difference, including case, whitespace, or the literal value in a WHERE clause (e.g., WHERE C=1 vs. WHERE C=2), results in a mismatch, forcing a hard parse.

2- CURSOR_SHARING = FORCE:

Before the comparison, Oracle internally rewrites the SQL statement by replacing all literal values with system-generated bind variables (e.g., :"SYS_B_0").

The comparison is then performed on this rewritten, bind-variable-substituted text.

This allows statements that differ only by their literal values to be considered identical, enabling a soft parse and avoiding the costly Optimization and Shared SQL Area Creation steps.

Impact on Hard Parse Steps: By enabling a soft parse, CURSOR_SHARING allows the database to skip the resource-intensive Optimization (plan generation) and Shared SQL Area Creation steps, which are the most expensive parts of a hard parse.

In summary, CURSOR_SHARING acts as a pre-processor for the soft-parse comparison, effectively expanding the pool of statements that can be considered identical for cursor reuse.

Shared SQL Area vs. Private SQL Area

A comparison between Shared SQL Area and Private SQL Area in Oracle memory architecture

The Shared SQL Area and the Private SQL Area are fundamental memory structures in the Oracle Database, each playing a distinct role in the execution of SQL statements. Here's a comparison of the two:

Shared SQL Area:

The Shared SQL Area is a component of the Shared Pool, which itself resides within the System Global Area (SGA). The SGA is a memory region shared by all Oracle processes.

Key characteristics of the Shared SQL Area include:

Shared Access: As its name suggests, the Shared SQL Area is accessible to all users and sessions in the database.

Contains Execution Plan: It stores the parsed representation and execution plan of a unique SQL statement. This allows the database to reuse the plan for subsequent identical SQL statements, avoiding the overhead of reparsing.

Reduces Memory Usage: By sharing the SQL statement's parsed form and execution plan, the Shared SQL Area significantly reduces overall memory consumption.

Located in the Library Cache: The Shared SQL Area is part of the Library Cache, a key component of the Shared Pool.

LRU Algorithm: The database uses a Least Recently Used (LRU) algorithm to manage the objects in the Shared Pool, including the Shared SQL Areas. This means that if a SQL statement is not executed for a while, its Shared SQL Area might be aged out to make space for new statements.

Private SQL Area (PGA):

The Private SQL Area (PGA) is a memory region that is private to a specific session or connection. It is not shared with other sessions.


Key characteristics of the Private SQL Area include:

Private to a Session: Each session that issues a SQL statement has its own Private SQL Area.

Contains Session-Specific Data: The Private SQL Area stores data that is specific to a particular execution of a SQL statement by a session.

This includes:

Bind variable values: The values supplied to the SQL statement at runtime.

Query execution state information: Information about the current state of the query execution.

Runtime memory structures: Buffers and work areas used for operations like sorting or hash joins.

Location: The location of the Private SQL Area depends on the server connection model:

Dedicated Server: The Private SQL Area is located in the Program Global Area (PGA) of the server process.

Shared Server: Part of the Private SQL Area is located in the System Global Area (SGA), specifically in the Large Pool or Shared Pool.

Cursor Association: A cursor is essentially a handle to a specific Private SQL Area. Closing a cursor releases the memory in the persistent area of the Private SQL Area.

Two Main Areas: The Private SQL Area is divided into two main parts:

Persistent Area: Contains bind information and is freed only when the cursor is closed.

Runtime Area: Contains information about the execution state and is freed when the execution is complete.

Summary of Differences:

Location:

Shared SQL Area: Resides in the Shared Pool, which is a part of the System Global Area (SGA).

Private SQL Area: Typically located in the Program Global Area (PGA) for a dedicated server connection. For shared server connections, part of it is in the SGA.

Accessibility:

Shared SQL Area: It is a public resource, shared by all sessions connected to the database.

Private SQL Area: It is a private resource, accessible only to the specific session that created it.

Contents:

Shared SQL Area: Contains the parsed SQL text and the execution plan, which are not specific to any single session.

Private SQL Area: Holds session-specific information, such as bind variable values, query execution state, and runtime memory buffers.

Purpose:

Shared SQL Area: Its primary purpose is to enable code sharing and reuse, which reduces the overhead of parsing the same SQL statement multiple times.

Private SQL Area: Its purpose is to store the private data and state needed for a specific execution of a SQL statement by a single session.
Lifespan:

Shared SQL Area: Its lifetime is managed by a Least Recently Used (LRU) algorithm within the Shared Pool. It can be aged out if not used, to make space for other statements.

Private SQL Area: Its lifespan is tied to a cursor. The persistent part is freed when the cursor is closed, and the runtime part is freed when the execution is complete.

In essence, when a SQL statement is executed, Oracle checks the Shared Pool for an existing Shared SQL Area for that statement. If one is found, it's reused.

Regardless, a new Private SQL Area is created for the current session to hold its private data for that execution. This architecture allows Oracle to efficiently manage memory and improve performance by sharing the resource-intensive parts of SQL execution while keeping session-specific data separate.

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