SQL Tuning Fundamentals for Oracle DBAs
SQL tuning is one of the most critical skills for an Oracle DBA. A poorly written query can bring an entire system to its knees, while a well-tuned one can run in milliseconds. This guide covers the most important techniques every Oracle DBA should know. 1. Understanding the Execution Plan The execution plan is the roadmap Oracle uses to execute your SQL. Always start here when tuning. Using EXPLAIN PLAN EXPLAIN PLAN FOR SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 5000 ORDER BY e.last_name; -- Display the plan SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); Using DBMS_XPLAN with actual runtime statistics -- Step 1: Run the query with the hint SELECT /*+ gather_plan_statistics */ e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 5000; -- Step 2: Pull the actual execution plan SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( sql_id => NULL, cursor_child_no => 0, format => 'ALLSTATS LAST' ) ); 2. Gathering Statistics Stale or missing statistics are the number one cause of bad execution plans in Oracle. ...