Advanced 11 min · April 14, 2026

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

ORA-06512 Timeout: Missing FK Index Cost 6 Hours

Q: 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.

Q: 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.

Q: 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.

Q: 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.

Q: 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.

A 30-second query in a 50,000-iteration loop cost 6 hours—ORA-06512.

Naren Founder & Principal Engineer

20+ years shipping high-throughput database systems. Lessons pulled from things that broke in production.

✓ Production

production tested

July 04, 2026

last updated

2,165

articles · all by Naren

Before you start⏱ 30 min

✓Deep production experience
✓Understanding of internals and trade-offs
✓Experience debugging complex systems

● Production Incident 🔎 Debug Guide ⚙ Triage Commands

⚡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

✦ Definition~90s read

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

ORA-06512 is Oracle's stack trace error, not a timeout itself—it surfaces when a PL/SQL block hits a resource limit like MAX_DURATION or a deadlock, often masking the real culprit: a missing foreign key index. In production, a single unindexed FK can cascade row-by-row full table scans (FTS) across child tables during parent updates or deletes, locking rows and consuming undo.

★

ORA-06512 timeout errors happen when a PL/SQL procedure takes too long to execute.

A 6-hour timeout isn't a configuration issue; it's a query plan disaster where Oracle must validate referential integrity by scanning millions of rows per DML operation. The fix isn't tweaking timeouts—it's adding the missing index, which drops FTS to a unique scan and cuts runtime from hours to milliseconds.

This pattern is so common that experienced DBAs check FK indexing first when they see ORA-06512 with long-running PL/SQL, especially in OLTP systems where row-by-row processing (slow-by-slow) amplifies the damage. Alternatives like disabling FK constraints (risky) or increasing resource limits (band-aid) only delay the inevitable; proper indexing and bulk operations with FORALL/BULK COLLECT are the production-proven solutions used at companies like Oracle and AWS RDS to prevent these timeouts at scale.

Plain-English First

ORA-06512 timeout errors happen when a PL/SQL procedure takes too long to execute. The database kills the session, the application times out, or a resource limit is exceeded. The error stack shows ORA-06512 pointing to a procedure and line number, but the root cause is not a bug in the logic — it is a performance problem. A query that takes 50 milliseconds is perfectly fine in isolation, but when that same query sits inside a loop that runs 100,000 times, it becomes an 83-minute operation. Double the per-iteration cost to 100ms due to data growth, and it becomes a nearly 3-hour operation. The fix is almost never rewriting the procedure — it is either eliminating the loop through bulk operations, adding an index to make the query fast, or both.

⚙ Browser compatibility

Latest versions — ✓ supported

Chrome	Firefox	Safari	Edge
✓	✓	✓	✓

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.

Why ORA-06512 Timeout Is Almost Always a Missing FK Index

ORA-06512 is a stack trace marker in PL/SQL, not an error itself. When paired with a timeout, it signals that a DML operation on a child table triggered a full table lock escalation because the foreign key column lacks an index. Oracle must lock the entire parent table to prevent referential integrity violations during the child table update, and under concurrent load that lock contention escalates into a timeout. The cost is not theoretical: a single unindexed FK on a table with 10 million rows can turn a 50ms update into a 6-hour deadlock cascade.

In practice, the mechanism is straightforward. When you insert, update, or delete a row in the parent table, Oracle must ensure no orphaned child rows exist. Without an index on the child FK column, Oracle performs a full table scan of the child table for every parent row touched. Under serial execution that scan is slow; under concurrent transactions it triggers row-level lock escalation to table-level locks, and eventually the database throws ORA-06512 with a timeout. The missing index is the root cause in over 90% of these cases.

You should suspect this pattern whenever a PL/SQL block that updates a parent table consistently times out under load but runs fine in isolation. The fix is a single B-tree index on the child FK column. Adding it eliminates the full table scan, drops lock contention to near zero, and turns a 6-hour failure into a sub-second operation. In production systems, this is the first thing to check before tuning SQL or increasing timeouts.

⚠ Indexing FK Columns Is Not Optional

Oracle does not automatically index foreign keys. A missing FK index is the #1 cause of ORA-06512 timeouts in OLTP systems — not bad SQL.

📊 Production Insight

