Performance Optimization Basics

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

How Oracle's Query Optimiser Works

Every SQL statement you submit to Oracle passes through the Cost-Based Optimiser (CBO) before it runs. The CBO analyses the statement, considers all the ways it could be executed — different join orders, different access paths, index scans vs full table scans — and assigns a cost to each. It picks the plan with the lowest estimated cost and executes that. Cost is measured in terms of I/O, CPU, and memory, based on statistics Oracle collects about your tables and indexes.

When the CBO makes a bad choice — choosing a full table scan when an index would be faster, or joining tables in the wrong order — it is almost always because the statistics are stale or missing. Fresh, accurate statistics are the single most important factor in getting good execution plans. The second most important is writing queries that give the optimiser the best chance of using your indexes.

• The CBO uses table and index statistics to estimate the cost of each possible plan
• Statistics are gathered with DBMS_STATS.GATHER_TABLE_STATS
• Stale statistics cause bad plans — Oracle can gather them automatically but manual gathering is sometimes needed after large data loads
• The execution plan shows exactly what Oracle decided to do — reading it is the first step in diagnosing a slow query
• An INDEX RANGE SCAN means Oracle used an index — a TABLE ACCESS FULL means it did not

Reading an Execution Plan

An execution plan shows the sequence of operations Oracle performs to execute a query. Use EXPLAIN PLAN FOR followed by your query, then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) to read it. The plan is displayed as a tree — child operations feed into parent operations. Execution starts at the innermost, most-indented line. The most important columns are Operation (what Oracle is doing), Cost (the CBO's estimate), and Rows (the estimated row count at each step).

-- Generate and display the execution plan for a query
EXPLAIN PLAN FOR
SELECT
    c.first_name || ' ' || c.last_name  AS customer_name,
    COUNT(o.order_id)                   AS total_orders,
    SUM(o.total_amount)                 AS total_spent
FROM   dataplexa_store.customers  c
JOIN   dataplexa_store.orders     o ON c.customer_id = o.customer_id
WHERE  o.order_date >= DATE '2024-01-01'
GROUP  BY c.customer_id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 500
ORDER  BY total_spent DESC;
-- Display the stored plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• Read the plan bottom-up within each branch — line 6 (INDEX RANGE SCAN) feeds into line 4 (HASH JOIN), which feeds upward
• TABLE ACCESS FULL on CUSTOMERS is acceptable here — with only 42 rows a full scan is cheaper than an index lookup
• INDEX RANGE SCAN on orders uses the index on order_date — the WHERE clause on order_date is being handled efficiently
• A high Cost number relative to similar queries, or a TABLE ACCESS FULL on a large table, are the two most common signals that a query needs attention

Gathering Statistics

The CBO cannot make good decisions without current statistics. Statistics describe the shape of your data — how many rows a table has, how many distinct values a column contains, the distribution of values. Oracle's automatic statistics job runs nightly by default but it may miss tables that change significantly during the day. After a large bulk load or a major data change, gather statistics manually before running queries against the affected tables.

-- Gather statistics on a single table
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname   => 'DATAPLEXA_STORE',   -- schema name -- must be uppercase
        tabname   => 'ORDERS',            -- table name -- must be uppercase
        cascade   => TRUE                 -- also gather stats on all indexes of this table
    );
END;
/
-- Gather statistics on the entire schema
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(
        ownname  => 'DATAPLEXA_STORE',
        cascade  => TRUE
    );
END;
/
-- Check when statistics were last gathered on a table
SELECT table_name,
       num_rows,
       last_analyzed
FROM   user_tables
WHERE  table_name = 'ORDERS';

• cascade => TRUE gathers statistics on both the table and all of its indexes in one call — always include it
• Schema and table names must be passed in uppercase — DBMS_STATS is case-sensitive for these parameters
• last_analyzed in USER_TABLES shows when statistics were last collected — a NULL or very old date is a strong signal that a query plan may be suboptimal
• Gathering statistics on a very large table can be resource-intensive — schedule it during off-peak hours or use sampling with the estimate_percent parameter

