Unlock Oracle Performance: How Architecture Impacts SQL Optimization

1. Oracle Architecture Overview

Oracle consists of an instance and data files. The instance includes a shared memory area called the System Global Area (SGA) and a set of background processes.

The SGA is divided into three key parts:

Shared Pool : stores parsed SQL statements and execution plans.

Data Buffer : caches data blocks read from disk; dirty blocks are written back to disk.

Log Buffer : holds redo information before it is written to the online redo log.

Images illustrate the SGA layout before and after the database is started and explain why the allocation size appears as 2 485 125 120 bytes.

SGA Allocation Before Startup

SGA Allocation After Startup

2. Architecture and SQL Optimization

2.1 Shared Pool – Parsing Optimization

The first execution of a SQL statement parses the statement, validates semantics, and stores an optimized execution plan in the Shared Pool. Subsequent executions reuse this plan, eliminating parsing overhead.

Experiment:

First run time: 00:00:00.10, Recursive Calls: 28.

Second run time: 00:00:00.07, Recursive Calls: 0.

Parsing optimization makes the second execution noticeably faster.

2.2 Bind Variables – Reducing Hard Parses

When many similar statements differ only by literal values (e.g., different phone numbers), each statement is parsed separately, wasting CPU and Shared Pool space. Rewriting them with bind variables consolidates them into a single parsed statement.

Before using bind variables (execution time ≈ 43 s):

After using bind variables (execution time ≈ 4 s):

Bind variables reduce parsing time by an order of magnitude and free Shared Pool resources.

2.3 Hard‑Parse Count and Execution Count

Scripts demonstrate how hard‑parse counts drop to zero after applying bind variables and shared‑pool caching.

2.4 Result‑Cache Experiment

Adding the hint /*+ result_cache */ (or enabling the session result‑cache) stores the result set in the Shared Pool. Subsequent executions read the cached result, making logical reads zero.

Note: The cache becomes invalid as soon as the underlying tables change.

2.5 Function Result‑Cache

Similar to SQL result‑cache, caching function results can also drive logical reads to zero when the function’s inputs remain unchanged.

2.6 Data Buffer – Buffer Optimization

Flushing the Shared Pool removes the parsed plan, allowing the second execution to demonstrate the pure effect of buffer caching. Physical reads drop from 1038 to 0, while recursive calls stay unchanged because the plan was re‑loaded.

2.7 Combined Parsing and Buffer Optimization

When both parsing cache and buffer cache are active, the second execution shows zero recursive calls and zero physical reads.

2.8 Direct‑Path Insert

Direct‑path insert bypasses the SGA, writing data straight to disk. It is much faster for bulk loads (4.22 s vs 14.81 s) but generates a large number of physical reads for subsequent queries because the data is not cached.

2.9 Log Buffer – Batch Commit vs Single Commit

Using ALTER SESSION SET COMMIT_WRITE = BATCH (or equivalent batch‑commit techniques) reduces total commit time from 11.21 s to 4.26 s.

2.10 Log Buffer – Disabling Logging

Turning off redo logging (e.g., ALTER DATABASE NOLOGGING) halves insert time (12.08 s vs 23.68 s). This should only be used when data loss is acceptable.

3. Extended Optimization Cases

3.1 Detecting Hard‑Parse Issues

To locate SQL statements that are not using bind variables, replace variable parts with a placeholder (e.g., @), group identical patterns, and extract the offending statements.

3.2 Zero Logical Reads via Result Cache

Applying the /*+ result_cache */ hint or enabling session‑level result caching makes the first execution store the result set in the Shared Pool; the second execution reads it directly, yielding zero logical reads.

3.3 Zero Logical Reads for Functions

When a function’s result does not change, caching its result in the Shared Pool eliminates logical reads for subsequent calls.

3.4 Keeping Critical Data in Buffer

The KEEP clause can pin frequently accessed rows in the Data Buffer, preventing them from being evicted.

3.5 Monitoring Database Health

Collecting metrics such as session count, logical reads, parse counts, and transaction rates over time helps spot abnormal periods.

3.6 Identifying Excessive Commits

Using AWR reports to find sessions with a high commit count, then retrieving the corresponding SQL_ID, reveals statements that should be batch‑committed.

3.7 Log‑Switch Pattern Analysis

Analyzing the frequency of log switches can reveal days with abnormal activity.

3.8 Tracing Log Volume Spikes

When redo logs fill up quickly, the following steps locate the offending SQL:

Identify the time window with high redo generation.

Query V$SQL and V$SESSION to find statements with large REDO_SIZE.

Inspect the source code of the identified statements.

Understanding Oracle's internal architecture enables targeted performance tuning: leverage the Shared Pool for parsing reuse, adopt bind variables to cut hard parses, use result‑cache and direct‑path techniques where appropriate, batch commits to reduce logging overhead, and continuously monitor key metrics to detect and resolve bottlenecks.