Database Advanced

Oracle PL/SQL Performance Tuning to Prevent ORA-06512 Timeouts

📅 2026-04-14 ⏱ 13 min read 🎯 Advanced

Where developers are forged. · Structured learning · Free forever.

📍 Part of: PL/SQL → Topic 25 of 27

Link between slow queries, loops, and ORA-06512.

🔥 Advanced — solid Database foundation required

In this tutorial, you'll learn

Link between slow queries, loops, and ORA-06512.

Cursor loops are the number one cause of ORA-06512 timeouts — total time = iteration count × per-iteration cost, and small per-iteration costs become massive at scale
FORALL replaces DML loops and BULK COLLECT with LIMIT replaces query loops — both are 100x to 400x faster than cursor loops by eliminating the PL/SQL-to-SQL context switch
Missing foreign key indexes are the single most common root cause of full table scans inside loops — Oracle does not auto-index FKs

✦ Plain-English analogy ✦ Real code with output ✦ Interview questions

⚡Quick Answer

ORA-06512 from timeouts means a PL/SQL block exceeded a resource limit or an application timeout — it is a performance problem, not a logic error
Slow queries inside cursor loops are the number one cause — a 50ms query repeated 100,000 times takes 83 minutes
Bulk operations (FORALL for DML, BULK COLLECT with LIMIT for queries) replace row-by-row processing and reduce elapsed time by 100x to 400x
Missing indexes on join and filter columns cause full table scans inside loops — the difference between 2ms and 30 seconds per iteration
Production insight: the majority of ORA-06512 timeout errors in ETL systems trace back to a single unindexed foreign key column inside a cursor loop
Biggest mistake: tuning the PL/SQL logic or adding parallel hints when the real bottleneck is a missing index on a join column — fix the access path first, then fix the loop

🚨 START HERE

Performance-Driven ORA-06512 Quick Debug

Real commands to identify and fix performance bottlenecks causing timeout errors

🟡Procedure hangs for hours then raises ORA-06512

Immediate ActionKill the session and enable SQL tracing before re-running

Commands

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE);

Fix NowRun tkprof on the trace file: tkprof <trace_file> output.txt sys=no sort=prsela,exeela — the top statement by elapsed time is the bottleneck

🟡Cursor loop is the bottleneck — 10K+ iterations with per-iteration DML

Immediate ActionMeasure the loop iteration count and per-iteration query time

Commands

SELECT COUNT(*) FROM (<cursor_query>);

SELECT /*+ gather_plan_statistics */ ... FROM ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

Fix NowReplace the cursor loop with BULK COLLECT ... LIMIT 5000 for the fetch and FORALL for all DML inside the loop

🟡Full table scan on a large table — EXPLAIN PLAN shows TABLE ACCESS FULL

Immediate ActionIdentify the filter and join columns used in the query's WHERE and ON clauses

Commands

EXPLAIN PLAN FOR <the_query>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL +PREDICATE'));

SELECT column_name, num_distinct, num_nulls FROM dba_tab_columns WHERE table_name = 'TABLE_NAME' AND owner = 'SCHEMA' AND column_name IN ('JOIN_COL','FILTER_COL');

Fix NowCREATE INDEX idx_name ON schema.table_name(join_col, filter_col) TABLESPACE idx_data;

🔴Session killed with ORA-00028 — exceeded Oracle resource profile limit

Immediate ActionCheck the profile's resource limits for the failing user

Commands

SELECT profile, resource_name, limit FROM dba_profiles WHERE profile = (SELECT profile FROM dba_users WHERE username = 'ETL_USER') AND resource_type = 'KERNEL';

SELECT sid, serial#, username, status, last_call_et, event FROM v$session WHERE username = 'ETL_USER';

Fix NowCreate a dedicated profile with IDLE_TIME UNLIMITED for service accounts: CREATE PROFILE etl_profile LIMIT IDLE_TIME UNLIMITED; ALTER USER etl_user PROFILE etl_profile;

🟡Query plan changed after data growth — optimizer chose a different, worse plan

Immediate ActionGather fresh statistics on all tables involved in the slow query

Commands

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE_NAME',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

SELECT table_name, num_rows, blocks, last_analyzed FROM dba_tables WHERE table_name = 'TABLE_NAME' AND owner = 'SCHEMA';

Fix NowIf statistics are current but the plan is still wrong, pin the known-good plan: EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'abc123def456');

Production IncidentMissing Index on Foreign Key Turned a 5-Minute ETL Into a 6-Hour TimeoutA nightly ETL procedure joined a 100-million-row fact table to a 50,000-row dimension table on an unindexed foreign key column. Each join triggered a full table scan. The procedure timed out after 6 hours with ORA-06512.

SymptomAt 01:00 AM, the nightly ETL procedure began failing with ORA-06512: at "APP_USER.ETL_PKG", line 342. The application timeout was set to 6 hours. The procedure had been completing successfully in under 5 minutes for the previous 8 months. No code changes had been deployed. No schema changes were visible in the change log.

AssumptionThe team assumed a data volume increase was the cause — the fact table had grown from 80 million to 100 million rows over the 8-month period. They considered partitioning the fact table, adding PARALLEL hints to the cursor query, and increasing the application timeout to 12 hours. All three would have provided partial relief but none would have addressed the root cause.

Root causeThe dimension table's primary key was indexed (Oracle auto-indexes primary keys), but the foreign key column on the fact table — customer_dim_id — was not indexed. Oracle does not automatically create indexes on foreign key columns. Every join between the fact table and the dimension table inside the cursor loop forced a full table scan on the 100-million-row fact table. Each scan took approximately 30 seconds. The cursor loop iterated 50,000 times (once per dimension row). Total theoretical time: 50,000 × 30 seconds = 416 hours. The application timeout killed the session at 6 hours with ORA-06512. The procedure had completed in under 5 minutes for 8 months because the fact table was small enough (80 million rows) that full scans completed in about 5 milliseconds when the data fit in the buffer cache. When the table grew past the buffer cache capacity, scans began hitting disk, and per-iteration time jumped from 5ms to 30 seconds — a 6,000x increase that turned a 4-minute procedure into a 416-hour one.

FixImmediate fix: created the missing index on the foreign key column. CREATE INDEX idx_fact_customer_dim_fk ON fact_orders(customer_dim_id) TABLESPACE idx_data PARALLEL 4; ALTER INDEX idx_fact_customer_dim_fk NOPARALLEL; The full table scan was replaced with an index range scan. Per-iteration cost dropped from 30 seconds to 2 milliseconds. Total procedure time dropped from 6+ hours to 4 minutes. The index was approximately 200 MB on a 50 GB table — negligible storage cost for a 6,000x performance improvement. Longer-term changes: 1. Replaced the cursor loop with a single MERGE statement that joined the tables set-based, eliminating the loop entirely. Procedure time dropped from 4 minutes to 12 seconds. 2. Added a weekly audit query that checks all foreign key columns for missing indexes and alerts the DBA team. 3. Added DBMS_STATS.GATHER_TABLE_STATS to the ETL post-load step to ensure the optimizer always has current statistics.

Key Lesson

Every foreign key column used in joins must have an index — Oracle does not auto-index foreign keys, and the performance difference is 15,000xA 30-second query inside a 50,000-iteration loop is a 416-hour operation — always calculate total cost (iterations × per-iteration time) before deploying cursor loopsORA-06512 timeouts are performance problems, not logic errors — tune the query access path and eliminate the loop before touching the procedure logicData volume growth triggers timeout failures months after deployment — monitor per-query execution time continuously, not just total procedure time

Production Debug GuideFrom timeout error to performance bottleneck identification

ORA-06512 after hours of execution — procedure never completes→Enable SQL tracing before re-running: EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE). After the procedure completes or times out, find the trace file (SELECT value FROM v$diag_info WHERE name='Default Trace File') and analyze with tkprof: tkprof trace_file output.txt sys=no sort=prsela,exeela. The statement with the highest elapsed time is the bottleneck.

ORA-06512 at a specific line number inside a cursor loop→Extract the SQL statement at that line from the procedure source. Run EXPLAIN PLAN FOR <the_statement> and SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY). Look for TABLE ACCESS FULL on any table with more than 100,000 rows — this is the bottleneck. Add an index on the filter or join columns used in the statement.

Procedure runs for hours, then the session is killed — ORA-06512 with ORA-00028→ORA-00028 means the session was killed by a resource limit or by a DBA. Check the user's profile: SELECT resource_name, limit FROM dba_profiles WHERE profile = (SELECT profile FROM dba_users WHERE username = 'THE_USER') AND resource_type = 'KERNEL'. If IDLE_TIME or CONNECT_TIME is set to a low value, the session was killed because the procedure was too slow to complete within the limit.