Common Performance Problems and Fixes

Most slow Oracle queries fall into a small number of patterns. Recognising these patterns and knowing the fix for each is more valuable than any single tuning technique. The most common are: functions applied to indexed columns in WHERE, implicit type conversions, fetching far more rows or columns than needed, and missing indexes on foreign key columns.

-- PROBLEM 1: Function on an indexed column suppresses the index
-- SLOW: TRUNC() wraps order_date -- index on order_date cannot be used
SELECT * FROM dataplexa_store.orders WHERE TRUNC(order_date) = DATE '2024-06-01';
-- FAST: range condition -- index on order_date is used
SELECT * FROM dataplexa_store.orders
WHERE  order_date >= DATE '2024-06-01'
AND    order_date <  DATE '2024-06-02';
-- PROBLEM 2: SELECT * fetches every column -- use only the columns you need
-- SLOW: retrieves all columns including large VARCHAR2 and CLOB columns
SELECT * FROM dataplexa_store.orders WHERE customer_id = 14;
-- FAST: fetch only what the query needs
SELECT order_id, order_date, total_amount
FROM   dataplexa_store.orders
WHERE  customer_id = 14;
-- PROBLEM 3: Missing index on a foreign key column
-- orders.customer_id is a FK -- without an index every JOIN scans the full orders table
CREATE INDEX idx_orders_customer_id ON dataplexa_store.orders (customer_id);
-- PROBLEM 4: Implicit type conversion suppresses an index
-- customer_id is NUMBER -- passing it as a string forces conversion on every row
SELECT * FROM dataplexa_store.orders WHERE customer_id = '14';   -- SLOW
SELECT * FROM dataplexa_store.orders WHERE customer_id = 14;     -- FAST

• Oracle does not automatically create indexes on foreign key columns — this is one of the most common missed optimisations in Oracle schemas
• SELECT * is convenient for exploration but should never appear in production queries — fetching unused columns wastes I/O and network bandwidth
• Implicit type conversion is silent — Oracle will run the query without an error, but performance degrades because the index cannot be used
• After adding an index, run EXPLAIN PLAN again to confirm the optimiser is now using it

Using Hints

When the optimiser consistently makes a wrong choice — usually because statistics cannot capture the true data distribution — you can override it with a hint. A hint is a comment placed immediately after SELECT that instructs the optimiser to use a specific index, join method, or access path. Hints are a last resort — fix the underlying statistics problem first, and use hints only when no other solution works. Hints are not errors if they are wrong or misspelled — Oracle silently ignores invalid hints.

-- Force Oracle to use a specific index with the INDEX hint
SELECT /*+ INDEX(o IDX_ORDERS_CUSTOMER_ID) */
    order_id, order_date, total_amount
FROM   dataplexa_store.orders o
WHERE  customer_id = 14;
-- Force a full table scan even if an index exists -- useful in bulk SELECT operations
SELECT /*+ FULL(o) */
    order_id, total_amount
FROM   dataplexa_store.orders o;
-- Force a specific join method -- HASH join for large tables
SELECT /*+ USE_HASH(c o) */
    c.first_name, o.order_id, o.total_amount
FROM   dataplexa_store.customers c
JOIN   dataplexa_store.orders    o ON c.customer_id = o.customer_id;

• Hints are written inside /*+ ... */ immediately after the SELECT keyword — the + distinguishes a hint from a regular comment
• A misspelled hint name is silently ignored — Oracle does not raise an error, making debugging hints tricky
• Hints become stale as data volumes change — a hint that improved performance today may hurt it a year from now when data distribution shifts
• Always try fixing statistics, rewriting the query, and adding appropriate indexes before reaching for a hint

Lesson Summary

Practice Questions

Quiz

Next up — Users, Roles & Privileges — How Oracle manages database access through users, system and object privileges, and roles.