Locking, Concurrency & Contention

-- All enqueue waits right now — decode the lock type from P1
SELECT s.sid, s.username, s.event,
       s.seconds_in_wait,
       CHR(BITAND(s.p1,-16777216)/16777215) ||
       CHR(BITAND(s.p1, 16711680)/65535)    AS lock_type,
       s.blocking_session,
       b.username                               AS blocking_user,
       b.sql_id                                 AS blocking_sql,
       b.status                                 AS blocker_status,
       b.last_call_et                           AS blocker_idle_secs
FROM   v$session s
LEFT JOIN v$session b ON b.sid = s.blocking_session
WHERE  s.event LIKE 'enq:%'
ORDER BY s.seconds_in_wait DESC;

-- Full blocking chain tree (A blocks B which blocks C which blocks D)
SELECT  LPAD(' ', 2*(LEVEL-1)) || s.sid  AS sid_tree,
        s.username, s.status, s.event,
        s.seconds_in_wait, s.blocking_session,
        s.sql_id, s.last_call_et AS idle_secs,
        s.machine
FROM    v$session s
START WITH s.blocking_session IS NULL
       AND s.sid IN (
           SELECT DISTINCT blocking_session
           FROM   v$session
           WHERE  blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY s.seconds_in_wait DESC;

-- Quick summary: how many sessions does each root blocker hold up?
SELECT b.sid, b.serial#, b.username,
       b.status, b.last_call_et     AS idle_secs,
       b.machine, b.program, b.sql_id,
       COUNT(w.sid)                 AS sessions_blocked,
       MAX(w.seconds_in_wait)       AS max_victim_wait
FROM   v$session w
JOIN   v$session b ON b.sid = w.blocking_session
WHERE  w.blocking_session IS NOT NULL
GROUP BY b.sid, b.serial#, b.username,
         b.status, b.last_call_et, b.machine, b.program, b.sql_id
ORDER BY sessions_blocked DESC;

-- Check rollback progress for a killed/long-running session
SELECT t.addr, t.start_time,
       t.used_ublk                       AS undo_blocks_remaining,
       t.used_urec                       AS undo_records_remaining,
       ROUND(t.used_ublk * 8 / 1024, 1)  AS undo_mb_to_rollback,
       rs.name                           AS rollback_segment,
       s.username, s.machine
FROM   v$transaction t
JOIN   v$session     s  ON s.taddr = t.addr
JOIN   v$rollstat    r  ON r.usn   = t.xidusn
JOIN   v$rollname    rs ON rs.usn  = t.xidusn
ORDER BY t.used_ublk DESC;
-- Large undo_mb_to_rollback = long wait ahead — cannot be cancelled

-- System-wide latch miss rates (miss_pct > 0.5% needs attention)
SELECT name, gets, misses, sleeps,
       ROUND(misses * 100 / NULLIF(gets, 0), 3)    AS miss_pct,
       ROUND(sleeps * 100 / NULLIF(misses, 0), 1)  AS sleep_per_miss
FROM   v$latch
WHERE  misses > 0 AND gets > 1000
ORDER BY misses DESC
FETCH FIRST 15 ROWS ONLY;

-- Child latch breakdown for cache buffers chains (hot block diagnosis)
SELECT name, child#, gets, misses, sleeps,
       ROUND(misses*100/NULLIF(gets,0),3) AS miss_pct
FROM   v$latch_children
WHERE  name = 'cache buffers chains'
AND    misses > 0
ORDER BY misses DESC
FETCH FIRST 10 ROWS ONLY;

-- Sessions waiting on library cache events + what object is held
SELECT s.sid, s.username, s.event,
       s.seconds_in_wait, s.sql_id,
       s.p1raw    AS handle_address,
       s.p2       AS lock_mode
FROM   v$session s
WHERE  s.event IN ('library cache lock', 'library cache pin')
ORDER BY s.seconds_in_wait DESC;

-- Identify WHAT object is being held (plug p1raw value in)
SELECT kglnaown AS owner,
       kglnaobj AS object_name,
       kglobtyp AS object_type
FROM   x$kglob
WHERE  kglhdadr = '&handle_address';

-- Who is actively using that object right now?
SELECT s.sid, s.username, s.status,
       s.sql_id, s.last_call_et, s.machine
FROM   v$session  s
JOIN   v$access   a ON a.sid = s.sid
WHERE  a.object = 'YOUR_PACKAGE_NAME'
AND    a.type   IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TABLE');

-- Deadlocks are recorded in the alert log AND trace files
-- Find trace file location:
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';
-- Look in: <diag_dest>/diag/rdbms/<dbname>/<instance>/trace/
-- File: <instance>_ora_<pid>.trc containing word "deadlock"

-- Deadlock frequency — spikes in user_rollbacks correlate with ORA-00060
SELECT name, value FROM v$sysstat
WHERE  name = 'user rollbacks';

-- THE FIX: enforce consistent row-access order in the application
-- Pattern that causes deadlock:
--   Session A: UPDATE orders SET status='X' WHERE id=100  (locks row 100)
--   Session B: UPDATE orders SET status='Y' WHERE id=200  (locks row 200)
--   Session A: UPDATE orders SET status='Z' WHERE id=200  (waits for B)
--   Session B: UPDATE orders SET status='W' WHERE id=100  (waits for A → DEADLOCK)

-- Fix: always lock rows in ascending primary key order
-- Both sessions update rows in order 100 then 200 → one waits, no deadlock

-- For queue processing: use SKIP LOCKED (see section 7.9)
SELECT order_id, payload FROM job_queue
WHERE  status = 'PENDING' AND ROWNUM <= 100
FOR UPDATE SKIP LOCKED;

-- THE essential query — run this on every production database immediately
SELECT c.owner, c.table_name,
       c.constraint_name       AS fk_name,
       cc.column_name,
       p.table_name            AS parent_table,
       p.constraint_name       AS pk_name
FROM   dba_constraints   c
JOIN   dba_cons_columns  cc ON cc.constraint_name = c.constraint_name
                          AND cc.owner           = c.owner
JOIN   dba_constraints   p  ON p.constraint_name  = c.r_constraint_name
                          AND p.owner            = c.r_owner
WHERE  c.constraint_type = 'R'   -- R = foreign key
AND    c.owner NOT IN ('SYS','SYSTEM','DBSNMP','MDSYS')
-- Now exclude FKs that already have an index on the FK column:
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 = cc.position
)
ORDER BY c.owner, c.table_name;

