# Production Support & Oracle SQL Interview Notes

## Core Topics

-   Oracle Query Optimization
-   Batch Jobs
-   Explain Plan
-   Production Support
-   Root Cause Analysis
-   Shell Scripting

## Key Areas to Master

-   Oracle Explain Plan and execution plans
-   End-to-end root cause analysis for slow SQL and batch jobs
-   Oracle performance views (AWR, ASH, V$SESSION, V$SQL, Wait Events)
-   Production support scenarios with step-by-step investigation
-   Shell scripting and scheduling (cron, log parsing, automation)

------------------------------------------------------------------------

# 1. How do you investigate a slow batch job?

Ask these questions in order:

1.  Is the database slow?
2.  Is the SQL slow?
3.  Which SQL statement is slow?
4.  Which processing step is slow?
5.  Which query is responsible?
6.  Review the Explain Plan.
7.  Check Wait Events.
8.  Check for locks.
9.  Check Disk I/O.
10. Check CPU utilization.

------------------------------------------------------------------------

# 2. Investigation Steps

## Step 1 - Identify the bottleneck

Split the batch job into:

-   Data extraction
-   Java processing
-   File generation

## Step 2 - Measure each stage

-   If **data extraction** is slow → Check Oracle SQL.
-   If **Java processing** is slow → Capture Thread Dump.
-   If **file generation** is slow → Check Disk I/O.

**Goal:** Isolate the bottleneck before attempting optimization.

------------------------------------------------------------------------

# 3. How do you troubleshoot a database issue?

Use the following tools:

-   Explain Plan
-   DBMS_XPLAN
-   AWR Report
-   ASH Report
-   SQL Trace
-   TKPROF
-   SQL Monitor
-   V\$SESSION
-   V\$SQL
-   Wait Events
-   Execution Plan

------------------------------------------------------------------------

# 4. What exactly do you look at in an Explain Plan?

-   Nested Loop
-   Hash Join
-   Merge Join
-   Cardinality
-   Cost
-   Predicate
-   Access Predicate
-   Filter Predicate
-   Join Order
-   Partition Pruning
-   Parallel Execution
-   Temp Sort
-   Buffer Gets

------------------------------------------------------------------------

# 5. IN vs EXISTS

## IN

-   Oracle evaluates the full subquery.
-   Good for small result sets.

## EXISTS

-   Stops immediately after finding the first matching row.
-   Better for correlated subqueries and large datasets.

------------------------------------------------------------------------

# 6. Slow Query Investigation Checklist

1.  Explain Plan
2.  SQL Trace
3.  Wait Event
4.  Which table?
5.  Which join?
6.  Missing index
7.  Cardinality
8.  Statistics
9.  Lock
10. Rewrite SQL