Oracle Database Skills

# Oracle Database Performance Tuning: Slow Query Analysis

## Issue Summary
A critical financial report query in `FINANCE_SCHEMA` is running for 12+ minutes instead of the expected 2 minutes. The query joins `TRANSACTIONS`, `ACCOUNTS`, and `CUSTOMERS` tables with a WHERE clause filtering for Q1 2023 data.

## Root Cause Analysis
1. **Execution Plan**: Full table scans on `TRANSACTIONS` (2.4M rows) due to missing index on `transaction_date`
2. **Statistics**: Outdated table statistics (last gathered 6 months ago)
3. **Bind Variables**: Hard-coded values causing suboptimal plan generation

## Recommended Solutions

### Immediate Fix (5 minutes)
```sql
-- Create index on frequently filtered column
CREATE INDEX idx_trans_date ON TRANSACTIONS(transaction_date) TABLESPACE FINANCE_IDX;

-- Gather fresh statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('FINANCE_SCHEMA', 'TRANSACTIONS');
```

### Query Optimization (10 minutes)
```sql
-- Replace hard-coded values with bind variables
SELECT /*+ GATHER_PLAN_STATISTICS */
    a.account_id, c.customer_name, SUM(t.amount)
FROM TRANSACTIONS t
JOIN ACCOUNTS a ON t.account_id = a.account_id
JOIN CUSTOMERS c ON a.customer_id = c.customer_id
WHERE t.transaction_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
                            AND TO_DATE('2023-03-31', 'YYYY-MM-DD')
GROUP BY a.account_id, c.customer_name;
```

### Monitoring
```sql
-- Check query performance after changes
SELECT sql_id, elapsed_time, cpu_time, buffer_gets
FROM v$sql WHERE sql_text LIKE '%FINANCE_SCHEMA%';
```

## Expected Outcome
- Query should complete in under 2 minutes after implementing both fixes
- CPU usage reduced from 95% to <30%
- Buffer gets reduced from 15M to 500K

## Verification Steps
1. Run the query with timing enabled:
   ```sql
   SET TIMING ON;
   SET AUTOTRACE ON;
   ```
2. Compare execution plans before/after changes
3. Monitor AWR reports for 24 hours post-deployment