🚨 RCA Chronicles: From 25 Minutes to 25 Seconds

🧪 Step 1: Tried an Alternate Approach (Spoiler: Don’t)

I began with a common instinct — try an alternative logic that might be more efficient. But lesson learned: never try to fix something without understanding the root cause first.
Wasted a few hours there. Moving on.

🧭 Step 2: Shifted Focus to UAT – With All Tools Ready

After half a day of trial and error, we changed gears. This time, we were better prepared.

• Checked logs — nothing broken.

• But oddly, logs were delayed and sluggish.

• Suspecting database slowness, we jumped into Oracle DB.

🔍 Step 3: Found the Real Culprit in Oracle

Sure enough, we spotted some queries running for over 20 minutes — stuck and chewing through something they shouldn’t.

📉 Step 4: Checked the Execution Plan

Pulled the execution plan of the offending query.

Boom 💥 — Full Table Scan.

That’s when the lightbulb went on.

🏗️ Step 5: The Missing Piece in Multi-Tenant Setup

We’re on a multi-tenant architecture. That means every query must include a tenant_id in the join or filter conditions. Otherwise, Oracle gleefully scans everything across tenants.

And guess what?

Yep — that tiny tenant_id condition was missing.

⚡ Step 6: The Fix — and the Magic

After adding the appropriate tenant condition in the join, the query’s execution time dropped from 25 minutes to 25 seconds.

🎯 Key Takeaways

• 🧠 Don't jump to alternatives before investigating the root cause.

• 🛠️ Logs can mislead — sometimes DB is the better mirror.

• 🧭 In multi-tenant systems, always enforce tenant scoping in queries.

• 📊 Query plans never lie. Trust them.

• ⏱️ Small changes in SQL can have massive performance implications.