Cursor loop processes one row per iteration — total iteration count exceeds 10,000→Replace the cursor loop with FORALL for DML operations or BULK COLLECT with LIMIT for queries. A 100,000-iteration cursor loop with 2ms per iteration takes 200 seconds. The same operation with FORALL takes approximately 0.5 seconds. If the loop body contains both a SELECT and DML, use BULK COLLECT to fetch the data in batches and FORALL to process each batch.

EXPLAIN PLAN shows HASH JOIN on large tables — no index is used for the join→The join column on one or both tables lacks an index. A HASH JOIN reads both tables fully into memory. For loops where the join runs repeatedly, add an index on the inner table's join column: CREATE INDEX idx_name ON inner_table(join_column). Verify with EXPLAIN PLAN that the plan changes to NESTED LOOPS with INDEX RANGE SCAN on the inner table.

Procedure was fast last week but is slow now — no code changes deployed→Check if optimizer statistics are stale: SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name IN ('TABLE1','TABLE2'). If last_analyzed is older than 7 days or num_rows does not reflect the current data volume, gather fresh statistics: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE'). Also check for plan changes: query v$sql for the sql_id and compare plan_hash_value against the historical value in DBA_HIST_SQLSTAT.

ORA-06512 timeout errors are performance failures disguised as code errors. The error stack points to a line in a PL/SQL procedure, so engineers naturally focus on the code at that line. But the real problem is that the code is too slow — a query takes too long, a loop iterates too many times, or the combination of both creates an operation that exceeds the resource limit or the application timeout.

The connection between performance and ORA-06512 is direct: when a SQL statement inside a PL/SQL block exceeds a resource profile limit (CPU_PER_CALL, LOGICAL_READS_PER_CALL, IDLE_TIME, CONNECT_TIME), Oracle kills the session with ORA-00028 and the error propagates through the PL/SQL call stack as ORA-06512. When the application's JDBC statement timeout or connection timeout fires before the procedure completes, the driver cancels the statement and ORA-01013 (user requested cancel) propagates as ORA-06512. In both cases, the procedure did not fail because of a logic error — it failed because it could not complete in time.

The diagnostic path starts with identifying the bottleneck: is it a slow query (missing index, full table scan), an excessive loop (row-by-row processing instead of bulk operations), or a combination of both? A single full table scan on a 100-million-row table takes 30 seconds. That is tolerable if it runs once. Inside a loop that runs 1,000 times, it becomes 8.3 hours. The fix is adding an index to eliminate the full scan (dropping per-iteration cost from 30 seconds to 2 milliseconds) and replacing the cursor loop with a bulk operation (eliminating the loop entirely). Either fix alone may be sufficient; applying both is the standard approach.

The Loop Problem: Row-by-Row Is Slow-by-Slow

The most common cause of ORA-06512 timeout errors is a cursor loop that processes rows one at a time. Each iteration executes one or more SQL statements — a query, an insert, an update, or a delete. The per-iteration cost is small in isolation (typically 1 to 10 milliseconds), but the total elapsed time is the per-iteration cost multiplied by the iteration count. This arithmetic is deceptively simple and catastrophically important.

A cursor loop that processes 100,000 rows with a 2ms query per iteration takes 200 seconds (3.3 minutes). If the per-iteration cost degrades to 50ms due to data growth exceeding the buffer cache, the same loop takes 5,000 seconds (83 minutes). If the cost degrades to 500ms because a missing index forces a full table scan on every iteration, it takes 50,000 seconds (nearly 14 hours). The code did not change — only the per-iteration cost changed — and the procedure went from completing in 3 minutes to timing out.

The fix is to eliminate the loop entirely. Oracle provides two bulk processing mechanisms that send all rows to the SQL engine in a single round-trip, eliminating the PL/SQL-to-SQL context switch overhead that makes cursor loops slow.

FORALL replaces INSERT, UPDATE, and DELETE loops. Instead of executing one DML statement per iteration, FORALL sends all statements to the SQL engine at once. The SQL engine processes them in a single operation with a single set of redo and undo entries.

BULK COLLECT replaces SELECT cursor fetch loops. Instead of fetching one row per FETCH call, BULK COLLECT fetches all rows (or a batch controlled by the LIMIT clause) in a single round-trip. The rows are stored in PL/SQL collections (nested tables or varrays) for processing.

The performance difference is dramatic and consistent: a 100,000-row INSERT loop takes approximately 200 seconds. The same INSERT with FORALL takes approximately 0.5 seconds. That is a 400x improvement — and it comes not from tuning the query itself but from eliminating the per-iteration context switch overhead between the PL/SQL engine and the SQL engine.

The SAVE EXCEPTIONS clause in FORALL allows the bulk operation to continue when individual rows fail. Without it, the first error stops the entire FORALL and rolls back all rows. With SAVE EXCEPTIONS, Oracle continues processing remaining rows and records each failure in the SQL%BULK_EXCEPTIONS collection, which you can iterate after the FORALL to log or handle each failed row individually.

io/thecodeforge/performance/loop_elimination.sql · SQL

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145

-- ANTI-PATTERN: Cursor loop — row-by-row processing
-- 100,000 iterations × 2ms per iteration = 200 seconds
CREATE OR REPLACE PROCEDURE app_schema.process_orders_slow IS
  CURSOR c_orders IS
    SELECT order_id, customer_id, amount
    FROM data_owner.staging_orders
    WHERE processed_flag = 'N';

  v_count NUMBER := 0;
BEGIN
  FOR rec IN c_orders LOOP
    -- Each iteration: 1 INSERT + 1 UPDATE + context switch overhead
    INSERT INTO data_owner.orders (order_id, customer_id, amount)
    VALUES (rec.order_id, rec.customer_id, rec.amount);

    UPDATE data_owner.staging_orders
    SET processed_flag = 'Y',
        processed_date = SYSDATE
    WHERE order_id = rec.order_id;

    v_count := v_count + 1;
    IF MOD(v_count, 10000) = 0 THEN
      COMMIT;  -- periodic commit to limit undo usage
    END IF;
  END LOOP;

  COMMIT;
END process_orders_slow;
/

-- CORRECT: FORALL — bulk DML in a single round-trip
-- 100,000 rows in ~0.5 seconds (400x faster)
CREATE OR REPLACE PROCEDURE app_schema.process_orders_fast IS
  TYPE t_order_id    IS TABLE OF data_owner.staging_orders.order_id%TYPE;
  TYPE t_customer_id IS TABLE OF data_owner.staging_orders.customer_id%TYPE;
  TYPE t_amount      IS TABLE OF data_owner.staging_orders.amount%TYPE;

  v_order_ids    t_order_id;
  v_customer_ids t_customer_id;
  v_amounts      t_amount;
BEGIN
  -- BULK COLLECT: fetch all qualifying rows in a single round-trip
  SELECT order_id, customer_id, amount
  BULK COLLECT INTO v_order_ids, v_customer_ids, v_amounts
  FROM data_owner.staging_orders
  WHERE processed_flag = 'N';

  -- FORALL INSERT: single round-trip for all rows
  FORALL i IN 1 .. v_order_ids.COUNT
    INSERT INTO data_owner.orders (order_id, customer_id, amount)
    VALUES (v_order_ids(i), v_customer_ids(i), v_amounts(i));

  -- FORALL UPDATE: single round-trip for all rows
  FORALL i IN 1 .. v_order_ids.COUNT
    UPDATE data_owner.staging_orders
    SET processed_flag = 'Y',
        processed_date = SYSDATE
    WHERE order_id = v_order_ids(i);

  COMMIT;
END process_orders_fast;
/

-- CORRECT: BULK COLLECT with LIMIT — memory-controlled bulk processing
-- Processes in batches of 5,000 — constant, predictable memory usage
CREATE OR REPLACE PROCEDURE app_schema.process_orders_batched IS
  CURSOR c_source IS
    SELECT order_id, customer_id, amount
    FROM data_owner.staging_orders
    WHERE processed_flag = 'N'
    ORDER BY order_id;

  TYPE t_order_rec IS RECORD (
    order_id    data_owner.staging_orders.order_id%TYPE,
    customer_id data_owner.staging_orders.customer_id%TYPE,
    amount      data_owner.staging_orders.amount%TYPE
  );
  TYPE t_order_tab IS TABLE OF t_order_rec;

  v_batch      t_order_tab;
  v_batch_size CONSTANT PLS_INTEGER := 5000;
  v_total      PLS_INTEGER := 0;