A financial reconciliation job that updates a 5M-row parent table with 20 child tables, all missing FK indexes, caused a 6-hour nightly timeout. The exact symptom was ORA-06512 followed by ORA-00060 deadlock under 50 concurrent sessions. Rule of thumb: every foreign key column must have an index unless the child table is never updated or deleted — and even then, index it.

🎯 Key Takeaway

ORA-06512 timeout is a symptom, not the error — the root cause is almost always a missing FK index.

A single B-tree index on the child FK column eliminates full table scans and lock escalation.

Always index foreign keys on child tables that participate in DML on the parent — no exceptions.

thecodeforge.io

Oracle Plsql Performance Tuning Ora 06512

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.sqlSQL

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

-- 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.sqlSQL

-- 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.

thecodeforge.io

Oracle Plsql Performance Tuning Ora 06512

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.sqlSQL

-- 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.sqlSQL

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

-- 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.sqlSQL

-- 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.sqlSQL

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

-- 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.

● Production incidentPOST-MORTEMseverity: high

Missing Index on Foreign Key Turned a 5-Minute ETL Into a 6-Hour Timeout

Symptom

At 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.

Assumption

The 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 cause

The 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.

Fix

Immediate 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,000x
A 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 loops
ORA-06512 timeouts are performance problems, not logic errors — tune the query access path and eliminate the loop before touching the procedure logic
Data 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 identification6 entries

Symptom · 01

ORA-06512 after hours of execution — procedure never completes

→

Fix

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.

Symptom · 02

ORA-06512 at a specific line number inside a cursor loop

→

Fix

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.

Symptom · 03

Procedure runs for hours, then the session is killed — ORA-06512 with ORA-00028

→

Fix

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.

Symptom · 04

Cursor loop processes one row per iteration — total iteration count exceeds 10,000

→

Fix

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.

Symptom · 05

EXPLAIN PLAN shows HASH JOIN on large tables — no index is used for the join

→

Fix

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.

Symptom · 06

Procedure was fast last week but is slow now — no code changes deployed

→

Fix

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.

★ Performance-Driven ORA-06512 Quick DebugReal commands to identify and fix performance bottlenecks causing timeout errors

Procedure hangs for hours then raises ORA-06512−

Immediate action

Kill 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 now

Run 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+

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

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

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

Cursor Loop vs. Bulk Operations Performance

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)

⚙ Quick Reference

6 commands from this guide

File	Command / Code	Purpose
iothecodeforgeperformanceloop_elimination.sql	CREATE OR REPLACE PROCEDURE app_schema.process_orders_slow IS	The Loop Problem
iothecodeforgeperformanceindexing_strategies.sql	CREATE INDEX data_owner.idx_fact_customer_fk	Indexing Strategies
iothecodeforgeperformancequery_optimization.sql	EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(	Query Optimization
iothecodeforgeperformancebulk_operations.sql	CREATE OR REPLACE PROCEDURE app_schema.process_large_dataset IS	Bulk Operations
iothecodeforgeperformanceresource_limits.sql	SELECT name, value	Resource Limits and Timeout Configuration
iothecodeforgemonitorperformance_monitoring.sql	SELECT	Monitoring and Continuous Performance Assurance

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

6 patterns

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 PREP · PRACTICE MODE

Interview Questions on This Topic

Q01JUNIOR

What is the relationship between cursor loops and ORA-06512 timeout erro...

Q02SENIOR

How do you identify a missing index as the cause of an ORA-06512 timeout...

Q03SENIOR

What is the difference between FORALL and BULK COLLECT, and when do you ...

Q04SENIOR

How would you prevent performance-driven ORA-06512 errors in a productio...

Q05SENIOR

What is the impact of stale statistics on query performance, and how doe...

Q01 of 05JUNIOR

What is the relationship between cursor loops and ORA-06512 timeout errors?

ANSWER

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.

FAQ · 5 QUESTIONS

Frequently Asked Questions

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

What is the optimal batch size for BULK COLLECT LIMIT?

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

How do I prevent plan regression after gathering statistics?

How do I find which foreign key columns need indexes?

Naren Founder & Principal Engineer

20+ years shipping high-throughput database systems. Lessons pulled from things that broke in production.

✓ Verified

production tested

July 04, 2026

last updated

2,165

articles · all by Naren

🔥

That's PL/SQL. Mark it forged?

11 min read · try the examples if you haven't