Oracle DBA

When users complain that “the database is slow,” the culprit is not always an inefficient execution plan. Very often, it’s concurrency—sessions waiting on locks—silently throttling throughput. This post is a field-ready playbook based on the diagnostic queries you shared, refactored, explained, and sequenced into an end-to-end workflow. Use it to see blocking, measure impact, identify the SQL and objects involved, and act with confidence—both on single-instance and RAC.

Executive takeaway: Tuning isn’t only about faster SQL; it’s also about removing waits. Lock visibility is a force multiplier for overall performance.

---

1) Who’s blocking whom? (Fast triage)

Purpose

Immediately reveal blocking → blocked relationships so you can prioritize action.

Query (ANSI join + readable columns)

SELECT
  a.sid   AS blocking_sid,
  b.sid   AS blocked_sid
FROM v$lock a
JOIN v$lock b
  ON  a.id1 = b.id1
  AND a.id2 = b.id2
WHERE a.sid <> b.sid
  AND b.request > 0
  AND a.block = 1;

How it helps

• a.block = 1 marks the holder of the blocking lock.
• b.request > 0 marks the waiter.
• This is your now view for escalation and communication.

Tuning action: If multiple blockers appear, start with the one that blocks the largest number of sessions or the longest waits (see Sections 3–4).

---

2) Human-friendly status string (quick comms)

Purpose

Produce a one-liner you can paste into chats/war rooms without interpretation.

Query (tidied)

COL blocking_status FOR A120;

SELECT
  s1.username || '@' || s1.machine || ' (SID=' || s1.sid || ') is blocking ' ||
  s2.username || '@' || s2.machine || ' (SID=' || s2.sid || ')' AS blocking_status
FROM v$lock l1
JOIN v$session s1 ON s1.sid = l1.sid
JOIN v$lock l2   ON l2.id1 = l1.id1 AND l2.id2 = l1.id2
JOIN v$session s2 ON s2.sid = l2.sid
WHERE l1.block = 1
  AND l2.request > 0;

How it helps

• Converts raw SIDs into who and where (user/machine).
• Useful for immediate outreach: “User X on host Y is blocking Z users.”

---

3) How long have they been waiting?

Purpose

Quantify business impact (minutes waiting) to drive prioritization and SLAs.

Query

SELECT
  blocking_session       AS blocking_sid,
  sid                    AS blocked_sid,
  serial#                AS blocked_serial#,
  ROUND(seconds_in_wait/60, 2) AS wait_time_minutes
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session, wait_time_minutes DESC;

How it helps

• Older, longer waits → higher urgency.
• Combine with program/module (see Section 4) for full context.

Tuning action: If waits are climbing for critical modules, consider canceling the blocker’s SQL or coordinating an application-level fix (e.g., shorter transactions).

---

4) What SQL is actually waiting? (blocked session focus)

Purpose

See the exact SQL the blocked session is trying to run.

Query (modernized)

SELECT
  s.sid,
  s.serial#,
  s.process                      AS os_pid,
  s.status,
  q.sql_id,
  q.sql_fulltext
FROM   v$session s
JOIN   v$sql     q ON q.sql_id = s.sql_id
JOIN   v$process p ON p.addr   = s.paddr
WHERE  s.sid = :blocked_sid;   -- ← supply blocked SID

How it helps

• Confirms the waiting statement (not the blocker’s statement).
• Use this to re-run the SQL and test alternatives (indexes/hints) after the incident.

---

5) Which objects are locked? (owner, object, lock mode)

Purpose

Translate lock symptoms into tables/objects so developers know where to fix.

Query (GV$ for RAC; DECODE for readability)

COL session_id       HEAD 'Sid'            FOR 9999
COL object_name      HEAD 'Table|Locked'   FOR A30
COL oracle_username  HEAD 'Oracle|User'    FOR A12 TRUNC
COL os_user_name     HEAD 'OS|User'        FOR A12 TRUNC
COL process          HEAD 'Client|PID'     FOR 99999999
COL owner            HEAD 'Table|Owner'    FOR A12
COL mode_held        FOR A20

SELECT
  lo.session_id,
  lo.oracle_username,
  lo.os_user_name,
  lo.process,
  o.object_name,
  o.owner,
  DECODE(lo.locked_mode,
    0,'None', 1,'Null', 2,'Row Share (SS)',
    3,'Row Excl (SX)', 4,'Share', 5,'Share Row Excl (SSX)',
    6,'Exclusive', TO_CHAR(lo.locked_mode)) AS mode_held
FROM gv$locked_object lo
JOIN dba_objects     o ON o.object_id = lo.object_id
ORDER BY o.object_name;

How it helps

• Lock mode tells you risk/impact: 6 = Exclusive is most disruptive.
• Use object owner/name to map back to module/transaction boundaries.

---

6) Another locked-tables view (with session details)

Purpose

An alternative projection that surfaces session attributes next to objects.

Query

SELECT
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
FROM v$locked_object a
JOIN v$session      b ON b.sid      = a.session_id
JOIN dba_objects    c ON c.object_id = a.object_id;

How it helps

• Useful for targeted outreach to specific machines/users.
• Pairs well with Section 2 for crisp incident narratives.

---

7) Pass a table name → get the sessions and processes

Purpose

You know the table; you want the sessions/processes touching it.

Query 7.1 — SID | SERIAL# | PROCESS for a table

SELECT a.sid || '|' || a.serial# || '|' || a.process AS sid_ser_proc
FROM   v$session a
JOIN   v$locked_object b ON a.sid = b.session_id
JOIN   dba_objects c     ON b.object_id = c.object_id
WHERE  c.object_name = UPPER(:table_name);
-- If no DBA privs: replace dba_objects with user_objects.
-- On RAC, prefer gv$locked_object.