BEGIN
  OPEN c_source;
  LOOP
    FETCH c_source BULK COLLECT INTO v_batch LIMIT v_batch_size;
    EXIT WHEN v_batch.COUNT = 0;

    FORALL i IN 1 .. v_batch.COUNT
      INSERT INTO data_owner.orders (order_id, customer_id, amount)
      VALUES (v_batch(i).order_id, v_batch(i).customer_id, v_batch(i).amount);

    FORALL i IN 1 .. v_batch.COUNT
      UPDATE data_owner.staging_orders
      SET processed_flag = 'Y',
          processed_date = SYSDATE
      WHERE order_id = v_batch(i).order_id;

    v_total := v_total + v_batch.COUNT;
    COMMIT;  -- commit per batch — limits undo and allows progress tracking

    app_schema.logger_pkg.info(
      'process_orders_batched',
      'Batch complete: ' || v_batch.COUNT || ' rows (total: ' || v_total || ')'
    );
  END LOOP;
  CLOSE c_source;

  app_schema.logger_pkg.info(
    'process_orders_batched',
    'Processing complete: ' || v_total || ' rows total'
  );
END process_orders_batched;
/

-- FORALL with SAVE EXCEPTIONS — continue on individual row failures
CREATE OR REPLACE PROCEDURE app_schema.process_with_error_handling IS
  TYPE t_order_id IS TABLE OF data_owner.staging_orders.order_id%TYPE;
  v_order_ids t_order_id;
  v_error_count PLS_INTEGER;
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  SELECT order_id BULK COLLECT INTO v_order_ids
  FROM data_owner.staging_orders WHERE processed_flag = 'N';

  BEGIN
    FORALL i IN 1 .. v_order_ids.COUNT SAVE EXCEPTIONS
      INSERT INTO data_owner.orders (order_id)
      VALUES (v_order_ids(i));
  EXCEPTION
    WHEN e_bulk_errors THEN
      v_error_count := SQL%BULK_EXCEPTIONS.COUNT;
      FOR j IN 1 .. v_error_count LOOP
        app_schema.logger_pkg.error(
          'process_with_error_handling',
          'Row ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX
          || ' failed: ORA-' || LPAD(SQL%BULK_EXCEPTIONS(j).ERROR_CODE, 5, '0')
        );
      END LOOP;
  END;

  COMMIT;
END process_with_error_handling;
/

Mental Model

Loop Cost Formula

Total elapsed time = iteration count × per-iteration cost — small per-iteration costs become massive at scale.

100K iterations × 2ms = 200 seconds (3.3 minutes) — acceptable
100K iterations × 50ms = 5,000 seconds (83 minutes) — approaching timeout territory
100K iterations × 500ms = 50,000 seconds (nearly 14 hours) — guaranteed timeout
FORALL eliminates the per-iteration context switch: 100K rows in ~0.5 seconds — 400x faster
Always calculate total cost before deploying a cursor loop: iterations × per-iteration time = total elapsed time

📊 Production Insight

Cursor loops multiply per-iteration cost by iteration count — a fast query becomes slow at scale.

A 50ms query × 100K iterations = 83 minutes — the query is fast, the loop is the problem.

Rule: if the loop iteration count exceeds 10,000, use FORALL or BULK COLLECT with LIMIT — never process row-by-row.

🎯 Key Takeaway

Cursor loops are the number one cause of ORA-06512 timeout errors — small per-iteration costs become massive at scale.

FORALL replaces DML loops. BULK COLLECT with LIMIT replaces query loops. Both eliminate the PL/SQL-to-SQL context switch overhead.

Bottom line: if your procedure has a cursor loop with 10,000+ iterations, it is a performance time bomb waiting for data growth to pull the pin.

Indexing Strategies: Eliminating Full Table Scans

A full table scan on a 100-million-row table takes approximately 30 seconds on typical storage. An index lookup on the same table takes approximately 2 milliseconds. That is a 15,000x difference. When a query inside a loop triggers a full scan on every iteration, the total time is the scan cost multiplied by the iteration count — and the procedure times out.

The most commonly missing index in Oracle databases is on foreign key columns. Oracle automatically creates a unique index on primary key columns, but it does not create any index on foreign key columns. This is by design — Oracle leaves the indexing decision to the developer — but it means that every join between a parent table and a child table on an unindexed foreign key forces a full scan of the child table. In data warehouse and ETL environments where fact tables have foreign keys to dimension tables, this single oversight causes more ORA-06512 timeouts than any other factor.

Composite indexes — indexes on multiple columns — serve both WHERE clause filters and JOIN conditions in a single index structure. The column order in a composite index matters: Oracle can use the index for queries that filter on the leading column(s), but not for queries that filter only on trailing columns. Place the most selective column first (the column that eliminates the most rows) and the join column second.

Function-based indexes handle queries that apply functions to columns in the WHERE clause. A query like WHERE UPPER(last_name) = 'SMITH' cannot use a regular index on last_name because the function transforms the column value. The index must match the function: CREATE INDEX idx_upper_name ON customers(UPPER(last_name)). Without the function-based index, Oracle performs a full table scan.

Covering indexes include all columns referenced by the query — both in the WHERE clause and in the SELECT list. When a covering index exists, Oracle reads only the index and never accesses the table at all. The I/O cost drops from the table size to the index size, which is typically 5 to 20 times smaller.

io/thecodeforge/performance/indexing_strategies.sql · SQL

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586

-- Strategy 1: Foreign key index — eliminates full table scans on joins
-- Without this index, every join to dimension tables triggers a full scan
CREATE INDEX data_owner.idx_fact_customer_fk
  ON data_owner.fact_orders(customer_dim_id)
  TABLESPACE idx_data;

-- Strategy 2: Composite index — serves filter + join in a single structure
-- Column order: most selective column first
CREATE INDEX data_owner.idx_orders_status_date
  ON data_owner.orders(status, order_date, customer_id)
  TABLESPACE idx_data;
-- Supports: WHERE status = 'PENDING' AND order_date > SYSDATE - 30
-- Supports: WHERE status = 'PENDING' (leading column match)
-- Does NOT efficiently support: WHERE order_date > SYSDATE - 30 (skips leading column)

-- Strategy 3: Function-based index — handles function-wrapped predicates
CREATE INDEX data_owner.idx_customers_upper_name
  ON data_owner.customers(UPPER(last_name), UPPER(first_name))
  TABLESPACE idx_data;
-- Supports: WHERE UPPER(last_name) = 'SMITH'
-- Without this index, Oracle performs a full table scan

-- Strategy 4: Covering index — avoids table access entirely
CREATE INDEX data_owner.idx_orders_covering
  ON data_owner.orders(customer_id, status, order_date, amount)
  TABLESPACE idx_data;
-- If the SELECT list contains only these columns plus ROWID,
-- Oracle reads the index only — no table access, I/O cost is index size

-- Strategy 5: Index-organized table — the table IS the index
CREATE TABLE app_schema.config_settings (
  setting_key   VARCHAR2(100) PRIMARY KEY,
  setting_value VARCHAR2(500),
  updated_date  DATE DEFAULT SYSDATE
) ORGANIZATION INDEX
  TABLESPACE app_data;
-- No separate table access — lookups by primary key read the IOT directly
-- Ideal for lookup tables, configuration tables, and small reference tables

-- Diagnostic: Find ALL foreign key columns missing indexes
-- This is the single most valuable query for preventing performance-driven ORA-06512
SELECT
  c.table_name,
  c.constraint_name,
  cc.column_name AS fk_column,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM dba_ind_columns ic
      WHERE ic.table_name  = c.table_name
        AND ic.table_owner = c.owner
        AND ic.column_name = cc.column_name
        AND ic.column_position = 1  -- FK column must be leading column
    ) THEN 'INDEXED'
    ELSE '*** MISSING INDEX ***'
  END AS index_status,
  t.num_rows
FROM dba_constraints c
JOIN dba_cons_columns cc
  ON c.constraint_name = cc.constraint_name
  AND c.owner = cc.owner
LEFT JOIN dba_tables t
  ON t.table_name = c.table_name
  AND t.owner = c.owner
WHERE c.constraint_type = 'R'  -- Foreign key constraints only
  AND c.owner = 'DATA_OWNER'
ORDER BY t.num_rows DESC NULLS LAST, c.table_name;
-- Any row with '*** MISSING INDEX ***' on a table with >100K rows
-- is a performance problem waiting to happen

