Why RMS Performance is Different

Tuning Oracle RMS is not the same as tuning a generic OLTP database. RMS has specific characteristics that drive performance challenges:

1. Massive Table Sizes: ITEM_LOC_SOH can have 500M+ rows (items × locations). TRAN_DATA can have billions of rows.
2. Complex Batch Workloads: Nightly batch windows process millions of rows through Pro*C programs with tight time constraints.
3. Mixed Workload: During the day, interactive users query the same tables that batch jobs modify at night.
4. Hierarchical Queries: Many business operations require traversing the merchandise or organizational hierarchy (recursive queries).
5. Cross-Table Joins: A single business operation (e.g., creating a PO) may join 10+ tables including ITEM_MASTER, ITEM_LOC, ITEM_SUPPLIER, ORDHEAD, ORDSKU, SUPS.

DBMS_STATS: The Foundation

The Oracle optimizer cannot generate good execution plans without accurate table and index statistics. This is the single most important performance factor in RMS.

Oracle's Gathering Strategy for RMS

-- Gather stats on the most critical RMS tables
-- Run during the batch window (not during business hours)

-- Item Master (moderate size, changes daily)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'RMS13',
    tabname    => 'ITEM_MASTER',
    estimate_percent => 10,  -- 10% sample for large tables
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade    => TRUE,      -- Also gather index stats
    degree     => 4          -- Parallel degree
);

-- Item-Location SOH (very large, changes constantly)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'RMS13',
    tabname    => 'ITEM_LOC_SOH',
    estimate_percent => 5,   -- Lower sample for huge tables
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
    cascade    => TRUE,
    degree     => 8          -- Higher parallelism
);

-- Transaction Data (partitioned, massive)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'RMS13',
    tabname    => 'TRAN_DATA',
    estimate_percent => 1,   -- 1% for billion-row tables
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
    cascade    => TRUE,
    degree     => 8,
    granularity => 'ALL'     -- Gather partition-level stats too
);

When to Gather

Index Analysis for RMS Tables

RMS ships with hundreds of indexes. Understanding which indexes are used for which operations is critical:

Key RMS Indexes

Identifying Missing Indexes

-- Find SQL statements doing full table scans
-- on tables larger than 1M rows
SELECT 
    s.sql_id,
    s.sql_text,
    p.object_name    AS table_name,
    p.operation,
    s.executions,
    ROUND(s.elapsed_time / NULLIF(s.executions, 0) / 1000, 0) 
                     AS avg_elapsed_ms,
    s.buffer_gets    AS total_buffer_gets
FROM 
    v$sql_plan p
JOIN 
    v$sql s ON p.sql_id = s.sql_id
WHERE 
    p.operation = 'TABLE ACCESS'
    AND p.options = 'FULL'
    AND p.object_name IN (
        'ITEM_MASTER', 'ITEM_LOC_SOH', 'ORDHEAD', 
        'ORDSKU', 'TRAN_DATA', 'ITEM_LOC'
    )
    AND s.executions > 10
ORDER BY 
    s.elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Reading Execution Plans

The execution plan is the roadmap the optimizer uses to execute your query. Reading it correctly is essential:

EXPLAIN PLAN FOR
SELECT im.item, im.item_desc, ils.stock_on_hand
FROM   item_master im
JOIN   item_loc_soh ils ON im.item = ils.item
WHERE  im.dept = 1000
AND    ils.loc = 1042
AND    ils.stock_on_hand > 0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'ALL'));

What to Look For

Partition Pruning for VLDB Tables

For very large tables like TRAN_DATA and STOCK_LEDGER, table partitioning is essential. RMS typically partitions these tables by date (monthly or weekly).

Partition Pruning

When a query includes a date filter on a partitioned table, Oracle can prune (skip) partitions that don't contain relevant data:

-- WITHOUT partitioning: scans entire 5-year TRAN_DATA table
-- WITH partitioning: scans only the July 2026 partition
SELECT item, loc, tran_date, quantity, cost
FROM   tran_data
WHERE  tran_date BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
AND    item = '100400012345';

If TRAN_DATA has 60 monthly partitions, this query scans only 1 partition instead of all 60 — a 60x reduction in I/O.

Bind Variables & Cursor Sharing

One of the most common performance mistakes in custom RMS code is using literal values instead of bind variables:

-- BAD: Literal values (generates a new execution plan for EACH item)
-- 100,000 items = 100,000 different SQL statements in the shared pool
SELECT * FROM item_master WHERE item = '100400012345';
SELECT * FROM item_master WHERE item = '100400012346';
SELECT * FROM item_master WHERE item = '100400012347';

-- GOOD: Bind variable (one plan, reused 100,000 times)
SELECT * FROM item_master WHERE item = :p_item;

Literal SQL causes:

• Hard parsing for every unique statement (CPU-intensive)
• Shared pool fragmentation (memory waste)
• Latch contention (performance degradation under concurrency)

In Pro*C batch programs, always use host variables. In PL/SQL, always use bind variables in dynamic SQL (EXECUTE IMMEDIATE ... USING).

Batch Performance Optimization

Nightly batch is where RMS performance problems cause the most business pain. If the batch window overruns, stores cannot open in the morning.

Key Optimization Techniques

1. Parallel DML: Enable parallel processing for large-scale batch operations
2. BULK COLLECT / FORALL: Replace row-by-row processing with bulk operations (10-50x faster)
3. Direct Path Insert: Use INSERT /*+ APPEND */ for loading staging tables
4. NOLOGGING: Reduce redo log generation for batch loads (with proper backup precautions)
5. Commit Frequency: Commit every 10,000-50,000 rows to balance redo log pressure vs. rollback segment size

DECLARE
    TYPE t_items IS TABLE OF item_master.item%TYPE;
    l_items t_items;
    
    CURSOR c_items IS
        SELECT item FROM item_master 
        WHERE  status = 'A' AND dept = 1000;
BEGIN
    OPEN c_items;
    LOOP
        FETCH c_items BULK COLLECT INTO l_items LIMIT 10000;
        EXIT WHEN l_items.COUNT = 0;
        
        FORALL i IN 1..l_items.COUNT
            UPDATE item_loc_soh
            SET    last_update_datetime = SYSDATE
            WHERE  item = l_items(i)
            AND    loc_type = 'S';
        
        COMMIT;  -- Commit every 10,000 rows
    END LOOP;
    CLOSE c_items;
END;
/

AWR Report Analysis

The AWR (Automatic Workload Repository) report is the primary diagnostic tool for RMS database performance. Key sections to review:

Top 5 Timed Events

Shows where the database is spending the most time. Common RMS findings:

• db file sequential read — single-block I/O (index lookups). High values suggest I/O bottleneck or excessive logical reads.
• db file scattered read — multi-block I/O (full table scans). Indicates missing indexes or stale statistics.
• log file sync — waiting for redo log writes. Too-frequent commits or slow I/O subsystem.
• enq: TX - row lock contention — multiple sessions trying to update the same rows. Common in ITEM_LOC_SOH during batch and online overlap.

SQL Ordered by Elapsed Time

The most important section for identifying slow queries. Focus on queries with:

• Total elapsed time > 60 seconds
• Executions > 1,000
• Buffer gets per execution > 100,000

Common RMS Bottlenecks & Fixes