Performance Tuning

Performance tuning is the discipline of finding why a database is slow and fixing the root cause. Lesson 32 introduced the basics — execution plans, statistics, and index anti-patterns. This lesson goes deeper: using Oracle's diagnostic infrastructure to identify what is actually consuming resources, finding the specific SQL that is causing problems, and applying the SQL Tuning Advisor to fix it. The most important rule in performance tuning is to measure first — never guess.

AWR — Automatic Workload Repository

The Automatic Workload Repository (AWR) is Oracle's built-in performance data warehouse. Every hour by default, Oracle takes a snapshot of key performance statistics — wait events, SQL execution counts, CPU usage, I/O rates, memory usage. By comparing two snapshots you can see exactly what changed between any two points in time. AWR is the starting point for almost all serious performance investigations. It requires the Oracle Diagnostics Pack licence.

-- List recent AWR snapshots -- each row is one hourly snapshot
SELECT snap_id, begin_interval_time, end_interval_time
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;
-- Generate an AWR report between two snapshots as text
-- Replace 1 with your dbid, and 42/43 with your snap_id range
SELECT output
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
    l_dbid    => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_bid      => 42,    -- beginning snapshot id
    l_eid      => 43     -- ending snapshot id
));
-- Check how often AWR snapshots are taken and how long they are retained
SELECT snap_interval, retention
FROM   dba_hist_wr_control;

• AWR retains snapshots for 8 days by default — increase retention if you need to compare week-on-week patterns
• The AWR text report contains a Top 5 Wait Events section — if a single wait event dominates it tells you exactly where the bottleneck is (CPU, I/O, locking, network)
• AWR also ranks SQL statements by elapsed time, CPU time, buffer gets, and disk reads — the top SQL list is the fastest way to find the worst-performing queries
• DBMS_WORKLOAD_REPOSITORY requires the Diagnostics Pack licence — check your licence before using it in production

ASH — Active Session History

The Active Session History (ASH) samples every active session in the database once per second and records what each session is doing — which SQL it is running, which wait event it is experiencing, which object it is accessing. This data is held in memory in V$ACTIVE_SESSION_HISTORY and flushed to disk in DBA_HIST_ACTIVE_SESS_HISTORY. ASH is invaluable for diagnosing intermittent problems — spikes that lasted only a few minutes and are gone by the time you investigate.

-- Top wait events in the last 30 minutes from ASH (in-memory, recent data)
SELECT wait_class, event,
       COUNT(*)                          AS sample_count,
       ROUND(COUNT(*) * 100
             / SUM(COUNT(*)) OVER(), 1) AS pct_of_total
FROM   v$active_session_history
WHERE  sample_time >= SYSTIMESTAMP - INTERVAL '30' MINUTE
AND    session_state = 'WAITING'
GROUP  BY wait_class, event
ORDER  BY sample_count DESC
FETCH FIRST 10 ROWS ONLY;
-- Top SQL statements by total database time in the last hour (from ASH)
SELECT sql_id,
       COUNT(*)          AS ash_samples,
       COUNT(*) * 10     AS est_seconds   -- each sample ~ 1 second of DB time
FROM   v$active_session_history
WHERE  sample_time >= SYSTIMESTAMP - INTERVAL '1' HOUR
AND    sql_id IS NOT NULL
GROUP  BY sql_id
ORDER  BY ash_samples DESC
FETCH FIRST 10 ROWS ONLY;

• db file sequential read is a single-block read — high counts mean Oracle is doing many index lookups; normal but can indicate too many index scans on a large result set
• enq: TX - row lock contention means sessions are waiting for row locks held by other sessions — investigate blocking transactions
• Each ASH sample represents approximately one second of database time — multiply sample count by the sample interval to estimate total seconds consumed
• sql_id from ASH links directly to V$SQL and DBA_HIST_SQLSTAT where you can retrieve the full SQL text and its execution statistics

Finding and Diagnosing Problem SQL

Once AWR or ASH identifies the sql_id of a slow statement, pull its full text, execution statistics, and execution plan from the data dictionary. The most useful views are V$SQL for currently cached statements and DBA_HIST_SQLSTAT for historical statistics across AWR snapshots.

-- Retrieve the SQL text for a given sql_id
SELECT sql_id, sql_fulltext, executions,
       ROUND(elapsed_time / 1e6, 2)              AS total_elapsed_sec,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1e6, 3) AS avg_elapsed_sec,
       buffer_gets,
       ROUND(buffer_gets / NULLIF(executions, 0)) AS avg_buffer_gets
FROM   v$sql
WHERE  sql_id = '8fk2mz9dqr1p7';
-- Display the current execution plan for a cached statement
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8fk2mz9dqr1p7', NULL, 'ALLSTATS LAST'));
-- Find the top 10 SQL statements by average elapsed time (requires DBA access)
SELECT sql_id,
       ROUND(AVG(elapsed_time_delta) / 1e6, 3) AS avg_elapsed_sec,
       SUM(executions_delta)                    AS total_executions
FROM   dba_hist_sqlstat
WHERE  snap_id BETWEEN 42 AND 44
GROUP  BY sql_id
ORDER  BY avg_elapsed_sec DESC
FETCH FIRST 10 ROWS ONLY;

• elapsed_time in V$SQL is stored in microseconds — divide by 1,000,000 to convert to seconds
• NULLIF(executions, 0) prevents a divide-by-zero error for statements that have been parsed but not yet executed
• ALLSTATS LAST in DBMS_XPLAN.DISPLAY_CURSOR shows both estimated rows (E-Rows) and actual rows (A-Rows) from the last execution — a large discrepancy between the two is a strong sign of stale statistics
• In this example the plan shows TABLE ACCESS FULL with E-Rows=5 but A-Rows=18400 — the optimiser severely underestimated the row count, likely due to stale statistics on the status column

SQL Tuning Advisor

The SQL Tuning Advisor is an Oracle built-in tool that analyses a specific SQL statement and recommends improvements — better statistics, new indexes, SQL profile, or query restructuring. You run it through the DBMS_SQLTUNE package. It requires the Oracle Tuning Pack licence. When the advisor recommends a SQL Profile, accepting it stores corrected cardinality estimates that override the optimiser's guesses without changing the SQL text — useful when you cannot modify application code.

-- Run the SQL Tuning Advisor on a specific sql_id
DECLARE
    v_task_name VARCHAR2(100);
BEGIN
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id      => '8fk2mz9dqr1p7',
        scope       => 'COMPREHENSIVE',   -- full analysis including index recommendations
        time_limit  => 60,                -- maximum seconds the advisor may run
        task_name   => 'tune_orders_query',
        description => 'Tuning slow orders query'
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_orders_query');
END;
/
-- Read the advisor's recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_orders_query') AS report
FROM   DUAL;
-- Accept a SQL Profile recommendation if the advisor suggests one
-- A SQL Profile stores corrected cardinality estimates -- no SQL change needed
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name    => 'tune_orders_query',
    replace      => TRUE
);

• The advisor found the same two issues visible in the execution plan — stale statistics and a missing index on the status column
• scope => 'COMPREHENSIVE' enables index recommendations and SQL profile generation — 'LIMITED' scope only checks statistics and SQL structure
• A SQL Profile is a better solution than a hint when you cannot touch the application SQL — it corrects the optimiser's estimates persistently without modifying the query text
• Always test advisor recommendations on a non-production system before applying them — an index that helps one query may slow down DML on a write-heavy table

Lesson Summary

Practice Questions

Quiz

Next up — Data Pump — How to export and import Oracle data and schema objects using expdp and impdp for migrations, cloning, and archiving.