-- Diagnostic: Find SQL statements performing full table scans in the current workload
SELECT
  sql_id,
  SUBSTR(sql_text, 1, 120) AS sql_preview,
  executions,
  ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
  ROUND(elapsed_time / NULLIF(executions, 0) / 1000, 1) AS ms_per_exec,
  ROUND(rows_processed / NULLIF(executions, 0)) AS rows_per_exec
FROM v$sql
WHERE buffer_gets / NULLIF(executions, 0) > 50000  -- high I/O per execution
  AND executions > 10
  AND parsing_schema_name NOT IN ('SYS','SYSTEM')
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;
-- High gets_per_exec with low rows_per_exec = full table scan returning few rows
-- Check EXPLAIN PLAN for each sql_id: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id'));

Mental Model

Index Selection Decision Tree

The right index eliminates the full table scan — the wrong index wastes storage and slows DML without helping queries.

Foreign key column used in joins: ALWAYS index — Oracle does not auto-index FKs, and the join cost without it is catastrophic
Filter column in WHERE with high selectivity (eliminates >95% of rows): index it — the optimizer will use it
Join column on the inner table of a nested loop: index it — nested loops require an index on the inner table
Column wrapped in a function (UPPER, TRUNC, TO_CHAR): function-based index — regular index cannot be used
Low-cardinality column (status with 3 values, type with 5 values): bitmap index for data warehouse only — skip for OLTP due to lock contention

📊 Production Insight

Missing foreign key indexes are the single most common cause of performance-driven ORA-06512 errors in ETL and reporting systems.

Oracle does not auto-index foreign keys — every join on an unindexed FK triggers a full table scan.

Rule: run the FK index audit query weekly — any FK column without an index on a table with more than 100,000 rows is a production incident waiting to happen.

🎯 Key Takeaway

A full table scan takes 30 seconds — an index lookup takes 2 milliseconds — the difference is 15,000x.

Foreign keys, filter columns, and join columns must be indexed. Oracle does not create these indexes automatically.

Bottom line: if EXPLAIN PLAN shows TABLE ACCESS FULL on a large table inside a loop, add an index before tuning anything else — the index alone may resolve the timeout.

Query Optimization: EXPLAIN PLAN and SQL Tracing

Before tuning a query, you must understand why it is slow. Guessing — adding hints, rewriting joins, adjusting parameters — without understanding the execution plan is the most common waste of tuning effort. Oracle provides two complementary diagnostic tools: EXPLAIN PLAN shows the optimizer's chosen execution path, and SQL tracing captures the actual runtime behavior including elapsed time, wait events, and actual row counts.

EXPLAIN PLAN shows the access method for each table (TABLE ACCESS FULL, INDEX RANGE SCAN, INDEX UNIQUE SCAN), the join method (HASH JOIN, NESTED LOOPS, MERGE JOIN), the estimated row counts, and the relative cost of each step. The most common bottleneck pattern is TABLE ACCESS FULL on a table with millions of rows — this means no usable index exists for the query's filter or join conditions.

SQL tracing with DBMS_MONITOR captures what actually happened during execution: actual elapsed time, actual CPU time, actual I/O waits, actual row counts at each step, and bind variable values. The tkprof utility formats the trace file into a readable report sorted by elapsed time. The statement at the top of the tkprof output — the one consuming the most elapsed time — is the bottleneck.

The optimizer chooses execution plans based on table and column statistics: num_rows, num_distinct, density, histogram data. When statistics are stale — the table has grown significantly since the last GATHER_TABLE_STATS — the optimizer's row estimates are wrong, and it may choose a suboptimal plan. A common example: the optimizer estimates 100 rows (based on stale statistics showing 10,000 total rows) and chooses NESTED LOOPS, but the actual row count is 5 million (the table grew to 100 million rows), and HASH JOIN would have been orders of magnitude faster. Gathering fresh statistics is always the first step in any tuning exercise.

Bind variable peeking can cause plan instability. On the first execution of a statement with bind variables, the optimizer peeks at the actual bind value and chooses a plan optimized for that value. If subsequent executions use very different values (e.g., a status column where 'ACTIVE' returns 95% of rows and 'CANCELLED' returns 0.1%), the plan that was optimal for the first value may be catastrophic for later values. Adaptive cursor sharing (available since 11g) mitigates this by allowing Oracle to maintain multiple execution plans for the same SQL statement and choose the right one based on the bind value distribution.

io/thecodeforge/performance/query_optimization.sql · SQL

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899

-- Step 1: Enable SQL tracing for the current session
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
  waits     => TRUE,   -- capture I/O wait events
  binds     => TRUE,   -- capture bind variable values
  plan_stat => 'ALL_EXECUTIONS'  -- capture plan stats for every execution
);

-- Step 2: Run the slow procedure
EXEC app_schema.process_orders_slow;

-- Step 3: Disable tracing
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE;

-- Step 4: Find the trace file
SELECT value AS trace_file
FROM v$diag_info
WHERE name = 'Default Trace File';
-- Copy this path and run tkprof from the OS:
-- $ tkprof /path/to/trace_file.trc output.txt sys=no sort=prsela,exeela
-- The first statement in output.txt (highest elapsed time) is the bottleneck

-- Step 5: Analyze the execution plan for a specific query
EXPLAIN PLAN FOR
  SELECT o.order_id, c.customer_name, o.amount
  FROM data_owner.orders o
  JOIN data_owner.customers c ON o.customer_id = c.customer_id
  WHERE o.status = 'PENDING'
    AND o.order_date > SYSDATE - 30;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(
  format => 'ALL +PREDICATE +PROJECTION'
));
-- Look for:
--   TABLE ACCESS FULL on tables with >100K rows — missing index
--   HASH JOIN where NESTED LOOPS is expected — missing index on join column
--   High COST on a single step — that step is the bottleneck
--   Estimated ROWS much lower than actual — stale statistics

-- Step 6: Check for stale statistics
SELECT
  table_name,
  num_rows,
  blocks,
  last_analyzed,
  ROUND(SYSDATE - last_analyzed, 1) AS days_since_analyzed,
  CASE
    WHEN last_analyzed IS NULL THEN '*** NEVER ANALYZED ***'
    WHEN last_analyzed < SYSDATE - 7 THEN '*** STALE — gather stats ***'
    ELSE 'Current'
  END AS stats_status
FROM dba_tables
WHERE owner = 'DATA_OWNER'
  AND table_name IN ('ORDERS', 'CUSTOMERS', 'STAGING_ORDERS', 'FACT_ORDERS')
ORDER BY num_rows DESC;

-- Step 7: Gather fresh statistics
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'DATA_OWNER',
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,   -- also gather statistics on all indexes
    degree           => DBMS_STATS.AUTO_DEGREE
  );
END;
/

-- Step 8: Pin a known-good plan with SQL Plan Management (if plan regressed)
DECLARE
  v_plans_loaded PLS_INTEGER;
BEGIN
  v_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc123def456'  -- the SQL_ID of the statement with the good plan
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded into SPM baseline: ' || v_plans_loaded);
END;
/

-- Step 9: Monitor a currently running long statement in real time
SELECT
  s.sid,
  s.serial#,
  s.sql_id,
  s.event,
  s.seconds_in_wait,
  ROUND(s.last_call_et / 60, 1) AS minutes_active,
  q.buffer_gets,
  q.disk_reads,
  ROUND(q.elapsed_time / 1000000, 1) AS elapsed_seconds,
  SUBSTR(q.sql_text, 1, 100) AS sql_preview
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.username = 'DATA_OWNER'
  AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;
-- 'db file sequential read' with high seconds_in_wait = single-block I/O (index reads, possibly slow storage)
-- 'db file scattered read' with high seconds_in_wait = multi-block I/O (full table scan)
-- 'enq: TX - row lock contention' = waiting for another session's lock

💡EXPLAIN PLAN Red Flags

TABLE ACCESS FULL on tables with >100K rows — add an index on the filter or join column
HASH JOIN where NESTED LOOPS is expected — the inner table's join column lacks an index
COST > 10,000 on a single step — that step dominates the total execution time
Estimated ROWS vs. actual ROWS differ by >10x — statistics are stale, gather fresh stats immediately
Multiple TABLE ACCESS FULL steps in the same plan — the query needs multiple indexes or a redesign

📊 Production Insight

EXPLAIN PLAN reveals the optimizer's chosen access path — TABLE ACCESS FULL on large tables is the most common bottleneck.