Query 7.2 — Just the OS process IDs for a table

SELECT DISTINCT a.process
FROM   v$session a
JOIN   v$locked_object b ON a.sid = b.session_id
JOIN   dba_objects c     ON b.object_id = c.object_id
WHERE  c.object_name = UPPER(:table_name);

How it helps

• Maps application processes to database sessions for end-to-end tracing.
• Combine with OS tooling to inspect client behavior.

---

8) Simple blocking matrix (users & SIDs)

Purpose

Quickly list blocker ↔ blockee pairs for stakeholder updates.

Query

SELECT
  (SELECT username FROM v$session WHERE sid = a.sid) AS blocker_user,
  a.sid                                              AS blocker_sid,
  ' is blocking '                                    AS txt,
  (SELECT username FROM v$session WHERE sid = b.sid) AS blocked_user,
  b.sid                                              AS blocked_sid
FROM v$lock a
JOIN v$lock b
  ON  a.id1 = b.id1
  AND a.id2 = b.id2
WHERE a.block = 1
  AND b.request > 0;

How it helps

• Intuitive readout for non-DBA stakeholders.

---

9) Blocking and lock type details (RAC-aware)

Purpose

Surface lock types and modes across instances to diagnose cluster-wide contention.

Query

SELECT
  l1.inst_id,
  l1.sid           AS blocker_sid,
  ' IS BLOCKING '  AS txt,
  l2.sid           AS blocked_sid,
  l1.type          AS blocker_lock_type,
  l2.type          AS blocked_lock_type,
  l1.lmode         AS blocker_lmode,
  l2.lmode         AS blocked_lmode,
  l2.inst_id       AS blocked_inst_id
FROM gv$lock l1
JOIN gv$lock l2
  ON  l1.id1 = l2.id1
  AND l1.id2 = l2.id2
WHERE l1.block = 1
  AND l2.request > 0;

How it helps

• type (e.g., TX, TM) differentiates transaction vs. DML enqueue locks.
• Cross-instance view is essential for RAC triage.

---

10) High-signal RAC narrative (who, where, what SQL)

Purpose

Create an actionable narrative: instance, user, machine, SIDs, status, and SQL_ID.

Query

SELECT
  'Instance ' || s1.inst_id || ' ' ||
  s1.username || '@' || s1.machine || ' (SID=' || s1.sid || ',' || s1.serial# || ',' || s1.status || ') is blocking ' ||
  s2.username || '@' || s2.machine || ' (SID=' || s2.sid || ') ' || s2.sql_id AS rac_blocking_status
FROM gv$lock l1
JOIN gv$session s1 ON s1.sid = l1.sid AND s1.inst_id = l1.inst_id
JOIN gv$lock l2   ON l2.id1 = l1.id1 AND l2.id2 = l1.id2
JOIN gv$session s2 ON s2.sid = l2.sid AND s2.inst_id = l2.inst_id
WHERE l1.block = 1
  AND l2.request > 0;

How it helps

• Condenses everything leadership needs in one string for fast decisions.
• The included sql_id helps you jump straight into the executing SQL.

---

Interpreting Lock Types and Modes (cheat-sheet)

• Lock types (common):
  • TX – Transaction lock (row-level; often due to uncommitted DML).
  • TM – DML enqueue on a table (e.g., conflicting DML/DDL).
• Modes (selected):
  • 3 (SX) – Row Exclusive (updating rows).
  • 5 (SSX) – Share Row Exclusive.
  • 6 (X) – Exclusive (strongest; most disruptive).

Insight: Systemic “TX in mode 6” on the same hot table usually signals long transactions or application code paths that hold locks too long—target them first.

---

From Diagnosis to Resolution: What to Do Next

1. Shorten transactions.
   Break large, multi-table changes into smaller commits where safe. Long-running transactions are the #1 cause of cascading waits.
2. Normalize access order.
   Enforce a consistent table locking order across code paths to avoid deadlocks and reduce cross-session blocking.
3. Use appropriate isolation/features.
   Default READ COMMITTED is usually fine; avoid introducing SERIALIZABLE unnecessarily. For reporting, consider materialized views or result caching to decouple from OLTP locks.
4. Index for contended predicates.
   Hotspot updates on unindexed predicates increase scan time and lock hold time. The fix is often a right-sized index.
5. Avoid DDL during peak DML.
   Coordinate DDL windows; many DDLs acquire exclusive object locks (TM) that stall writers/readers.
6. Operational guardrails.
   • Kill only as a last resort: ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;
     Prefer application-level cancellation to preserve consistency.
   • Log module/action (DBMS_APPLICATION_INFO) so you can identify offending code fast.

---

Pro Tips (Production Hardening)

• Prefer ANSI JOINs for readability and maintenance.
• RAC? Default to GV$* views for cluster-wide visibility.
• Privileges: Access to V$/GV$/DBA_* requires appropriate grants/roles.
• Add columns as needed: s.module, s.program, s.sql_id, s.event, s.wait_class, s.logon_time sharpen the picture.
• Historical view: Correlate with AWR/ASH to see when spikes occur and which SQL/objects participate over time.
• Automate alerts: Wrap Query #3 plus #10 into a scheduled job to page when wait minutes cross your threshold.

---

Great tuning is half acceleration, half friction removal. These lock diagnostics remove friction with surgical precision—illuminating blockers, quantifying pain, surfacing the exact SQL and objects in play, and accelerating your path from what’s wrong to what’s fixed.

Ship faster. Wait less. Let throughput sing.