-- Step 1: Is enq: SQ - contention appearing in top waits?
SELECT event, total_waits, time_waited, average_wait
FROM   v$system_event
WHERE  event = 'enq: SQ - contention';

-- Step 2: Find sequences with dangerously small cache sizes
SELECT sequence_owner, sequence_name,
       cache_size, order_flag, cycle_flag
FROM   dba_sequences
WHERE  sequence_owner NOT IN ('SYS','SYSTEM')
AND    cache_size < 100
ORDER BY cache_size ASC;

-- Step 3: Apply fix — dramatically increase cache size
-- Rule: cache = peak_inserts_per_second × 10 (10 seconds of headroom)
-- Example: 500 inserts/sec peak → CACHE 5000
ALTER SEQUENCE apps.orders_seq     CACHE 1000;
ALTER SEQUENCE apps.customers_seq  CACHE 1000;
ALTER SEQUENCE apps.events_seq     CACHE 5000;   -- high volume
-- Takes effect immediately — no restart, no data loss

-- WITHOUT skip locked: all workers fight over the same rows → contention
SELECT job_id, payload FROM job_queue
WHERE  status = 'PENDING' AND ROWNUM <= 10
FOR UPDATE;   -- all 10 workers try to lock the same 10 rows

-- WITH skip locked: each worker gets unique rows → zero contention
SELECT job_id, payload FROM job_queue
WHERE  status = 'PENDING' AND ROWNUM <= 10
FOR UPDATE SKIP LOCKED;   -- each worker skips rows locked by others

-- Full PL/SQL worker pattern
DECLARE
  CURSOR c_jobs IS
    SELECT job_id, payload
    FROM   job_queue
    WHERE  status = 'PENDING'
    AND    ROWNUM <= 50
    FOR UPDATE SKIP LOCKED;
