Notes based on the talk: YouTube — WaqdsaepmY8
Related Oracle Support KB: Essential SQL Optimization Skills (Doc ID 2903738.1) (My Oracle Support login required)
These are my personal learning notes on how the Oracle optimizer uses statistics to build execution plans, why stats go stale, the base math behind cardinality estimates, and why data skew breaks those estimates.
1. About the statistics
Statistics ("stats") on a database object are just numbers that describe the shape of the data. Examples:
- Number of rows in a table
- Number of distinct values in a column
- Number of levels in an index
- (and more — min/max values, nulls, average row length, etc.)
The optimizer reads these numbers to guess how much data a query will touch and, from that, picks an execution plan.
Key properties
- Stats are static. Once collected, they do not change even when the underlying table changes. They are a snapshot taken at collection time.
- Stale stats are not automatically bad. "Stale" simply means enough of the table has changed since the last collection that the numbers may no longer be statistically accurate. Sometimes the old numbers are still close enough.
- Fresh stats don't guarantee a good plan — but they improve the odds. Giving the optimizer accurate inputs makes a good decision more likely; it isn't a guarantee.
Takeaway: Think of stats as the optimizer's map of the data. An out-of-date map isn't always wrong, but the more the terrain has changed, the more likely you get lost.
2. A bit of the math
For a simple equality predicate like:
NAME = 'RIC'
the estimate (cardinality — the number of rows the optimizer predicts will come back) is:
estimated rows = number of rows in the table × density of the column
Density
density = 1 / number of distinct values
Worked example
| Input | Value |
|---|---|
Distinct values in NAME | 10 |
| Density | 1 / 10 = 0.1 |
| Rows in table | 1000 |
| Estimated rows returned | 1000 × 0.1 = 100 |
So for any given NAME value, the optimizer predicts 100 rows will match.
Other formulas (ranges, joins, LIKE, multi-column predicates) are more complex, but this rows × density calculation is the base building block everything else extends from.
Takeaway: The base estimate assumes each distinct value is equally common. With 10 values over 1000 rows, it assumes ~100 rows per value across the board.
3. Skew is the problem
The density formula above hides a big assumption: the data is evenly distributed. Most optimizer math is built on the premise that values are spread out evenly, or at least close to it.
Skew is when one (or a few) values dominate the population. When that happens, the "each value = ~100 rows" assumption breaks down.
Why it breaks
Reusing the example: 1000 rows, 10 distinct NAME values, so density predicts 100 rows per value. But suppose in reality:
'RIC'appears in 910 rows- The other 9 names share the remaining 90 rows (~10 each)
Now the optimizer's flat estimate of 100 rows is badly wrong for every value — way too low for 'RIC', way too high for the rest. A wrong cardinality estimate leads to the wrong plan (e.g. index scan when a full scan is better, or vice versa).
What helps
- Histograms tell the optimizer about the skew — they record how values are actually distributed instead of assuming uniformity.
- Histograms are not a cure-all: they have bucket limits, add maintenance cost, and can themselves go stale.
- Your knowledge of the data matters. You often know which columns are skewed and which queries care about it — that human context guides where histograms and targeted stats are worth collecting.
Takeaway: Uniform-distribution math is fast and usually fine — until skew shows up. Histograms + your domain knowledge are how you tell the optimizer where the assumption doesn't hold.
4. Execution plans vs. Explain plans
Both are the set of steps the optimizer produces to solve a query. The difference is when and how real they are:
- Explain plan = a guess at what the optimizer thinks it will do.
- Execution plan = what actually happened when the query ran.
Why they can differ
There are several reasons, but a key one is bind peeking:
- Explain plans do NOT peek at bind values (the parameters passed in). They estimate as if the bind value is unknown, falling back to average density.
- Execution plans DO peek — the real plan is built using the actual bind value present at hard-parse time.
This matters most with skewed columns (ties back to section 3): the same query can get very different plans depending on whether a bind value is common or rare — and only the execution plan reflects that.
Verified: In Oracle,
EXPLAIN PLAN/ autotrace does not perform bind peeking, so binds are treated as unknown. The runtime plan captured viaDBMS_XPLAN.DISPLAY_CURSORuses the peeked bind value from the first hard parse. Practical rule: trust the execution plan (DISPLAY_CURSOR), not the explain plan, when a query uses bind variables.
Live verification (Oracle AI Database 26ai — 23.26.3.0.0)
I ran these on a real 26ai instance to confirm the slides aren't just theory.
Setup — density math (no histogram)
1000 rows, 10 distinct evenly-distributed names, stats gathered with SIZE 1 (no histogram):
CREATE TABLE plan_demo (id NUMBER, name VARCHAR2(20));
INSERT INTO plan_demo
SELECT LEVEL, 'NAME_' || MOD(LEVEL,10) FROM dual CONNECT BY LEVEL <= 1000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'PLAN_DEMO',
method_opt=>'FOR ALL COLUMNS SIZE 1');
Column stats came back exactly as the math predicts:
| num_rows | num_distinct | density |
|---|---|---|
| 1000 | 10 | 0.1 (= 1/10) |
And the cardinality estimate for an equality predicate:
EXPLAIN PLAN FOR SELECT * FROM plan_demo WHERE name = 'NAME_5';
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | 100 |
| 1 | TABLE ACCESS FULL| PLAN_DEMO | 100 | <- 1000 × 0.1 = 100 ✔
Confirmed: estimated rows = num_rows × density = 1000 × 0.1 = 100.
Skew + bind peeking (with histogram)
Reload the table heavily skewed — NAME_0 = 910 rows, the other 9 names = 10 each — and gather a histogram (SIZE 254) so the optimizer can see the skew:
INSERT INTO plan_demo SELECT LEVEL,'NAME_0'
FROM dual CONNECT BY LEVEL<=910;
INSERT INTO plan_demo SELECT 910+LEVEL,'NAME_'||(MOD(LEVEL,9)+1)
FROM dual CONNECT BY LEVEL<=90;
COMMIT;
CREATE INDEX plan_demo_name_ix ON plan_demo(name);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'PLAN_DEMO',
method_opt=>'FOR COLUMNS NAME SIZE 254');
Now the same query with a bind variable produces two different execution plans depending on the peeked value — exactly the point from section 4:
-- bind :n = 'NAME_0' (common value, 910 rows)
SELECT MAX(id) FROM plan_demo WHERE name = :n;
| Id | Operation | Name | Rows |
| 2 | TABLE ACCESS FULL | PLAN_DEMO | 910 | <- full scan
Peeked Binds: :N = 'NAME_0'
-- bind :n = 'NAME_5' (rare value, 10 rows) — new cursor
SELECT MAX(id) FROM plan_demo WHERE name = :n;
| Id | Operation | Name | Rows |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_DEMO | 10 |
| 3 | INDEX RANGE SCAN | PLAN_DEMO_NAME_IX | 10 | <- index
Peeked Binds: :N = 'NAME_5'
Confirmed live:
- The histogram let the optimizer estimate 910 vs 10 correctly instead of a flat density average — so skew awareness works.
- Bind peeking drove two genuinely different plans (full scan for the common value, index range scan for the rare one) for the identical SQL text. The
Peeked Bindssection ofDBMS_XPLAN.DISPLAY_CURSORshows the actual value used. - An
EXPLAIN PLANwould not show these peeked binds — it falls back to the average — which is why the execution plan is the source of truth.
Gotcha I hit while building this:
'NAME_'||MOD(LEVEL,9)+1fails with ORA-01722 because||and+precedence makes Oracle try to add 1 to a string. Wrap it:'NAME_'||(MOD(LEVEL,9)+1).
5. Reading plans
- Explain plans are useful for a quick idea of what will likely happen.
- Execution plans are required for serious optimization of a SQL statement (they reflect what really ran, with peeked binds and actual row sources).
Long, complex plans are hard to read — but the good news: you rarely need to read the whole plan.
- For the most part you just read the "subplan" that is misbehaving — often only a few lines. This is much easier with an execution plan.
- Understanding how the steps interact is key. A set of steps might be performing badly because of some other part of the plan — e.g. a bad cardinality estimate upstream feeds a bad join method downstream. Fix the cause, not the symptom line.
Takeaway: Don't be intimidated by a 200-line plan. Find the step doing the damage (usually where actual rows blow past the estimate), then trace why — the real culprit is often a feeding step, not the one that looks slow.
6. The basics of reading a plan
Steps complete "inside-out, bottom-to-top":
- A child step completes before its parent step.
- Multiple steps that share the same parent complete in ID order.
The slide's example
SELECT ename, dname FROM EMP emp, DEPT dept
WHERE emp.deptno = dept.deptno AND sal > 1000
ORDER BY empno;
| Id | Operation | Parent | Object | Notes |
|---|---|---|---|---|
| 0 | SELECT STATEMENT | |||
| 1 | SORT (ORDER BY) | 0 | ||
| 2 | HASH JOIN | 1 | access: EMP.DEPTNO = DEPT.DEPTNO | |
| 3 | TABLE ACCESS FULL | 2 | DEPT | STORAGE FULL |
| 4 | TABLE ACCESS (BY INDEX ROWID BATCHED) | 2 | EMP | filter: SAL > 1000 |
| 5 | INDEX FULL SCAN | 4 | EMP_DEPT_IDX | filter: EMP.DEPTNO IS NOT NULL |
Execution order by applying the two rules:
3 (scan DEPT) ← first child of HASH JOIN (2), lowest ID
5 (index full scan EMP_DEPT) ← child of 4, so runs before 4
4 (EMP rows by rowid) ← second child of HASH JOIN (2)
2 (HASH JOIN) ← parent runs after both children
1 (SORT ORDER BY)
0 (SELECT STATEMENT) ← returns results
So: 3 → 5 → 4 → 2 → 1 → 0.
Takeaway: Find the deepest, most-indented child, work outward. Parents wait for their children; same-level siblings go in ID order.
Live check (26ai)
I built the same EMP/DEPT (14-row Scott dataset) + emp_dept_idx and ran the exact query. The reading rules held, though with only 14 rows the optimizer chose a MERGE JOIN instead of a HASH JOIN — a good reminder that plan shape depends on data volume and stats, while the reading order is universal:
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | MERGE JOIN | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX FULL SCAN | SYS_C009380 | (PK index)
| 5 | SORT JOIN | | access EMP.DEPTNO=DEPT.DEPTNO
| 6 | TABLE ACCESS FULL | EMP | filter SAL>1000
Reading order here: 4 → 3 → 6 → 5 → 2 → 1 → 0.
7. The traditional text-based plan (DBMS_XPLAN)
The classic way to see a plan is text output from DBMS_XPLAN.DISPLAY with format => 'typical':
EXPLAIN PLAN FOR
SELECT ename, dname FROM EMP emp, DEPT dept
WHERE emp.deptno = dept.deptno AND sal > 1000
ORDER BY empno;
SET LINES 4000
SET PAGES 5000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'typical'));
Two things to read:
- Indentation shows who is a child of who — a more-indented step feeds the less-indented step above it. (This is the visual form of the parent/child rule from section 6.)
- The star
*on anIdtells you a predicate is applied at that step. Look it up in the Predicate Information section below the plan to see the exactaccess(...)orfilter(...)condition.
Live check (26ai)
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 390 | 7 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 13 | 390 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 13 | 390 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | SYS_C009380 | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 13 | 221 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 13 | 221 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
6 - filter("SAL">1000)
Confirmed: the * appears on steps 5 and 6, and each maps to its predicate in the section below — step 6 filters SAL>1000, step 5 does the join access. The typical format also adds the Rows / Bytes / Cost (%CPU) / Time estimate columns that the BASIC format omits.
As before, my plan is a MERGE JOIN (14-row dataset); the slide's is a HASH JOIN. The
*-and-indentation reading technique is identical either way.
8. Joins — Nested Loops vs Hash vs Sort Merge
| Join | Typical access | Best for | Main resource |
|---|---|---|---|
| Nested Loops | Indexes | "Smaller" sets of data | Time (repeated lookups) |
| Hash | Full scans | "Larger" sets of data | Memory / CPU (builds a hash table) |
| Sort Merge | Sorted inputs | When you want data in join-key order | Memory (can be less than hash) |
Same query for all three:
SELECT ename, dname FROM EMP emp, DEPT dept
WHERE emp.deptno = dept.deptno AND sal > 1000
ORDER BY empno;
"Why no sort?" in the Nested Loops plan
The Hash and Sort Merge plans both end with SORT ORDER BY, but the Nested Loops plan doesn't. The reason: the outer (driving) row source is EMP read through an INDEX FULL SCAN of EMP_EMPNO_PK, so EMP rows come out already in empno order. A nested loop keeps the order of its outer input, so the result is already sorted for ORDER BY empno and the optimizer skips the sort.
Live check (26ai), with hints forcing each join method
Nested Loops (LEADING(emp dept) USE_NL(dept) INDEX(emp)):
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | filter SAL>1000
| 4 | INDEX FULL SCAN | SYS_C009381 | <- EMP PK (empno order)
|* 5 | INDEX UNIQUE SCAN | SYS_C009380 | <- DEPT PK lookup per row
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT |
Confirmed: no SORT ORDER BY. The two stacked NESTED LOOPS are the normal 11g+ "NL batching" shape (the inner loop gets DEPT index rowids and the outer one fetches DEPT rows). It's logically the same as the slide's single NL.
Hash (LEADING(dept emp) USE_HASH(emp) INDEX(emp emp_dept_idx)): plan hash 232383463, the same hash value as the slide:
| 1 | SORT ORDER BY | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | DEPT |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP |
| 5 | INDEX FULL SCAN | EMP_DEPT_IDX |
Sort Merge (LEADING(dept emp) USE_MERGE(emp) INDEX(emp emp_dept_idx)):
| 1 | SORT ORDER BY | |
| 2 | MERGE JOIN | |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT |
| 4 | INDEX FULL SCAN | SYS_C009380 | <- sorted, no SORT
| 5 | SORT JOIN | | <- still here!
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP |
| 7 | INDEX FULL SCAN | EMP_DEPT_IDX | <- already deptno order
Gotcha: SORT JOIN shows up even when the input is already sorted
The EMP side is read through EMP_DEPT_IDX, so it already comes out in deptno order, and the plan still has a SORT JOIN. I also ran it with NO_BATCH_TABLE_ACCESS_BY_ROWID(emp) (plain rowid access, no batching). The SORT JOIN was still there.
The reason is that in a sort merge join, only the first (driving) input can skip the sort when it's already in order, as DEPT does here. The second input always gets a SORT JOIN. That operation does more than sort: it buffers the rows so the merge can go back over them when the first input has duplicate join keys. If the data arrives already sorted, the sort is cheap, but the step is still there.
Two more things:
- Sort merge gives the data in join-key (
deptno) order. This query orders byempno, so a finalSORT ORDER BYis still required. BATCHEDrowid access can return rows out of index order, so you can't count on it to keep things sorted. As the hint test shows, though, batching is not the reason theSORT JOINappears.
Takeaway: Pick the join method by data volume. NL suits small row counts with indexes, hash suits big sets, and merge suits cases where sorted order is useful. When a plan has no sort, look at whether the driving input already arrives in the required order.
9. Important stats for a plan
- Use Active SQL Monitor, in OEM, SQL Developer, or
DBMS_SQLTUNE.REPORT_SQL_MONITOR. - Let the plan statistics lead you to the problem.
- Don't use
COSTas a tuning metric. Cost is the optimizer's own estimate, so when its inputs are wrong, the cost is wrong too.
The columns to watch:
| Column (OEM / SQL Developer) | Why it matters |
|---|---|
| Executions | How many times the step ran. A high count on a nested-loop inner side multiplies the work. |
| Est. Rows / Plan Rows vs Rows / Actual Rows | A big gap means a misestimate, which is usually the root cause of a bad plan. |
| Activity % | Where the time actually went. Tune that step first. |
Live check (26ai)
A function on the column hides it from the column stats, so the optimizer has to guess. For an equality predicate on an expression like this, the default guess is 1% of the rows:
SELECT /*+ MONITOR */ COUNT(*) FROM plan_demo WHERE UPPER(name) = 'NAME_0';
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id', type => 'TEXT') FROM dual;
| Id | Operation | Name | Rows | Cost | Execs | Rows |
| | | | (Estim) | | | (Actual) |
| 0 | SELECT STATEMENT | | | | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | | 1 | 1 |
| 2 | TABLE ACCESS FULL| PLAN_DEMO | 10 | 3 | 1 | 910 |
Confirmed: the optimizer estimated 10 rows (1% of 1000) and 910 came back, 91 times off. The cost of 3 gives no hint of this. Only the estimated vs actual comparison exposes it. The Activity column is empty because the query took 0.0004s: Activity comes from ASH samples, which are taken once per second, so only longer-running statements show it.
Licensing note: SQL Monitor requires the Diagnostics + Tuning Packs. Without them, you can get estimated vs actual rows for free with the
/*+ GATHER_PLAN_STATISTICS */hint plusDBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'), which shows E-Rows vs A-Rows.
10. But what about COST?
- Cost is the optimizer's guess at the "work" a plan will do. It doesn't directly relate to anything real, such as I/O or seconds.
- It's driven by the stats, which feed the math formulas from section 2.
- The optimizer uses it to choose between plans. The assumption is that lower cost means less work.
- That usually holds. But a low cost doesn't always mean the best plan. What really matters is how the query performs.
Live check (26ai): the lower-cost plan runs 2x slower
This is a typical stale-stats scenario. cost_fact was loaded with 10,000 rows and 100 evenly spread names, and stats were gathered. Then another 90,000 NAME_0 rows were loaded and the stats were not regathered:
stats num_rows=10000 actual=100000
I ran the join cost_fact → cost_dim with WHERE name = 'NAME_0' and GATHER_PLAN_STATISTICS, first with the optimizer's own choice, then with a hinted full scan:
Optimizer's choice, Cost 148:
| Id | Operation | Name | E-Rows | Cost | A-Rows | A-Time | Buffers |
| 2 | HASH JOIN | | 100 | 148 | 90100 | | 3707 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| COST_FACT | 100 | 101 | 90100 | | 3550 |
| 4 | INDEX RANGE SCAN | COST_FACT_NAME_IX | 100 | 1 | 90100 | | 638 |
| 5 | TABLE ACCESS FULL | COST_DIM | 10000 | 47 | 10000 | | 157 |
Total elapsed: 0.16s, 3707 buffers
Forced FULL(f), Cost 149 (higher):
| Id | Operation | Name | E-Rows | Cost | A-Rows | Buffers |
| 2 | HASH JOIN | | 100 | 149 | 90100 | 3304 |
| 3 | TABLE ACCESS FULL| COST_FACT | 100 | 102 | 90100 | 3147 |
| 4 | TABLE ACCESS FULL| COST_DIM | 10000 | 47 | 10000 | 157 |
Total elapsed: 0.08s, 3304 buffers
| Plan | Cost | Buffers | Elapsed |
|---|---|---|---|
| Optimizer's pick (index) | 148 (lower) | 3707 | 0.16s |
| Forced full scan | 149 | 3304 | 0.08s |
Confirmed: the lower-cost plan did more work and took twice as long. The cause is the misestimate: the optimizer expected 100 rows (E-Rows) and got 90,100 (A-Rows). With 100 rows, an index range scan is sensible. With 90% of the table, a full scan is better. The cost was accurate for the data the stats described, not for the data actually in the table.
Takeaway: Judge a plan by actual rows, buffers, and elapsed time. When the cost looks fine but the query is slow, compare E-Rows with A-Rows. A large gap means stale stats or skew, and fixing that fixes the cost.
11. Finding the problem
The slide shows a SQL Monitor report for a 213-line plan. You don't read all of it. The Activity column points straight at the problem: one line, an INDEX RANGE SCAN on PAY_RUN_RESULT_VALUES_N50, accounts for 75.58% of all activity. The other lines are close to 0%.
The same row also shows the pattern from sections 5 and 9:
- Executions in the hundreds of thousands (568K). It's the inner side of a stack of nested loops, so it runs once for every driving row.
- Est. Rows of about 4 per execution, against Actual Rows in the millions.
So the method is:
- Sort or scan by Activity % to find where the time goes.
- On that line, compare Executions and Est. vs Actual rows to see why.
- Walk up to its parent or driving step. The cause is often there (for example, a bad row estimate upstream that made nested loops look cheap).
Live check (26ai)
I forced a bad plan: a nested loop that full-scans cost_fact once for each of 2,000 driving rows.
SELECT /*+ MONITOR LEADING(d) USE_NL(f) FULL(f) */ COUNT(*)
FROM cost_dim d, cost_fact f
WHERE f.dim_id = d.id AND d.id <= 2000;
-- Elapsed: 23s, 6M buffer gets
| Id | Operation | Name | Rows | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | (Actual) | (%) | (# samples) |
| 0 | SELECT STATEMENT | | | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | | |
| 2 | NESTED LOOPS | | 1999 | 1 | 20000 | | |
| 3 | INDEX RANGE SCAN | COST_DIM_PK | 2000 | 1 | 2000 | | |
| 4 | TABLE ACCESS FULL| COST_FACT | 1 | 2000 | 20000 | 100.00 | Cpu (23) |
Confirmed: the Activity column shows 100% on line 4, with 23 CPU samples, one per second of runtime. Execs = 2000 explains why: a full scan of the whole table is repeated for each driving row. Unlike the 0.0004s query in section 9, this one ran long enough for ASH to collect samples, so Activity is filled in.
Takeaway: In a long plan, start from the Activity column rather than from the top of the plan. Then check Executions and estimated vs actual rows on the hot line to see why it's hot.
12. Found the problem, now what?
Finding the problem is usually easy. Fixing it might not be. The basic idea is "don't do that", and if you do need to do it, do it a different way.
| Issue | Possible fix |
|---|---|
| FULL TABLE SCAN | Appropriate index |
| INDEX SCAN | FULL TABLE SCAN |
| JOIN | A different join method (Hash, Nested Loops, Sort Merge), a different join order (LEADING or ORDERED hint), or a Common Table Expression (CTE) |
| Correlated subquery | CTE or ILV (inline view) |
| Repeatedly executed steps | CTE or ILV |
The first two rows point in opposite directions on purpose. Neither access method is always better. It depends on how many rows you need (see section 10).
Live check (26ai): fixing the section 11 query two ways
The bad plan was a nested loop that full-scanned cost_fact 2,000 times.
| Version | Plan | Elapsed | Buffers |
|---|---|---|---|
| Original | NL + TABLE ACCESS FULL × 2000 | 23s | 6M |
| Fix 1: different join | USE_HASH, scan cost_fact once | 0.03s | 3,152 |
| Fix 2: appropriate index | Keep NL, add cost_fact(dim_id) index | 0.02s | 300 |
Fix 2 is the "FULL TABLE SCAN → appropriate index" row of the table. The inner step still runs 2,000 times (Starts = 2000), but each run is now a cheap index range scan instead of a full scan.
Live check (26ai): correlated subquery → CTE
This query counts cost_dim rows that have more than 5 matching NAME_0 facts. NO_UNNEST forces Oracle to run the subquery once per outer row, which is what an un-rewritable correlated subquery does:
SELECT COUNT(*) FROM cost_dim d
WHERE d.id <= 2000
AND 5 < (SELECT /*+ NO_UNNEST */ COUNT(*) FROM cost_fact f
WHERE f.dim_id = d.id AND f.name = 'NAME_0');
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 3 | SORT AGGREGATE | | 2000 | 1 | 2000 | 00:00:28 | 618K |
| 9 | INDEX RANGE SCAN | COST_FACT_NAME_IX | 2000 | 10 | 180M | ... | 618K |
Total: 28s, 618K buffers
Line 9 is the repeated step. Because the cost_fact stats are stale (section 10), the optimizer expected 10 rows per run. Each run actually read all 90K NAME_0 index entries, and 2,000 runs added up to 180 million rows.
Rewritten as a CTE, the aggregation is done once and the result is joined:
WITH fcnt AS (
SELECT dim_id, COUNT(*) cnt FROM cost_fact
WHERE name = 'NAME_0' GROUP BY dim_id)
SELECT COUNT(*) FROM cost_dim d JOIN fcnt ON fcnt.dim_id = d.id
WHERE d.id <= 2000 AND fcnt.cnt > 5;
| Version | Elapsed | Buffers |
|---|---|---|
| Correlated subquery (per row) | 28s | 618K |
| CTE (aggregate once) | 0.08s | 583 |
Both return the same result (2000).
The same rewrite as an ILV (inline view), where the subquery goes directly in the FROM clause instead of a WITH block:
SELECT COUNT(*)
FROM cost_dim d,
(SELECT dim_id, COUNT(*) cnt FROM cost_fact
WHERE name = 'NAME_0' GROUP BY dim_id) fcnt
WHERE fcnt.dim_id = d.id AND d.id <= 2000 AND fcnt.cnt > 5;
-- 0.06s, 580 buffers: the HASH GROUP BY runs once (Starts = 1)
A CTE and an ILV are the same idea written two ways. The CTE is easier to read and can be reused within the query. I used /*+ MATERIALIZE */ in the CTE, so Oracle wrote its result to a temp table (TEMP TABLE TRANSFORMATION). The ILV was simply merged into the main plan. Both got rid of the per-row repetition.
Reading materialized CTE names: each materialized CTE shows up as a temp table named
SYS_TEMP_<xxxx>_<yyyy>. With several CTEs in one query, the first part (xxxx) changes for each CTE while the second part stays the same. Verified live with two CTEs:| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D662A_BDE689 | <- CTE a | 5 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D662B_BDE689 | <- CTE b | 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662B_BDE689 | <- reads b | 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662A_BDE689 | <- reads aMatch the
LOAD AS SELECTline (where the CTE is built) to theTABLE ACCESS FULLlines with the same name (where it's used) to see which CTE feeds which step.
In practice Oracle often unnests correlated subqueries on its own, turning them into joins. I needed
NO_UNNESTto reproduce the slow case. The CTE/ILV rewrite matters when the optimizer can't or won't do that transformation itself.
Takeaway: Once the Activity column shows where the time goes, change what that step does: a different access method, join method, or join order, or restructure the SQL so the expensive work runs once instead of per row.
13. Indexes: ways an index can be used
| Operation | What it does |
|---|---|
INDEX UNIQUE SCAN | Returns one rowid (equality on a unique/PK index). |
INDEX RANGE SCAN | Returns a set of rowids (could be just one). Ascending or descending. |
INDEX FULL SCAN | Reads every entry in key order along the bottom (leaf level) of the index. Ascending or descending. |
INDEX FAST FULL SCAN | Reads every entry, not in order (multiblock reads, like a full table scan of the index). |
INDEX SKIP SCAN | Skips over the leading column of a composite index. |
Index joins: two indexes on the same table are joined to get the needed columns without touching the table. This is always a HASH join, and the rowid is the join key.
Several of these already appeared in the live runs above: INDEX UNIQUE SCAN on COST_DIM_PK (section 12), INDEX FULL SCAN producing sorted output (sections 6 and 8), and an index join. The CTE and ILV plans in section 12 contain VIEW index$_join$_001, a HASH JOIN of COST_FACT_NAME_IX and COST_FACT_DIM_IX, because those two indexes together cover name and dim_id and the table never had to be read.
14. Why isn't an index being used?
- The indexed column is modified, e.g.
UPPER(name) = 'RIC'. An index onnamecan't be used, though a function-based index onUPPER(name)could be. - Implicit conversion. Strings always lose in a conversion: when a string column is compared with a number, Oracle converts the column, not the literal.
CAR_ID = 8975becomesTO_NUMBER(CAR_ID) = 8975, and the index onCAR_IDis no longer usable. The same applies to join columns (MCD.CAR_ID = PRT.CAR_ID) when one side is a number and the other a string. The conversion is visible in the plan's predicate section. If a conversion is needed, do it explicitly. - The optimizer estimated too many rows, so a full scan is cheaper. Know your data.
Live check (26ai): implicit conversion
car.car_id is VARCHAR2, indexed, with 100,000 rows:
-- WHERE car_id = 8975 (number literal)
|* 1 | TABLE ACCESS FULL | CAR |
1 - filter(TO_NUMBER("CAR_ID")=8975) <- column wrapped, index ignored
-- WHERE car_id = '8975' (string literal)
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CAR |
|* 2 | INDEX RANGE SCAN | CAR_ID_IX |
2 - access("CAR_ID"='8975')
Confirmed: the TO_NUMBER() Oracle added appears in the predicate section, and it turns an index range scan into a full table scan.
My addition: prefer LIKE over REGEXP_LIKE when LIKE is enough
A regular expression is a black box to the optimizer. It can't use column stats to estimate how many rows match, so it falls back to a fixed guess. A simple LIKE prefix can be estimated from the column stats and can also use an index range scan.
Same filter both ways (models starting with M1; actual matches = 22,000):
| Predicate | E-Rows | Actual |
|---|---|---|
model LIKE 'M1%' | 13,111 | 22,000 |
REGEXP_LIKE(model, '^M1') | 5,000 (5% fixed guess) | 22,000 |
The LIKE estimate is off too, because the column has no histogram, but it comes from the data. The REGEXP_LIKE estimate is always 5% of the table, whatever the pattern is. Inside a bigger query, a guess like that feeds join-order and join-method choices.
15. It's all about the predicates
- The optimizer is a math engine, so the predicates drive its decisions.
- A poorly written or complex predicate can make it difficult or impossible to estimate the number of rows.
- The simpler a predicate is, the better. Avoid functions when you can (as in sections 9, 14, and the
REGEXP_LIKEexample). - Use the Predicate Information section of the plan to map a bad step back to the part of the query text that causes it.
The slide's example is a typical WHERE clause from an application (Oracle payroll): a join condition and a stack of simple equality filters.
WHERE PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID
AND PAAM.LEGISLATION_CODE = 'US'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PPRD.PERSON_ID = PAAM.PERSON_ID
Each filter on PAAM is simple, but by default the optimizer treats them as independent and multiplies their selectivities. Flag columns like these are often correlated (for example, most active assignments are also primary), so the combined estimate can end up far too low. When the plan's predicate section shows all of these filters on one step and its E-Rows is much lower than its A-Rows, that is the place to look.
Summary
| Concept | One-liner |
|---|---|
| Statistics | Numbers describing the data's shape; the optimizer's map. |
| Static | Collected once, don't update as data changes. |
| Stale | Data changed enough that stats may be inaccurate — not always bad. |
| Base math | estimated rows = table rows × density, density = 1 / distinct values. |
| Skew | A few dominant values break the "even distribution" assumption. |
| Fix | Histograms + domain knowledge to inform the optimizer about skew. |
| Explain vs Execution | Explain = guess (no bind peeking); Execution = reality (peeks binds). |
| Reading plans | Read only the misbehaving subplan; trace bad steps to their feeding step. |
| Plan order | Inside-out, bottom-to-top: child before parent, same-parent siblings in ID order. |
| Text plan | Indentation = parent/child; * = predicate applied (see Predicate Information). |
| Joins | NL = small + indexes; Hash = large + full scans; Merge = sorted output, 2nd input always gets SORT JOIN. |
| Plan stats | Use SQL Monitor; compare Est vs Actual rows, Executions, Activity %; ignore COST. |
| COST | Optimizer's guess from stats; lower cost ≠ faster (live: cost 148 ran 2x slower than 149). |
| Finding the problem | Go straight to the highest Activity % line, then check its Executions and Est vs Actual rows. |
| Fixing it | "Don't do that": change access/join method or order; CTE/ILV so repeated work runs once. |
| Index access | Unique / range / full (ordered) / fast full (unordered) / skip scan; index join = hash on rowid. |
| Index not used | Function on column, implicit conversion (string loses), or too many rows. Prefer LIKE over REGEXP_LIKE. |
| Predicates | Simple predicates = better estimates; map bad steps back via Predicate Information. |
No comments:
Post a Comment