Stale statistics cause the optimizer to choose wrong plans — always gather stats before assuming the index is missing.

Rule: check EXPLAIN PLAN and statistics freshness first — tune the access path, not the SQL text.

🎯 Key Takeaway

EXPLAIN PLAN shows the optimizer's chosen path — SQL tracing captures actual runtime performance.

TABLE ACCESS FULL on large tables and HASH JOIN without indexes are the two most common bottlenecks causing ORA-06512 timeouts.

Bottom line: if statistics are stale, the optimizer cannot choose the right plan — gather stats before changing any code or indexes.

Bulk Operations: FORALL and BULK COLLECT Deep Dive

FORALL and BULK COLLECT are Oracle's bulk processing mechanisms. They eliminate the PL/SQL-to-SQL context switch — the round-trip between the PL/SQL engine and the SQL engine that occurs on every single DML statement or FETCH call in a cursor loop. This context switch typically costs 0.5 to 2 milliseconds per iteration. At 100,000 iterations, the context switch overhead alone is 50 to 200 seconds — even if the SQL statement itself is instantaneous.

FORALL replaces DML loops (INSERT, UPDATE, DELETE, MERGE). Instead of executing one DML statement per loop iteration, FORALL sends the entire collection of DML statements to the SQL engine in a single round-trip. The SQL engine processes them as a batch, generating a single set of redo log entries rather than one per row. The performance improvement scales linearly with collection size: 100x to 400x faster than the equivalent cursor loop.

BULK COLLECT replaces cursor FETCH loops. Instead of fetching one row per FETCH call (which is what a FOR rec IN cursor LOOP does internally), BULK COLLECT fetches all rows — or a batch of rows controlled by the LIMIT clause — in a single round-trip. The rows are stored in PL/SQL collections.

The LIMIT clause is critical for memory management. BULK COLLECT without LIMIT fetches the entire result set into PGA memory at once. For a query that returns 10 million rows with 200 bytes per row, that is 2 GB of PGA memory consumed by a single session. With LIMIT 5000, only 1 MB is consumed per batch — and the collection is reused for each batch, so total memory usage stays constant regardless of the total result set size. The optimal batch size depends on row width and available PGA memory; start with 1,000 to 5,000 and tune upward only if needed.

The SAVE EXCEPTIONS clause in FORALL is the production-grade approach for handling individual row failures in bulk operations. Without SAVE EXCEPTIONS, the first error stops the entire FORALL and Oracle rolls back all rows processed in that FORALL call. With SAVE EXCEPTIONS, Oracle continues processing the remaining rows and records each failure in the SQL%BULK_EXCEPTIONS collection. After the FORALL, you iterate over SQL%BULK_EXCEPTIONS to log each failure — the collection provides the index of the failed row (ERROR_INDEX) and the Oracle error code (ERROR_CODE). The FORALL raises ORA-24381 (error(s) in array DML) after completing, which you catch with a named exception.

io/thecodeforge/performance/bulk_operations.sql · SQL

-- Pattern 1: Simple BULK COLLECT + FORALL with LIMIT
-- Memory-controlled, production-grade bulk processing
CREATE OR REPLACE PROCEDURE app_schema.process_large_dataset IS
  CURSOR c_source IS
    SELECT order_id, customer_id, amount, status
    FROM data_owner.staging_orders
    WHERE processed_flag = 'N'
    ORDER BY order_id;

  TYPE t_rec IS RECORD (
    order_id    data_owner.staging_orders.order_id%TYPE,
    customer_id data_owner.staging_orders.customer_id%TYPE,
    amount      data_owner.staging_orders.amount%TYPE,
    status      data_owner.staging_orders.status%TYPE
  );
  TYPE t_rec_tab IS TABLE OF t_rec;

  v_rows       t_rec_tab;
  v_batch_size CONSTANT PLS_INTEGER := 5000;
  v_total      PLS_INTEGER := 0;
BEGIN
  OPEN c_source;
  LOOP
    FETCH c_source BULK COLLECT INTO v_rows LIMIT v_batch_size;
    EXIT WHEN v_rows.COUNT = 0;

    FORALL i IN 1 .. v_rows.COUNT
      INSERT INTO data_owner.orders (order_id, customer_id, amount, status)
      VALUES (v_rows(i).order_id, v_rows(i).customer_id,
              v_rows(i).amount, v_rows(i).status);

    FORALL i IN 1 .. v_rows.COUNT
      UPDATE data_owner.staging_orders
      SET processed_flag = 'Y',
          processed_date = SYSDATE
      WHERE order_id = v_rows(i).order_id;

    v_total := v_total + v_rows.COUNT;
    COMMIT;  -- commit per batch for recoverability

    app_schema.logger_pkg.info(
      'process_large_dataset',
      'Batch: ' || v_rows.COUNT || ' rows | Total: ' || v_total
    );
  END LOOP;
  CLOSE c_source;

  app_schema.logger_pkg.info(
    'process_large_dataset',
    'Complete: ' || v_total || ' rows processed'
  );
END process_large_dataset;
/

-- Pattern 2: FORALL with SAVE EXCEPTIONS — production error handling
CREATE OR REPLACE PROCEDURE app_schema.process_with_error_logging IS
  TYPE t_ids IS TABLE OF data_owner.staging_orders.order_id%TYPE;
  TYPE t_amts IS TABLE OF data_owner.staging_orders.amount%TYPE;

  v_ids  t_ids;
  v_amts t_amts;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);

  v_error_count PLS_INTEGER;
BEGIN
  SELECT order_id, amount
  BULK COLLECT INTO v_ids, v_amts
  FROM data_owner.staging_orders
  WHERE processed_flag = 'N';

  BEGIN
    FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
      INSERT INTO data_owner.orders (order_id, amount)
      VALUES (v_ids(i), v_amts(i));
  EXCEPTION
    WHEN e_bulk_errors THEN
      v_error_count := SQL%BULK_EXCEPTIONS.COUNT;
      app_schema.logger_pkg.warn(
        'process_with_error_logging',
        v_error_count || ' rows failed out of ' || v_ids.COUNT
      );
      FOR j IN 1 .. v_error_count LOOP
        app_schema.logger_pkg.error(
          'process_with_error_logging',
          'Row index=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX
          || ' order_id=' || v_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)
          || ' ORA-' || LPAD(SQL%BULK_EXCEPTIONS(j).ERROR_CODE, 5, '0')
        );
      END LOOP;
  END;

  COMMIT;
END process_with_error_logging;
/

-- Pattern 3: Set-based alternative — eliminate PL/SQL entirely
-- When possible, replace the entire procedure with a single SQL statement
MERGE INTO data_owner.orders tgt
USING (
  SELECT order_id, customer_id, amount, status
  FROM data_owner.staging_orders
  WHERE processed_flag = 'N'
) src
ON (tgt.order_id = src.order_id)
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, status)
  VALUES (src.order_id, src.customer_id, src.amount, src.status);

UPDATE data_owner.staging_orders
SET processed_flag = 'Y', processed_date = SYSDATE
WHERE processed_flag = 'N';

COMMIT;
-- A single MERGE + UPDATE replaces the entire procedure
-- No PL/SQL loop, no context switch, no batch management
-- This is the fastest option when the logic is simple enough

Mental Model

Bulk vs. Row-by-Row Performance

Bulk operations eliminate the PL/SQL-to-SQL context switch — the performance improvement scales linearly with row count.

1K rows: cursor loop ~2s, FORALL ~0.01s — 200x faster
10K rows: cursor loop ~20s, FORALL ~0.05s — 400x faster
100K rows: cursor loop ~200s (3.3 min), FORALL ~0.5s — 400x faster
1M rows: cursor loop ~2,000s (33 min), FORALL ~5s — 400x faster
Always use LIMIT with BULK COLLECT — unbounded collections consume all available PGA memory
When possible, eliminate PL/SQL entirely — a single MERGE statement is faster than FORALL

📊 Production Insight

FORALL is 100x to 400x faster than cursor loops for DML operations — the improvement comes from eliminating context switches, not from SQL tuning.

BULK COLLECT without LIMIT on a 10M-row result set consumes gigabytes of PGA memory per session — always use LIMIT.

Rule: if the iteration count exceeds 10,000, use bulk operations — and if the logic is simple enough, consider replacing the entire procedure with a single SQL MERGE statement.

🎯 Key Takeaway

FORALL replaces DML loops. BULK COLLECT with LIMIT replaces query loops. Both eliminate the PL/SQL-to-SQL context switch overhead.