BEGIN
  FOR r IN c_jobs LOOP
    process_job(r.job_id, r.payload);
    UPDATE job_queue
    SET    status = 'DONE',
           processed_at = SYSTIMESTAMP
    WHERE  job_id = r.job_id;
  END LOOP;
  COMMIT;
END;
/

-- NOWAIT and WAIT variants for explicit lock control
SELECT order_id FROM orders WHERE order_id = 101
FOR UPDATE NOWAIT;    -- fail immediately if locked (raises ORA-00054)

SELECT order_id FROM orders WHERE order_id = 101
FOR UPDATE WAIT 5;    -- wait up to 5 seconds then fail

-- ============================================================
-- QUERY 1: How many sessions are blocked and what event?
-- ============================================================
SELECT wait_class, event,
       COUNT(*)                   AS blocked_sessions,
       MAX(seconds_in_wait)        AS max_wait_secs,
       ROUND(AVG(seconds_in_wait), 1) AS avg_wait_secs
FROM   v$session
WHERE  blocking_session IS NOT NULL
GROUP BY wait_class, event
ORDER BY blocked_sessions DESC;

-- ============================================================
-- QUERY 2: Who is the root blocker? (most victims first)
-- ============================================================
SELECT b.sid, b.serial#, b.username,
       b.status, b.last_call_et    AS idle_secs,
       b.machine, b.program, b.sql_id,
       COUNT(w.sid)                AS sessions_blocked,
       MAX(w.seconds_in_wait)      AS max_victim_wait
FROM   v$session w
JOIN   v$session b ON b.sid = w.blocking_session
WHERE  w.blocking_session IS NOT NULL
GROUP BY b.sid, b.serial#, b.username,
         b.status, b.last_call_et, b.machine, b.program, b.sql_id
ORDER BY sessions_blocked DESC;

-- ============================================================
-- QUERY 3: What was the blocker's last SQL?
-- ============================================================
SELECT s.sid, s.username, s.status, s.sql_id,
       sq.sql_text,
       s.prev_sql_id,
       psq.sql_text AS prev_sql_text
FROM   v$session  s
LEFT JOIN v$sqlarea sq  ON sq.sql_id  = s.sql_id
LEFT JOIN v$sqlarea psq ON psq.sql_id = s.prev_sql_id
WHERE  s.sid = &blocker_sid;

-- ============================================================
-- QUERY 4: What transaction does the blocker have open?
-- ============================================================
SELECT t.start_time, t.status,
       t.used_ublk                    AS undo_blocks,
       ROUND(t.used_ublk*8/1024,1)   AS undo_mb,
       t.log_io, t.phy_io,
       s.username, s.machine
FROM   v$transaction t
JOIN   v$session     s ON s.taddr = t.addr
WHERE  s.sid = &blocker_sid;

-- ============================================================
-- QUERY 5 (if needed): Kill the blocker
-- Use IMMEDIATE to signal the session to die NOW
-- ============================================================
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

-- Verify all blocking cleared
SELECT COUNT(*) AS still_blocked
FROM   v$session
WHERE  blocking_session IS NOT NULL;

-- Post-incident: capture what happened from ASH history
SELECT TO_CHAR(sample_time,'HH24:MI:SS') AS time,
       session_id, event, blocking_session, sql_id
FROM   v$active_session_history
WHERE  sample_time BETWEEN :incident_start AND :incident_end
AND    blocking_session IS NOT NULL
ORDER BY sample_time, session_id;

-- Monitor long-running uncommitted transactions (run as an alert job)
SELECT s.sid, s.username, s.status,
       t.start_time,
       ROUND((SYSDATE -
         TO_DATE(t.start_time,'MM/DD/YY HH24:MI:SS'))*24*60,1)
         AS txn_minutes,
       t.used_ublk, t.used_urec,
       s.machine, s.program
FROM   v$transaction t
JOIN   v$session     s ON s.taddr = t.addr
WHERE (SYSDATE -
       TO_DATE(t.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 > 10
ORDER BY txn_minutes DESC;

-- Create session idle timeout profile
CREATE PROFILE app_user_profile LIMIT
  IDLE_TIME          30    -- kill sessions idle > 30 minutes
  CONNECT_TIME       480   -- max 8 hours connected
  SESSIONS_PER_USER  50;   -- max concurrent sessions per user

ALTER USER app_user PROFILE app_user_profile;