Oracle Architecture for Performance Tuners

Instance vs Database

The single most important mental model shift for a tuner: Oracle is not just a database — it's a distributed system running entirely within a single server. It has its own memory manager, its own I/O scheduler, its own process pool, and its own caching layer.

When Oracle "starts up", it creates the instance in memory and then mounts and opens the database (the files). When Oracle "shuts down", the instance is destroyed but the files remain.

The SGA — System Global Area

The SGA is the shared memory region. Every server process and background process reads from and writes to the SGA. It is the most important structure to understand for tuning.

The Buffer Cache

When Oracle needs a data block (say, a row from the ORDERS table), it first checks the buffer cache. If the block is there — that's a logical read (buffer get). Fast. Cheap. If not — Oracle must go to disk. That's a physical read. It's 1000× slower on spinning disk, 50–100× slower even on SSD.

Buffer Cache Hit Ratio

A healthy OLTP system should show 95%+. Below 90% means your cache is too small or you have a full table scan problem.

-- Buffer cache hit ratio — should be > 95% on OLTP
SELECT ROUND(
  (1 - (phyrds.value / (dbgets.value + congets.value))) * 100, 2
) AS buffer_cache_hit_ratio
FROM   v$sysstat phyrds,
       v$sysstat dbgets,
       v$sysstat congets
WHERE  phyrds.name  = 'physical reads'
AND    dbgets.name  = 'db block gets'
AND    congets.name = 'consistent gets';

The Shared Pool

Every SQL statement that Oracle executes must first be parsed. Parsing is expensive — Oracle checks syntax, resolves object names, checks permissions, and generates the execution plan. After a hard parse, Oracle stores the parsed form in the library cache inside the shared pool. The next time the same SQL comes in, Oracle skips the hard parse — that's a soft parse.

The Bind Variable Anti-Pattern

-- BAD: every call is a hard parse (unique SQL text = unique entry in library cache)
SELECT * FROM orders WHERE order_id = 1001;
SELECT * FROM orders WHERE order_id = 1002;
SELECT * FROM orders WHERE order_id = 1003;
-- Oracle treats these as 3 completely different SQL statements

-- GOOD: bind variables — one parse, plan reused for all values of :b1
SELECT * FROM orders WHERE order_id = :b1;

-- Find SQL with high parse counts and no bind variables
SELECT executions, parse_calls, sql_text
FROM   v$sqlarea
WHERE  parse_calls > 100
AND    executions  < parse_calls * 1.1   -- parsing nearly every execution
ORDER BY parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

The Redo Log Buffer

Every change to the database generates redo — the change record used for crash recovery. Redo lands first in the redo log buffer (in SGA). The background process LGWR flushes it to redo log files on disk on every COMMIT.

-- High average_wait here = LGWR bottleneck = slow commits
SELECT event, total_waits, time_waited,
       average_wait, max_wait
FROM   v$system_event
WHERE  event = 'log file sync';

The PGA — Program Global Area

Unlike the SGA which is shared, the PGA is private to each server process. It holds the sort work area, hash join area, bind variable values, cursor state, and PL/SQL variables. If a sort operation doesn't fit in the PGA sort area, Oracle spills it to disk — creating temp tablespace I/O.

-- Sessions currently spilling sorts to disk (temp tablespace)
SELECT s.sid, s.username, s.sql_id,
       w.event, w.seconds_in_wait
FROM   v$session       s
JOIN   v$session_wait  w ON w.sid = s.sid
WHERE  w.event LIKE '%temp%'
ORDER BY w.seconds_in_wait DESC;

Background Processes

These run silently behind every Oracle instance. As a tuner, you need to know what each one does and what its failure or slowness means in terms of wait events.

-- Active background processes (paddr != '00' means running)
SELECT name, description
FROM   v$bgprocess
WHERE  paddr != '00'
ORDER BY name;

How a Query Actually Executes

When your application fires a SELECT, here is exactly what happens — and which wait events map to each step. This is the most important thing to internalize for tuning.

Key V$ Views — Day 1 Queries

These are the live windows into Oracle's memory and processes. You'll use these every single day.

-- What is every session doing RIGHT NOW?
SELECT sid, username, status, event,
       seconds_in_wait, sql_id
FROM   v$session
WHERE  type = 'USER'
ORDER BY seconds_in_wait DESC;

-- Current SGA memory allocation breakdown
SELECT name, bytes/1024/1024 AS mb
FROM   v$sgainfo
WHERE  name IN (
  'Buffer Cache Size',
  'Shared Pool Size',
  'Large Pool Size',
  'Redo Buffers'
);

-- Hard parses vs soft parses + key I/O stats since instance startup
SELECT name, value
FROM   v$sysstat
WHERE  name IN (
  'parse count (hard)',
  'parse count (total)',
  'execute count',
  'db block gets',
  'consistent gets',
  'physical reads'
);

Self-Check Questions

Before moving to Chapter 2, make sure you can answer these from memory. These are real interview questions and real production scenarios.

• Q1 What is the difference between the Oracle instance and the Oracle database? If Oracle crashes and the DBA runs STARTUP, what is created first — the instance or the database opens?
• Q2 A developer says "my query was fast last month, now it's slow." You check the buffer cache hit ratio and it's 70%. What does this tell you and what two things would you investigate next?
• Q3 Why does Oracle store parsed SQL in the shared pool? What happens if the application doesn't use bind variables — and what is the performance impact at 10,000 executions per second?
• Q4 A session is doing ORDER BY on 10 million rows. Where does the sort happen first? What happens if the sort doesn't fit, and which wait event appears in that case?
• Q5 You see log file sync as the #1 wait event in ASH. Which background process is the bottleneck? Name three possible root causes and one fix for each.