The performance improvement scales linearly: 100x to 400x faster for 10K+ rows.

Bottom line: if your cursor loop processes 10,000+ rows with per-iteration DML, it is leaving two to three orders of magnitude of performance on the table.

Resource Limits and Timeout Configuration

Oracle resource profiles enforce hard limits on session resource consumption: CPU time per call, logical reads per call, total session elapsed time, and idle time. When a PL/SQL block exceeds any of these limits, Oracle kills the session with ORA-00028 (your session has been killed) and the error propagates through the PL/SQL call stack as ORA-06512. The procedure did not have a bug — it was killed because it consumed more resources than the profile allows.

The RESOURCE_LIMIT initialization parameter controls whether profile limits are enforced. If set to FALSE, profile limits are defined but not enforced — every procedure runs until completion regardless of resource consumption. If set to TRUE (the default in most production environments), limits are actively enforced and sessions are killed when exceeded.

The most dangerous limit for long-running procedures is IDLE_TIME — the maximum number of minutes a session can be idle before Oracle kills it. The critical subtlety is that Oracle considers a session 'idle' whenever it is not actively executing a SQL statement on the CPU. A session that is waiting for I/O — reading blocks from disk during a full table scan — appears idle to Oracle. A session that is waiting for a lock — blocked by another session's uncommitted DML — also appears idle. This means a procedure that performs heavy I/O or encounters lock waits can be killed by IDLE_TIME even though it is actively doing work.

Application-side timeouts are independent of Oracle profile limits and are equally common causes of ORA-06512. JDBC's Statement.setQueryTimeout() cancels the statement after N seconds. Connection pool idle timeouts close connections that have been checked out but inactive for too long. Socket-level TCP timeouts terminate the connection if no data is received within the timeout period. All of these surface as ORA-06512 in the PL/SQL error stack.

The fix for resource limit timeouts is two-pronged: first, optimize the procedure to reduce resource consumption (add indexes, use bulk operations, eliminate full table scans) so it completes within the limits. Second, create a dedicated resource profile for service accounts that run long-running procedures, with appropriate limits for their workload — typically IDLE_TIME UNLIMITED and a CONNECT_TIME that matches the longest expected ETL window.

io/thecodeforge/performance/resource_limits.sql · SQL

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586

-- Check if resource limits are enforced on this database
SELECT name, value
FROM v$parameter
WHERE name = 'resource_limit';
-- TRUE:  profile limits are enforced — sessions will be killed
-- FALSE: profile limits exist but are not enforced

-- Check the profile and its limits for a specific user
SELECT
  p.profile,
  p.resource_name,
  p.limit
FROM dba_profiles p
WHERE p.profile = (
  SELECT profile FROM dba_users WHERE username = 'ETL_USER'
)
  AND p.resource_type = 'KERNEL'
ORDER BY p.resource_name;
-- Key limits to check:
--   CPU_PER_CALL:             max CPU centiseconds per single SQL call
--   LOGICAL_READS_PER_CALL:   max buffer gets per single SQL call
--   IDLE_TIME:                max idle minutes before session kill
--   CONNECT_TIME:             max total session minutes (idle + active)
--   CPU_PER_SESSION:          max CPU centiseconds per entire session
--   LOGICAL_READS_PER_SESSION: max buffer gets per entire session

-- Create a dedicated profile for long-running ETL and batch service accounts
CREATE PROFILE etl_service_profile LIMIT
  CPU_PER_CALL              UNLIMITED
  CPU_PER_SESSION           UNLIMITED
  LOGICAL_READS_PER_CALL    UNLIMITED
  LOGICAL_READS_PER_SESSION UNLIMITED
  IDLE_TIME                 UNLIMITED   -- I/O waits appear idle; do not kill
  CONNECT_TIME              720         -- 12 hours max total session time
  SESSIONS_PER_USER         10
  FAILED_LOGIN_ATTEMPTS     5
  PASSWORD_LIFE_TIME        180;

-- Assign the profile to the ETL service account
ALTER USER etl_user PROFILE etl_service_profile;

-- Verify the assignment
SELECT username, profile
FROM dba_users
WHERE username = 'ETL_USER';

-- Monitor resource consumption of active sessions in real time
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.sql_id,
  s.event,
  s.seconds_in_wait,
  ROUND(s.last_call_et / 60, 1) AS minutes_active,
  ss.value AS logical_reads,
  ROUND(st.value / 100, 1) AS cpu_seconds
FROM v$session s
JOIN v$sesstat ss ON s.sid = ss.sid
  AND ss.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'session logical reads')
JOIN v$sesstat st ON s.sid = st.sid
  AND st.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session')
WHERE s.username = 'ETL_USER'
  AND s.status = 'ACTIVE'
ORDER BY ss.value DESC;

-- Check for sessions recently killed by resource limits
SELECT
  username,
  os_username,
  timestamp,
  returncode,
  action_name,
  SUBSTR(sql_text, 1, 100) AS sql_preview
FROM dba_audit_trail
WHERE username = 'ETL_USER'
  AND returncode IN (
    28,    -- ORA-00028: your session has been killed
    1013,  -- ORA-01013: user requested cancel of current operation
    2396   -- ORA-02396: exceeded maximum idle time
  )
  AND timestamp > SYSDATE - 7
ORDER BY timestamp DESC;
-- ORA-00028: killed by profile limit or DBA intervention
-- ORA-01013: cancelled by application timeout (JDBC queryTimeout)
-- ORA-02396: specifically killed by IDLE_TIME limit

⚠ Timeout Sources — Database and Application

📊 Production Insight

IDLE_TIME kills sessions that appear idle during I/O waits — a full table scan that waits on disk reads looks idle to Oracle.

A default profile with IDLE_TIME = 10 kills every ETL procedure that performs more than 10 minutes of I/O.

Rule: create a dedicated profile with IDLE_TIME UNLIMITED for service accounts that run batch and ETL procedures — then optimize the procedure to run faster, not the timeout to be longer.

🎯 Key Takeaway

Oracle profiles enforce resource limits — exceeding any limit kills the session, which surfaces as ORA-06512 in the PL/SQL stack.

IDLE_TIME is the most dangerous limit for batch procedures because I/O waits appear as idle time.

Bottom line: if your service account uses the DEFAULT profile with IDLE_TIME = 10, every long-running procedure is one full table scan away from being killed.

Monitoring and Continuous Performance Assurance

Performance-driven ORA-06512 errors are preventable with continuous monitoring. The key principle is that performance degradation is gradual — a query that takes 10 milliseconds at deployment can take 10 seconds six months later after the table grows 10x. By the time it causes an ORA-06512 timeout, the degradation has been building for weeks or months. Monitoring catches the degradation trend before it crosses the timeout threshold.

The key metrics to monitor are: SQL elapsed time per execution (from v$sql), buffer gets per execution (a proxy for I/O volume — high values indicate full table scans), execution plan changes (a new plan_hash_value for the same sql_id indicates a plan regression), and statistics freshness (days since last GATHER_TABLE_STATS on tables with significant data changes).

AWR (Automatic Workload Repository) captures hourly snapshots of database performance including the top SQL statements by elapsed time, CPU time, and buffer gets. Comparing AWR reports across time periods — this week vs. last month — reveals performance degradation trends before they cause timeouts. The ADDM (Automatic Database Diagnostic Monitor) automatically analyzes AWR data and recommends actions: create an index, gather statistics, adjust a parameter.

SQL Plan Management (SPM) captures and preserves execution plans. When the optimizer generates a new plan for a statement (due to a statistics update, an Oracle upgrade, or a parameter change), SPM can automatically reject the new plan if it performs worse than the baseline plan. This prevents plan regression — a known-good plan being replaced by a worse one — which is a frequent cause of sudden ORA-06512 timeouts on procedures that 'worked fine for months.'

Real-time monitoring with v$session and v$sql identifies active long-running statements. A statement with high buffer_gets and increasing elapsed_time is the current bottleneck. Alerting on statements that exceed a threshold — for example, any statement active for more than 5 minutes or consuming more than 1 million buffer gets — catches performance problems before they cause ORA-06512 timeouts.

io/thecodeforge/monitor/performance_monitoring.sql · SQL

-- Monitor 1: Top SQL by logical I/O (buffer gets per execution)
-- High gets_per_exec with low rows_per_exec = full table scan returning few rows
SELECT
  sql_id,
  SUBSTR(sql_text, 1, 100) AS sql_preview,
  executions,
  buffer_gets,
  ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
  ROUND(elapsed_time / NULLIF(executions, 0) / 1000, 1) AS ms_per_exec,
  ROUND(rows_processed / NULLIF(executions, 0)) AS rows_per_exec
