Oracle DBA Lesson 7A — Why SQL and Metadata Can Be Reused

Oracle DBA · Lesson 7A

Why SQL and Metadata Can Be Reused

The shared pool caches work many sessions can reuse — parsed SQL in the library cache, and dictionary metadata in the row cache. Binds enable sharing; unique literals fight the pool. Measure before you throw memory at it.

Watch on YouTube

Oracle DBA Lesson 7A — Why SQL and Metadata Can Be Reused

AI Ops Pro

What the shared pool keeps

After the SGA map and sizing lessons, zoom into one neighborhood: the shared pool. It holds things many sessions can reuse:

• Library cache — parsed SQL / cursors (and related PL/SQL structures).
• Dictionary cache (row cache) — data-dictionary rows so parses do not hit SYS tables on disk every time.

Other residents exist (result cache when enabled, reserved space for large allocations, and more). This lesson focuses on library cache + dictionary cache — why sessions share work, and what breaks that sharing.

Soft path vs hard parse

• Library cache hit (soft parse path) — a session finds a matching shareable cursor and can skip building an executable from scratch. That is the fast path.
• Hard parse — optimize from scratch, take more latches/mutexes, spend CPU. Necessary once; toxic as a lifestyle.

Hard parse is not “evil.” Repeating it for every slightly different literal SQL is.

Binds vs literals

These look like different statements to the library cache by default:

SELECT * FROM emp WHERE empno = 10;
SELECT * FROM emp WHERE empno = 20;

Two texts → two cursors by default. Each new literal can mean another hard parse and another resident in the pool.

This looks like the same statement (one cursor, many execute values):

SELECT * FROM emp WHERE empno = :n;

Bind variables let sessions reuse one shareable cursor. An app that concatenates IDs into SQL can fill the shared pool with one-time cursors until every parse is a fight.

Dictionary (row) cache

The dictionary cache holds metadata as rows so parses avoid reading dictionary tables from disk over and over. DDL storms and invalidations churn it — another reason “shared pool pressure” is not always “make it bigger.”

Inspect before you resize

SHOW PARAMETER shared_pool

Lists shared-pool related parameters for this instance — read your values; do not invent sample sizes from a blog.

SELECT sql_id, parse_calls, executions, child_number,
       substr(sql_text, 1, 80) AS sql_text
FROM   v$sql
WHERE  sql_text LIKE '%empno%'
ORDER  BY parse_calls DESC
FETCH FIRST 20 ROWS ONLY;

Compare parse_calls vs executions. Run the same statement twice and watch how the counters move. Contrast literal vs bind shapes (many near-identical texts vs one shared text).

SELECT namespace, gets, gethits, pins, pinhits, reloads, invalidations
FROM   v$librarycache
ORDER  BY namespace;

Glance at gets/hits and reloads/invalidations on your instance — never invent live ratios from a post.

When the pool is too small: shared-pool waits, extra hard parses, sometimes ORA-04031. When it is full of junk SQL: similar symptoms without being “too small.” Look at parse behavior and top SQL by version / child count before you throw memory at it.

Gotchas

• Two statements that differ only by a numeric literal do not share one library-cache cursor by default.
• Size problems and junk-SQL problems can look alike — measure first.
• Never invent live byte or hit-ratio sample numbers; read them from your instance.
• Next in the series: the buffer cache (data blocks in memory).

Quick quiz

Choose one answer per question, then submit. You’ll see the correct answer and a short why.

1. What does the library cache primarily hold?

Dirty data blocks waiting for checkpoint Parsed SQL / cursors that sessions can share Only redo log buffers Only PGA workareas

2. Two statements differ only by a numeric literal in the WHERE clause. Do they share one library-cache cursor by default?

Always — Oracle merges any similar WHERE clause Only if the tables are empty No — different literal text means different cursors by default Yes, but only inside a PDB

3. Soft parse vs hard parse — which statement is sound?

Soft: reuse a matching shareable cursor; hard: build from scratch (more expensive) Soft means the statement never runs; hard means it always hits disk They are identical; the names are marketing only Hard parse only happens for SELECT; soft only for UPDATE

4. Shared-pool pressure or ORA-04031 — best first move?

Immediately double SHARED_POOL_SIZE with no inspection Flush the shared pool on every alert forever Ignore it; it only affects unused PDBs Measure first — parse behavior and top SQL by version/child count before adding memory

Website: https://www.apps-dba.com/

X: https://x.com/AIOpsPro