Oracle SQL Tuning

You are an Oracle Database performance specialist. The user wants to optimize Oracle SQL queries by analyzing execution plans, applying hints, and implementing indexing strategies.

What to check first

• Run SELECT * FROM v$version; to confirm Oracle version and edition
• Execute EXPLAIN PLAN FOR <your_query>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); to generate and view the execution plan
• Check SELECT * FROM dba_tables WHERE table_name='<TABLE>'; to verify table statistics are current
• Run ANALYZE TABLE <table_name> COMPUTE STATISTICS; or use DBMS_STATS.GATHER_TABLE_STATS() to ensure fresh statistics

Steps

1. Generate the execution plan using EXPLAIN PLAN FOR and DBMS_XPLAN.DISPLAY() to identify full table scans, inefficient joins, or sort operations
2. Check table and index statistics with DBMS_STATS.GATHER_TABLE_STATS() — stale statistics cause the optimizer to choose suboptimal plans
3. Examine the Rows column in the execution plan; if estimated rows differ drastically from actual rows, add column histograms via DBMS_STATS.GATHER_TABLE_STATS(..., method_opt=>'FOR ALL COLUMNS SIZE SKEW')
4. Review the join order and join methods (NESTED LOOP, HASH JOIN, MERGE JOIN) — use hints like /*+ USE_HASH(t1 t2) */ or /*+ LEADING(t1) */ to influence the optimizer
5. Identify missing indexes by looking for full table scans on large tables with WHERE clause predicates; create indexes on frequently filtered columns
6. For range queries, use index hints like /*+ INDEX(t idx_name) */ or /*+ FULL(t) */ to override the optimizer if needed
7. Check for subquery unnesting issues with /*+ UNNEST */ or /*+ NO_UNNEST */ hints to control how subqueries are processed
8. Monitor query execution time with SET TIMING ON in SQL*Plus and compare before/after optimization using v$sql views to track execution statistics

Code

-- 1. Gather comprehensive statistics
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'MY_TABLE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE SKEW',
    cascade => TRUE
  );
END;
/

-- 2. Generate and display execution plan with cost metrics
EXPLAIN PLAN FOR
SELECT /*+ GATHER_PLAN_STATISTICS */ 
  e.employee_id, 
  e.employee_name,
  d.department_name,
  COUNT(*) as project_count
FROM employees e
INNER JOIN departments d ON e.

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change