FROM v$sql
WHERE buffer_gets / NULLIF(executions, 0) > 50000
  AND executions > 10
  AND parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
ORDER BY gets_per_exec DESC
FETCH FIRST 20 ROWS ONLY;
-- Investigate each sql_id with:
-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_here'));

-- Monitor 2: Active long-running statements in real time
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.sql_id,
  s.event,
  s.seconds_in_wait,
  ROUND(s.last_call_et / 60, 1) AS minutes_active,
  SUBSTR(q.sql_text, 1, 80) AS sql_preview
FROM v$session s
LEFT JOIN v$sql q
  ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
  AND s.last_call_et > 300  -- active for more than 5 minutes
ORDER BY s.last_call_et DESC;
-- Alert threshold: any user statement active for >5 minutes

-- Monitor 3: Stale statistics on large tables
SELECT
  owner,
  table_name,
  num_rows,
  ROUND(SYSDATE - last_analyzed, 1) AS days_since_analyzed,
  stale_stats  -- Oracle 12c+: 'YES' if Oracle considers stats stale
FROM dba_tab_statistics
WHERE owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
  AND num_rows > 100000
  AND (stale_stats = 'YES' OR last_analyzed < SYSDATE - 7 OR last_analyzed IS NULL)
ORDER BY num_rows DESC;
-- Alert on: any table with >100K rows and stale or missing statistics

-- Monitor 4: Missing foreign key indexes on large tables
-- (Same diagnostic from the indexing section — schedule weekly)
SELECT
  c.owner,
  c.table_name,
  cc.column_name AS fk_column,
  t.num_rows,
  'MISSING INDEX' AS status
FROM dba_constraints c
JOIN dba_cons_columns cc
  ON c.constraint_name = cc.constraint_name AND c.owner = cc.owner
JOIN dba_tables t
  ON t.table_name = c.table_name AND t.owner = c.owner
WHERE c.constraint_type = 'R'
  AND c.owner NOT IN ('SYS', 'SYSTEM')
  AND t.num_rows > 100000
  AND NOT EXISTS (
    SELECT 1 FROM dba_ind_columns ic
    WHERE ic.table_name = c.table_name
      AND ic.table_owner = c.owner
      AND ic.column_name = cc.column_name
      AND ic.column_position = 1
  )
ORDER BY t.num_rows DESC;
-- Every row returned is a potential ORA-06512 timeout

-- Monitor 5: Execution plan changes (plan regression detection)
SELECT
  h.sql_id,
  h.plan_hash_value,
  h.snap_id,
  sn.end_interval_time,
  ROUND(h.elapsed_time_total / NULLIF(h.executions_total, 0) / 1000, 1) AS ms_per_exec
FROM dba_hist_sqlstat h
JOIN dba_hist_snapshot sn ON h.snap_id = sn.snap_id AND h.instance_number = sn.instance_number
WHERE h.sql_id = 'your_sql_id_here'
  AND sn.end_interval_time > SYSDATE - 30
ORDER BY sn.end_interval_time;
-- If plan_hash_value changes AND ms_per_exec increases significantly:
-- the plan regressed — consider pinning the old plan with SPM

-- Schedule daily monitoring job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'APP_SCHEMA.DAILY_PERF_AUDIT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      BEGIN
        -- Check for stale stats on large tables
        -- Check for missing FK indexes
        -- Check for long-running active statements
        -- Log results to monitoring table
        app_schema.perf_monitor.run_daily_checks;
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=6; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Daily performance audit — prevents ORA-06512 timeouts'
  );
END;
/

💡Performance Monitoring Checklist

Daily: check top SQL by buffer_gets_per_exec — alert on any statement with >50,000 gets per execution
Daily: check for active statements running longer than 5 minutes — investigate immediately
Weekly: audit all foreign key columns on tables with >100K rows for missing indexes
Weekly: check for stale statistics on tables with >100K rows — gather stats if older than 7 days
Monthly: compare AWR snapshots across time periods — detect gradual performance degradation trends
On plan change: compare ms_per_exec before and after — if worse, pin the old plan with SPM

📊 Production Insight

Performance degradation is gradual — a query that takes 10ms today may take 10 seconds after six months of data growth.

Continuous monitoring catches the degradation trend before it crosses the timeout threshold and causes ORA-06512.

Rule: monitor buffer_gets_per_exec and ms_per_exec weekly — a 10x increase means an index was dropped, statistics went stale, or a plan regressed.

🎯 Key Takeaway

AWR, v$sql, and v$session provide real-time and historical performance data for proactive monitoring.

SQL Plan Management prevents plan regression after statistics updates and Oracle upgrades.

Bottom line: if you are not monitoring SQL performance continuously, you will discover the degradation only when ORA-06512 kills the session during a production batch — by which time the degradation has been building for weeks.

🗂 Cursor Loop vs. Bulk Operations Performance

Elapsed time comparison for common row counts with typical per-iteration overhead

Row Count	Cursor Loop (row-by-row DML)	FORALL (bulk DML)	Improvement Factor	BULK COLLECT Memory (LIMIT 5000)
1,000	~2 seconds	~0.01 seconds	200x	< 1 MB
10,000	~20 seconds	~0.05 seconds	400x	< 1 MB
100,000	~200 seconds (3.3 min)	~0.5 seconds	400x	< 1 MB per batch
1,000,000	~2,000 seconds (33 min)	~5 seconds	400x	< 1 MB per batch
10,000,000	~20,000 seconds (5.5 hrs)	~50 seconds	400x	< 1 MB per batch (with LIMIT)

🎯 Key Takeaways

Cursor loops are the number one cause of ORA-06512 timeouts — total time = iteration count × per-iteration cost, and small per-iteration costs become massive at scale
FORALL replaces DML loops and BULK COLLECT with LIMIT replaces query loops — both are 100x to 400x faster than cursor loops by eliminating the PL/SQL-to-SQL context switch
Missing foreign key indexes are the single most common root cause of full table scans inside loops — Oracle does not auto-index FKs
Stale statistics cause the optimizer to choose wrong execution plans — gather statistics after every large data load
EXPLAIN PLAN reveals the access path — always check for TABLE ACCESS FULL on large tables before tuning PL/SQL logic
Service accounts must have IDLE_TIME UNLIMITED — I/O waits during full table scans appear as idle time and trigger session kills
Continuous monitoring of buffer_gets_per_exec, elapsed time trends, and statistics freshness catches degradation before it causes timeouts

⚠ Common Mistakes to Avoid

✕Not indexing foreign key columns

Symptom

Joins between parent and child tables trigger full table scans on the child table. A 100-million-row fact table scan takes 30 seconds per join iteration. Inside a cursor loop that iterates 50,000 times, the total time is hundreds of hours — the session is killed long before completion.

Fix

Create an index on every foreign key column: CREATE INDEX idx_name ON child_table(fk_column). Verify with EXPLAIN PLAN that the join uses NESTED LOOPS with INDEX RANGE SCAN instead of HASH JOIN with TABLE ACCESS FULL. Run the FK index audit query weekly to catch newly created tables with missing FK indexes.

✕Using cursor loops for 10,000+ row operations with per-iteration DML

Symptom

The procedure takes hours to complete and eventually times out with ORA-06512. AWR reports show the procedure's SQL statements consuming the majority of database CPU and I/O. The per-statement execution time is low (milliseconds), but the total is massive due to iteration count.

Fix

Replace the cursor loop with FORALL for DML operations and BULK COLLECT with LIMIT for data fetching. A 100,000-row operation drops from 200 seconds to 0.5 seconds. If the logic is simple enough, consider replacing the entire procedure with a single MERGE statement — eliminating PL/SQL entirely.

✕Not gathering statistics after large data loads or bulk operations

Symptom

The optimizer chooses a suboptimal execution plan based on stale statistics — for example, choosing a full table scan because it estimates the table has 10,000 rows when it actually has 10 million. The query that was fast yesterday is slow today because the data volume changed but the statistics did not.

Fix

Gather statistics immediately after every large data load: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE). Add GATHER_TABLE_STATS to the post-load step of every ETL procedure. Verify freshness: SELECT table_name, num_rows, last_analyzed FROM dba_tables.

✕Using BULK COLLECT without LIMIT on large result sets

Symptom

The procedure consumes all available PGA memory. Other sessions are starved. The database may become unresponsive. On Linux, the OOM killer may terminate the Oracle background process. The error may be ORA-04030 (out of process memory) rather than ORA-06512.

