The Anatomy of a Slow Query#

When a query takes 10 minutes to run in Oracle RMS (or any large Oracle database), the problem rarely lies in the hardware. It almost always stems from the database taking a highly inefficient path to retrieve the data.

A query can be slow for many reasons:

1. Missing or Stale Statistics: The database thinks a table has 10 rows when it actually has 10 million.
2. Missing Indexes: The database is forced to read every single row on the disk (Full Table Scan) to find one specific record.
3. CPU-Intensive Joins: The database is joining two massive tables using an inefficient loop instead of a hash.
4. Hard Parsing: The application is not using bind variables, forcing the database to recompile the query on every execution.

To fix a slow query, you must stop guessing and start measuring. You must understand how the database intends to execute your code.

The Cost-Based Optimizer (CBO)#

When you submit a SQL statement to Oracle, the database doesn't just execute it blindly. It passes it to a highly sophisticated piece of software called the Cost-Based Optimizer (CBO).

The CBO's job is to evaluate hundreds or thousands of potential execution plans for your query and choose the one with the lowest "Cost."

Cost is an abstract unit of work, roughly representing the estimated time and resources (I/O, CPU, Memory) required to fetch the data.

For example, if you want to find ITEM = 12345 in the ITEM_MASTER table, the CBO might consider:

1. Path A: Read the entire table from top to bottom (Cost: 5,000).
2. Path B: Look up 12345 in an index, then fetch that one specific row (Cost: 3).

Naturally, the CBO chooses Path B. But how does the CBO know that Path B is cheaper?

The Importance of DBMS_STATS#

The CBO makes its decisions based on Optimizer Statistics. These statistics are metadata about your tables and indexes, stored in the Data Dictionary.

Statistics include:

• How many rows are in the table?
• How many distinct values are in the DEPT column?
• What are the minimum and maximum values of the CREATE_DATE column?
• How deep is the index B-Tree?

If your statistics are stale, the CBO makes terrible decisions.

To fix this, you manually gather statistics using the DBMS_STATS package:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'RMS', 
  tabname => 'TRAN_DATA', 
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade => TRUE -- Also gathers stats on all indexes
);

Hard Parsing vs. Soft Parsing (Bind Variables)#

Before a query can be executed, it must be parsed.

1. Hard Parse: The database has never seen this exact query string before. It must validate the syntax, check privileges, generate thousands of execution plans, and pick the best one. This consumes a massive amount of CPU and locks memory structures.
2. Soft Parse: The database recognizes the query string from a previous execution. It retrieves the saved execution plan from memory (the Shared Pool) and skips the hard work.

The Bind Variable Rule

If you write dynamic SQL or application code that concatenates values into the string, you force a Hard Parse every single time.

v_sql := 'SELECT first_name FROM users WHERE user_id = ' || p_id;

-- PERFECT: Causes one hard parse, then millions of soft parses.
v_sql := 'SELECT first_name FROM users WHERE user_id = :id';

In Oracle, always use bind variables (:id, ?) when a value changes between executions.

Reading Execution Plans#

The most important tool in a performance tuner's arsenal is the EXPLAIN PLAN. It shows you exactly what the CBO intends to do.

You generate a plan using the EXPLAIN PLAN FOR command, followed by DBMS_XPLAN.DISPLAY.

EXPLAIN PLAN FOR
SELECT i.item, i.item_desc, s.supplier
FROM item_master i
JOIN item_supplier s ON i.item = s.item
WHERE i.dept = 145;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

The output looks like a tree structure.

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   500 | 25000 |    50   (2)|
|*  1 |  HASH JOIN                   |                  |   500 | 25000 |    50   (2)|
|   2 |   TABLE ACCESS BY INDEX ROWID| ITEM_MASTER      |   500 | 15000 |    10   (0)|
|*  3 |    INDEX RANGE SCAN          | ITEM_MASTER_I1   |   500 |       |     2   (0)|
|   4 |   TABLE ACCESS FULL          | ITEM_SUPPLIER    |  100K | 2000K |    35   (3)|
-----------------------------------------------------------------------------------------

How to read the tree

1. Read the tree inside-out and top-to-bottom. The most indented operation happens first.
2. If two operations are at the same indentation level, the top one happens first.

In the example above:

1. Oracle does an INDEX RANGE SCAN on ITEM_MASTER_I1 to find the dept = 145.
2. It uses the ROWID from the index to fetch the actual rows via TABLE ACCESS BY INDEX ROWID.
3. Simultaneously, it performs a TABLE ACCESS FULL on ITEM_SUPPLIER.
4. It joins the two result sets together using a HASH JOIN.

Understanding Access Paths (Indexes vs. FTS)#

An Access Path is how Oracle retrieves data from a table.

Understanding Join Methods#

When joining two tables, Oracle chooses one of three methods:

1. Nested Loops Join:

   • How it works: For every row in Table A (the driving table), it looks up the matching row in Table B.
   • When to use: Excellent for joining small datasets (e.g., retrieving a single item and joining to its descriptions).
   • Warning: Terrible for large datasets. Looping 1 million times takes forever.

2. Hash Join:

   • How it works: Reads the smaller table into memory and builds a Hash Table. Then it scans the larger table and probes the Hash Table for matches.
   • When to use: The gold standard for joining large datasets or data warehousing queries.

3. Sort Merge Join:

   • How it works: Sorts Table A, sorts Table B, and then merges them together.
   • When to use: Used when joining with inequalities (<, >) or when the data is already pre-sorted via an index.

Rewriting Queries for Performance#

Sometimes the CBO needs help. You can rewrite queries to give the optimizer better options.

1. Avoid Functions on Indexed Columns

If you have an index on create_date, this query will not use the index:

SELECT * FROM orders WHERE TRUNC(create_date) = TRUNC(SYSDATE);

Rewrite it to evaluate the literal, not the column:

SELECT * FROM orders 
WHERE create_date >= TRUNC(SYSDATE) 
  AND create_date < TRUNC(SYSDATE) + 1;

2. Replace OR with UNION ALL

The OR operator is notoriously difficult to optimize.

SELECT * FROM items WHERE dept = 10 OR supplier = 500;

-- Good: Can use two different indexes
SELECT * FROM items WHERE dept = 10
UNION ALL
SELECT * FROM items WHERE supplier = 500;

Using Optimizer Hints#

If the CBO refuses to pick the right plan (perhaps because statistics are skewed), you can force it using Hints. Hints are special comments placed immediately after the SELECT, INSERT, UPDATE, or DELETE keyword.

SELECT /*+ FULL(i) */ item, desc FROM item_master i;

-- Force the use of a specific index
SELECT /*+ INDEX(i item_master_i1) */ item FROM item_master i WHERE dept = 10;

-- Force a Parallel execution (great for massive ETL)
SELECT /*+ PARALLEL(t, 4) */ SUM(amount) FROM tran_data t;

Common Gotchas for Developers#

Key Takeaways#