Fix

Always use LIMIT with BULK COLLECT: FETCH cursor BULK COLLECT INTO collection LIMIT 5000. The collection is reused for each batch, so total memory usage stays constant regardless of total result set size. Start with LIMIT 1,000 to 5,000 and tune upward only after verifying PGA capacity.

✕Not checking EXPLAIN PLAN before deploying queries to production

Symptom

The query works perfectly in development with 1,000 rows but times out in production with 100 million rows. The execution plan shows TABLE ACCESS FULL on the production table — which completed in milliseconds on the development table but takes 30 seconds on the production table.

Fix

Always run EXPLAIN PLAN on production-representative data volumes before deployment. Look for TABLE ACCESS FULL on any table expected to have more than 100,000 rows in production. If found, add an index on the filter or join columns or rewrite the query to use an existing index.

✕Using the DEFAULT profile with restrictive IDLE_TIME for service accounts that run batch procedures

Symptom

Long-running ETL procedures are killed after 10 or 30 minutes with ORA-00028 (session killed) and ORA-06512. The procedures are not actually idle — they are waiting on I/O during full table scans or waiting on locks during concurrent DML — but Oracle's IDLE_TIME counter treats I/O waits as idle time.

Fix

Create a dedicated profile for batch service accounts with IDLE_TIME UNLIMITED: CREATE PROFILE etl_profile LIMIT IDLE_TIME UNLIMITED CONNECT_TIME 720. Assign it: ALTER USER etl_user PROFILE etl_profile. Then optimize the procedure to run faster — the right fix is a faster procedure, not a longer timeout.

Interview Questions on This Topic

QWhat is the relationship between cursor loops and ORA-06512 timeout errors?JuniorReveal
Cursor loops multiply the per-iteration cost by the iteration count. A 50ms query inside a 100,000-iteration loop takes 83 minutes. If the per-iteration cost increases due to data growth or a missing index, the total can exceed the resource limit or application timeout, causing the session to be killed with ORA-00028 or ORA-01013, which propagates through the PL/SQL stack as ORA-06512. The fix is to replace cursor loops with FORALL for DML and BULK COLLECT with LIMIT for queries — these eliminate the PL/SQL-to-SQL context switch overhead and reduce execution time by 100x to 400x.
QHow do you identify a missing index as the cause of an ORA-06512 timeout?Mid-levelReveal
Run EXPLAIN PLAN on the slow SQL statement inside the procedure. Look for TABLE ACCESS FULL on any table with more than 100,000 rows — this indicates a missing index on the query's filter or join columns. Check specifically whether foreign key columns are indexed, because Oracle does not auto-index FKs. Also check whether statistics are stale — stale statistics can cause the optimizer to choose a full scan even when an index exists. Add the missing index and verify with EXPLAIN PLAN that the plan changes from TABLE ACCESS FULL to INDEX RANGE SCAN or NESTED LOOPS.
QWhat is the difference between FORALL and BULK COLLECT, and when do you use each?Mid-levelReveal
FORALL is for bulk DML (INSERT, UPDATE, DELETE) — it sends all DML statements to the SQL engine in a single round-trip, eliminating the per-iteration context switch. BULK COLLECT is for bulk query fetching — it retrieves all rows (or a batch via LIMIT) in a single round-trip instead of one row per FETCH. They are complementary: use BULK COLLECT with LIMIT to fetch rows in batches, then use FORALL to process the DML for each batch. SAVE EXCEPTIONS on FORALL allows continued processing when individual rows fail. When the logic is simple enough, a single SQL MERGE statement can replace both BULK COLLECT and FORALL entirely.
QHow would you prevent performance-driven ORA-06512 errors in a production ETL system?SeniorReveal
Five-layer prevention. First, audit all foreign key columns on tables with more than 100,000 rows for missing indexes — every FK used in joins must be indexed. Second, replace all cursor loops with 10K+ iterations with FORALL and BULK COLLECT with LIMIT, or with set-based MERGE statements. Third, gather statistics on all ETL tables immediately after every large data load using DBMS_STATS.GATHER_TABLE_STATS with CASCADE=>TRUE. Fourth, create a dedicated resource profile for ETL service accounts with IDLE_TIME UNLIMITED. Fifth, monitor SQL performance daily: alert on buffer_gets_per_exec > 50,000, active statements > 5 minutes, stale statistics > 7 days, and plan hash value changes with increased elapsed time. Use SQL Plan Management to prevent plan regression after statistics updates.
QWhat is the impact of stale statistics on query performance, and how does it relate to ORA-06512?SeniorReveal
Stale statistics cause the optimizer to make wrong cardinality estimates. If statistics say a table has 10,000 rows but it actually has 10 million, the optimizer may choose NESTED LOOPS (optimal for small sets) instead of HASH JOIN (optimal for large sets), or it may choose a full table scan because it estimates the index access would return too many rows. The query that ran in milliseconds yesterday suddenly takes minutes or hours. Inside a cursor loop, the degradation is multiplied by the iteration count, pushing total elapsed time past the resource limit or application timeout and triggering ORA-06512. The fix is to gather fresh statistics: DBMS_STATS.GATHER_TABLE_STATS with AUTO_SAMPLE_SIZE. SQL Plan Management can prevent plan regression by pinning a known-good plan as a baseline.

Frequently Asked Questions

How do I know if my ORA-06512 is caused by a performance problem rather than a logic error?

Check the execution time and the accompanying Oracle error. If the procedure runs for minutes or hours before raising ORA-06512, and the stack includes ORA-00028 (session killed), ORA-01013 (user cancelled), or ORA-02396 (exceeded idle time), it is a performance timeout — not a logic error. Enable SQL tracing with DBMS_MONITOR.SESSION_TRACE_ENABLE, re-run the procedure, and analyze the trace file with tkprof. The SQL statement with the highest elapsed time is the bottleneck. If the procedure fails in seconds with a different ORA error (ORA-01403, ORA-00001, etc.) at the root of the stack, it is a logic or data error, not a performance problem.

What is the optimal batch size for BULK COLLECT LIMIT?

Start with 1,000 to 5,000 rows per batch. The optimal size depends on row width (bytes per row) and available PGA memory. A batch of 5,000 rows at 100 bytes per row consumes approximately 500 KB — negligible. A batch of 5,000 rows at 10 KB per row consumes approximately 50 MB — still acceptable for most environments. Avoid batches larger than 10,000 to 50,000 rows unless you have verified PGA memory capacity. Monitor PGA usage with: SELECT name, value FROM v$pgastat WHERE name LIKE '%aggregate%'. The diminishing returns threshold is typically around 5,000 to 10,000 — increasing beyond that provides minimal additional performance improvement.

Should I use PARALLEL hints to speed up slow queries instead of adding indexes?

No — PARALLEL should be a last resort, not a first response. Parallel execution distributes work across multiple CPU cores, which can speed up individual full table scans. But it consumes proportionally more CPU, I/O, and memory resources, reducing capacity for other sessions. Adding an index converts a 30-second full scan into a 2ms index lookup — a 15,000x improvement with no additional resource consumption. Use the index first. If the query is still slow after indexing (e.g., it genuinely needs to process millions of rows), then consider PARALLEL as an additional optimization.

How do I prevent plan regression after gathering statistics?

Use SQL Plan Management (SPM). Before gathering statistics, load the current execution plans for critical SQL statements into SPM baselines: DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'abc123'). After statistics are gathered, if the optimizer generates a new plan, SPM compares it against the baseline. If the new plan performs worse, SPM rejects it and uses the baseline plan. If the new plan performs better, SPM can optionally accept it (controlled by the EVOLVE task). This provides plan stability while still allowing the optimizer to improve when it genuinely finds a better path.

How do I find which foreign key columns need indexes?

Run the FK index audit query from the indexing section: join dba_constraints (where constraint_type = 'R') with dba_cons_columns and left join to dba_ind_columns. Any foreign key column that does not appear as the leading column of an index is a missing index. Prioritize by table size: missing FK indexes on tables with millions of rows cause the most severe performance problems. Schedule this query to run weekly and alert on any new missing FK indexes — tables created after the initial audit will not have FK indexes unless someone explicitly creates them.

🔥

Naren Founder & Author

Developer and founder of TheCodeForge. I built this site because I was tired of tutorials that explain what to type without explaining why it works. Every article here is written to make concepts actually click.

About Naren Get in touch

Forged with 🔥 at TheCodeForge.io — Where Developers Are Forged