Oracle Performance Tuning

A complete production-ready SOP for Oracle Database real-time performance tuning. Covers AWR, ADDM, ASH report generation and analysis, wait event analysis, SQL tuning with explain plan and SQL profiles, index analysis, undo and temp space issues, locking and blocking sessions, SGA and PGA parameter tuning, and Statspack setup for non-Enterprise Edition — with real commands, expected outputs, and consultant-level notes.

1. Document Info

2. Performance Tuning — Concepts You Must Know First

📝 What is Oracle performance tuning? Performance tuning is the process of identifying and resolving bottlenecks that cause the database to respond slowly, consume excessive resources, or fail to meet SLA requirements. Tuning is always reactive (fixing current problems) and proactive (preventing future problems).

📝 The Golden Rule of Oracle Tuning: Always identify the ROOT CAUSE before making any change. Never tune blindly. The most common mistake is changing parameters randomly without data to support the change. Every tuning action must be backed by evidence from AWR, ASH, or wait event analysis.

Oracle Performance Tuning Hierarchy

Start Here → Is there a specific slow SQL?
                     │
              YES ──►│──► SQL Tuning (Section 7)
                     │
              NO  ──►│──► Check Wait Events (Section 5)
                     │
                     ├──► CPU bottleneck? → Parameter Tuning (Section 9)
                     │
                     ├──► I/O bottleneck? → Storage/Index analysis
                     │
                     ├──► Memory issue? → SGA/PGA tuning (Section 9)
                     │
                     ├──► Locking? → Session analysis (Section 8)
                     │
                     └──► Undo/Temp? → Space issues (Section 8)

Key Performance Views

3. Initial Performance Assessment

📝 Before diving into specific tools, always start with a quick overall assessment of the database health. This gives you context for what you find in AWR and ASH.

3.1 — Quick Database Health Check

sqlplus / as sysdba

-- Check instance status and uptime
set linesize 200
set pagesize 50
col instance_name  for a15
col host_name      for a25
col version        for a15
col status         for a12
col startup_time   for a25

SELECT instance_name, host_name, version_full,
       status,
       TO_CHAR(startup_time,'YYYY-MM-DD HH24:MI:SS') startup_time,
       ROUND((SYSDATE - startup_time)*24,1) uptime_hours
FROM   v$instance;

3.2 — Check Current Active Sessions and Wait Events

📝 This is your real-time pulse check. Run this first when you get a call about performance problems.

-- What is the database doing RIGHT NOW?
set linesize 200
set pagesize 100
col username   for a15
col status     for a10
col event      for a40
col wait_class for a20
col machine    for a25
col sql_id     for a15
col cnt        for 9999

SELECT NVL(username,'(background)') username,
       status,
       event,
       wait_class,
       COUNT(*) cnt
FROM   v$session
WHERE  type   = 'USER'
OR     status = 'ACTIVE'
GROUP BY username, status, event, wait_class
ORDER BY cnt DESC;

3.3 — Check Top Wait Events Right Now

-- System-wide wait events sorted by total wait time
set linesize 200
set pagesize 100
col event        for a45
col wait_class   for a20
col total_waits  for 9999999999
col time_waited_sec for 9999999
col avg_wait_ms  for 999999.99

SELECT event,
       wait_class,
       total_waits,
       ROUND(time_waited/100,2)          time_waited_sec,
       ROUND(time_waited/total_waits/100*1000,2) avg_wait_ms
FROM   v$system_event
WHERE  wait_class != 'Idle'
AND    total_waits > 0
ORDER BY time_waited DESC
FETCH FIRST 20 ROWS ONLY;

3.4 — Check Hit Ratios

-- Buffer cache hit ratio (should be > 95%)
set linesize 200
set pagesize 50

SELECT ROUND(
    (1 - (phy.value / (con.value + cur.value))) * 100, 2
) buffer_cache_hit_ratio
FROM   v$sysstat phy,
       v$sysstat con,
       v$sysstat cur
WHERE  phy.name = 'physical reads'
AND    con.name = 'consistent gets'
AND    cur.name = 'db block gets';

-- Library cache hit ratio (should be > 99%)
set linesize 200
set pagesize 50

SELECT namespace,
       ROUND(gethitratio*100,2)  get_hit_ratio,
       ROUND(pinhitratio*100,2)  pin_hit_ratio,
       reloads,
       invalidations
FROM   v$librarycache
WHERE  namespace IN ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')
ORDER BY namespace;

4. AWR — Automatic Workload Repository

📝 What is AWR? AWR is Oracle’s built-in performance data repository. Every 60 minutes (by default), Oracle takes a snapshot of performance statistics and stores them in the SYSAUX tablespace. AWR reports compare two snapshots and show what the database was doing during that time period.

⚠️ LICENSE REQUIREMENT: AWR requires the Oracle Diagnostics Pack license. Do NOT use AWR queries or generate AWR reports in a non-licensed environment. Use Statspack instead (Section 11).

4.1 — Check AWR Snapshot Settings

-- Check current AWR configuration
set linesize 200
set pagesize 50
col snap_interval for a20
col retention     for a20

SELECT extract(day    from snap_interval)*24*60 +
       extract(hour   from snap_interval)*60 +
       extract(minute from snap_interval)        snap_interval_mins,
       extract(day    from retention)*24 +
       extract(hour   from retention)            retention_hours
FROM   dba_hist_wr_control;

4.2 — Modify AWR Snapshot Settings

📝 Recommended settings for production: Interval 30 minutes (more granular than default 60), Retention 35 days (5 weeks for trend analysis).

-- Change snapshot interval to 30 minutes, retention to 35 days
-- interval in minutes, retention in minutes
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval  => 30,       -- snapshot every 30 minutes
    retention => 50400     -- retain for 35 days (35*24*60 = 50400 minutes)
);

-- Verify
SELECT extract(day    from snap_interval)*24*60 +
       extract(hour   from snap_interval)*60 +
       extract(minute from snap_interval)   snap_interval_mins,
       extract(day    from retention)*24 +
       extract(hour   from retention)       retention_hours
FROM   dba_hist_wr_control;

4.3 — List Available AWR Snapshots

-- List recent snapshots to identify time range for your report
set linesize 200
set pagesize 100
col snap_id    for 9999999
col begin_time for a25
col end_time   for a25
col level#     for 999

SELECT snap_id,
       TO_CHAR(begin_interval_time,'YYYY-MM-DD HH24:MI:SS') begin_time,
       TO_CHAR(end_interval_time,  'YYYY-MM-DD HH24:MI:SS') end_time,
       snap_level
FROM   dba_hist_snapshot
WHERE  begin_interval_time > SYSDATE - 2   -- last 2 days
ORDER BY snap_id DESC;

4.4 — Take Manual AWR Snapshot

📝 When to take a manual snapshot? Before and after a tuning change so you can compare performance before and after with an exact time boundary.

-- Take a manual snapshot right now
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- Verify snapshot was taken
SELECT snap_id,
       TO_CHAR(end_interval_time,'YYYY-MM-DD HH24:MI:SS') snap_time
FROM   dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 3 ROWS ONLY;

4.5 — Generate AWR Report

📝 Two ways to generate AWR reports:

• HTML format — rich formatting, charts, easy to read. Use for sharing with management or detailed analysis.
• TEXT format — plain text. Use for quick review in terminal or when email is plain text only.

-- Method 1: Generate AWR report to screen (HTML format)
-- Replace begin_snap and end_snap with your actual snap IDs
-- Replace dbid with your database DBID

-- First get your DBID
SELECT dbid FROM v$database;

-- Generate HTML AWR report
@?/rdbms/admin/awrrpt.sql

-- You will be prompted for:
-- Report type: html or text
-- Number of days to list snapshots: enter 1 or 2
-- Begin snapshot ID: enter the snap ID (e.g., 1234)
-- End snapshot ID: enter the snap ID (e.g., 1235)
-- Report name: awrrpt_ORCL_20240115.html (or press Enter for default)

-- Method 2: Generate AWR report programmatically (for scripts)
-- Saves report to a variable then you can spool it
set linesize 200
set pagesize 0
set long 100000
set longchunksize 100000
set trimspool on

spool /tmp/awr_report.html

SELECT *
FROM   TABLE(
    DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
        l_dbid     => (SELECT dbid FROM v$database),
        l_inst_num => 1,
        l_bid      => 1234,    -- begin snap ID -- replace with actual
        l_eid      => 1235     -- end snap ID -- replace with actual
    )
);

spool off;

4.6 — Key Sections to Read in an AWR Report

📝 AWR reports contain many sections. Here is where to focus first:

4.7 — Query AWR Data Directly (Without Report)

📝 Sometimes you need specific data from AWR without generating a full report.

-- Top SQL by elapsed time for a specific AWR period
set linesize 200
set pagesize 100
col sql_id          for a15
col elapsed_secs    for 9999999.99
col executions      for 9999999
col avg_elapsed_ms  for 9999999.99
col cpu_secs        for 9999999.99
col sql_text        for a60

SELECT s.sql_id,
       ROUND(SUM(s.elapsed_time_delta)/1000000,2)     elapsed_secs,
       SUM(s.executions_delta)                         executions,
       ROUND(SUM(s.elapsed_time_delta)/
             NULLIF(SUM(s.executions_delta),0)/1000,2) avg_elapsed_ms,
       ROUND(SUM(s.cpu_time_delta)/1000000,2)          cpu_secs,
       SUBSTR(t.sql_text,1,60)                         sql_text
FROM   dba_hist_sqlstat   s,
       dba_hist_sqltext   t,
       dba_hist_snapshot  sn
WHERE  s.sql_id       = t.sql_id
AND    s.snap_id      = sn.snap_id
AND    sn.begin_interval_time > SYSDATE - 1   -- last 24 hours
GROUP BY s.sql_id, t.sql_text
ORDER BY elapsed_secs DESC
FETCH FIRST 20 ROWS ONLY;

5. ASH — Active Session History

📝 What is ASH? ASH samples all active (non-idle) sessions every second and stores the sample in memory (v$active_session_history — last ~1 hour). Older samples are flushed to AWR (dba_hist_active_sess_history). ASH is invaluable for analyzing intermittent performance problems that may last only seconds or minutes.

⚠️ LICENSE REQUIREMENT: ASH requires Oracle Diagnostics Pack license.

5.1 — Real-Time ASH Analysis

-- What were sessions doing in the last 5 minutes?
set linesize 200
set pagesize 100
col event      for a40
col wait_class for a20
col cnt        for 9999
col pct        for 999.99

SELECT event,
       wait_class,
       COUNT(*)                              cnt,
       ROUND(COUNT(*)*100/SUM(COUNT(*))
             OVER(),2)                       pct
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 5/1440   -- last 5 minutes
AND    session_type = 'FOREGROUND'
GROUP BY event, wait_class
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY;

5.2 — ASH Report Generation

-- Generate ASH report for a specific time window
-- Useful for analyzing a performance spike that already occurred
@?/rdbms/admin/ashrpt.sql

-- When prompted:
-- Report type: html or text
-- Begin time: e.g., 01/15/2024 14:00:00
-- Duration in minutes: e.g., 30
-- Report name: ashrpt_ORCL_20240115.html

5.3 — ASH Analysis for Specific Time Window

-- Identify what caused a performance problem between 2:00 PM and 2:30 PM
set linesize 200
set pagesize 100
col event        for a40
col wait_class   for a20
col sql_id       for a15
col username     for a15
col cnt          for 9999

-- Top wait events during problem window
SELECT event,
       wait_class,
       COUNT(*) cnt
FROM   dba_hist_active_sess_history
WHERE  sample_time BETWEEN
       TO_TIMESTAMP('2024-01-15 14:00:00','YYYY-MM-DD HH24:MI:SS')
   AND TO_TIMESTAMP('2024-01-15 14:30:00','YYYY-MM-DD HH24:MI:SS')
AND    session_type = 'FOREGROUND'
GROUP BY event, wait_class
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY;

-- Top SQL during problem window
set linesize 200
set pagesize 100
col sql_id       for a15
col cnt          for 9999
col event        for a40
col sql_text     for a60

SELECT ash.sql_id,
       COUNT(*)                cnt,
       MAX(ash.event)          top_event,
       SUBSTR(sq.sql_text,1,60) sql_text
FROM   dba_hist_active_sess_history ash,
       dba_hist_sqltext             sq
WHERE  ash.sample_time BETWEEN
       TO_TIMESTAMP('2024-01-15 14:00:00','YYYY-MM-DD HH24:MI:SS')
   AND TO_TIMESTAMP('2024-01-15 14:30:00','YYYY-MM-DD HH24:MI:SS')
AND    ash.sql_id      = sq.sql_id (+)
AND    ash.session_type = 'FOREGROUND'
GROUP BY ash.sql_id, sq.sql_text
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY;

6. ADDM — Automatic Database Diagnostic Monitor

📝 What is ADDM? ADDM automatically analyzes AWR snapshots and identifies performance problems. Unlike AWR which shows raw data, ADDM provides specific recommendations — “This SQL is causing 40% of database time, create this index to fix it.” ADDM runs automatically after every AWR snapshot.

⚠️ LICENSE REQUIREMENT: ADDM requires Oracle Diagnostics Pack license.

6.1 — View ADDM Findings

-- View recent ADDM task findings
set linesize 200
set pagesize 100
col task_name   for a35
col finding     for a60
col type        for a20
col impact_pct  for 999.99

SELECT t.task_name,
       f.type,
       ROUND(f.impact/t.db_time*100,2) impact_pct,
       SUBSTR(f.message,1,60)          finding
FROM   dba_advisor_tasks    t,
       dba_advisor_findings f
WHERE  t.task_id    = f.task_id
AND    t.advisor_name = 'ADDM'
AND    t.created    > SYSDATE - 1
ORDER BY f.impact DESC
FETCH FIRST 20 ROWS ONLY;

6.2 — Generate ADDM Report Manually

-- Run ADDM for specific snapshot range
DECLARE
    l_task_name VARCHAR2(30);
BEGIN
    DBMS_ADVISOR.CREATE_TASK(
        advisor_name  => 'ADDM',
        task_name     => l_task_name,
        task_desc     => 'Manual ADDM analysis'
    );

    DBMS_ADVISOR.SET_TASK_PARAMETER(
        task_name => l_task_name,
        parameter => 'START_SNAPSHOT',
        value     => 1234   -- replace with your begin snap ID
    );

    DBMS_ADVISOR.SET_TASK_PARAMETER(
        task_name => l_task_name,
        parameter => 'END_SNAPSHOT',
        value     => 1235   -- replace with your end snap ID
    );

    DBMS_ADVISOR.EXECUTE_TASK(task_name => l_task_name);

    DBMS_OUTPUT.PUT_LINE('Task: ' || l_task_name);
END;
/

-- View the report
set linesize 200
set long 100000
set pagesize 0

SELECT DBMS_ADVISOR.GET_TASK_REPORT(
    task_name => '<task_name_from_above>'
) FROM dual;

7. Wait Event Analysis

📝 Why focus on wait events? Every time an Oracle session has to wait for something (a block to be read from disk, a lock to be released, a log write to complete), it records that wait. Wait events tell you exactly WHY the database is slow — not just that it is slow. Fixing the top wait event is always more effective than random parameter changes.

7.1 — Wait Event Classification

📝 Oracle groups wait events into classes. Know these classes:

7.2 — Real-Time Wait Event Analysis

-- Current wait events across all active sessions
set linesize 200
set pagesize 100
col sid        for 9999
col username   for a15
col event      for a40
col wait_class for a20
col seconds_in_wait for 9999999
col state      for a20
col sql_id     for a15

SELECT s.sid,
       s.username,
       s.status,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.state,
       s.sql_id
FROM   v$session s
WHERE  s.wait_class != 'Idle'
AND    s.type        = 'USER'
ORDER BY s.seconds_in_wait DESC;

7.3 — Most Common Wait Events and Their Fixes

-- Detailed wait event analysis with context
set linesize 200
set pagesize 100
col event          for a40
col wait_class     for a20
col total_waits    for 9999999999
col total_secs     for 9999999.99
col avg_wait_ms    for 999999.99

SELECT event,
       wait_class,
       total_waits,
       ROUND(time_waited_micro/1000000,2)   total_secs,
       ROUND(time_waited_micro/
             NULLIF(total_waits,0)/1000,2)  avg_wait_ms
FROM   v$system_event
WHERE  wait_class    != 'Idle'
AND    total_waits    > 100
ORDER BY time_waited_micro DESC
FETCH FIRST 25 ROWS ONLY;

Common wait events and what to do:

7.4 — Diagnose Specific Wait Event (Buffer Busy Waits)

-- Find the specific object causing buffer busy waits
set linesize 200
set pagesize 100
col object_name  for a40
col object_type  for a20
col owner        for a15
col count        for 9999

SELECT o.owner,
       o.object_name,
       o.object_type,
       COUNT(*) cnt
FROM   v$session        s,
       dba_objects      o
WHERE  s.event     = 'buffer busy waits'
AND    s.row_wait_obj# = o.object_id
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY cnt DESC;

8. SQL Tuning

📝 SQL tuning is the highest-impact performance tuning activity. A single poorly written SQL statement can consume 90% of database resources. Identifying and fixing the top 5 worst SQL statements usually solves 80% of performance problems.

8.1 — Identify Top SQL from AWR

-- Top 20 SQL by total elapsed time (last 24 hours from AWR)
set linesize 200
set pagesize 100
col sql_id         for a15
col elapsed_secs   for 9999999.99
col executions     for 9999999
col avg_ms         for 9999999.99
col sql_text       for a60

SELECT s.sql_id,
       ROUND(SUM(s.elapsed_time_delta)/1000000,2)   elapsed_secs,
       SUM(s.executions_delta)                        executions,
       ROUND(SUM(s.elapsed_time_delta)/
             NULLIF(SUM(s.executions_delta),0)/1000,2) avg_ms,
       SUBSTR(t.sql_text,1,60)                        sql_text
FROM   dba_hist_sqlstat  s,
       dba_hist_sqltext  t,
       dba_hist_snapshot sn
WHERE  s.sql_id       = t.sql_id
AND    s.snap_id      = sn.snap_id
AND    sn.begin_interval_time > SYSDATE - 1
GROUP BY s.sql_id, t.sql_text
ORDER BY elapsed_secs DESC
FETCH FIRST 20 ROWS ONLY;

8.2 — Find Currently Running SQL

-- SQL currently executing (right now)
set linesize 200
set pagesize 100
col sid         for 9999
col username    for a15
col status      for a10
col event       for a35
col elapsed_sec for 999999
col sql_id      for a15
col sql_text    for a60

SELECT s.sid,
       s.username,
       s.status,
       s.event,
       ROUND(s.last_call_et,0) elapsed_sec,
       s.sql_id,
       SUBSTR(q.sql_text,1,60) sql_text
FROM   v$session s,
       v$sql     q
WHERE  s.sql_id   = q.sql_id
AND    s.status   = 'ACTIVE'
AND    s.type     = 'USER'
AND    s.wait_class != 'Idle'
ORDER BY s.last_call_et DESC;

8.3 — Get Full SQL Text for a sql_id

-- Get complete SQL text for a specific sql_id
set linesize 200
set long 100000
set pagesize 0

-- From shared pool (current)
SELECT sql_fulltext
FROM   v$sql
WHERE  sql_id = '&sql_id'
FETCH FIRST 1 ROW ONLY;

-- From AWR (historical)
SELECT sql_text
FROM   dba_hist_sqltext
WHERE  sql_id = '&sql_id';

8.4 — Generate Execution Plan (EXPLAIN PLAN)

📝 What is an execution plan? The execution plan shows HOW Oracle will execute a SQL statement — which indexes it will use, how it will join tables, in what order. Understanding execution plans is the most fundamental SQL tuning skill.

-- Generate explain plan for a SQL statement
-- First delete any previous plan for this statement
DELETE FROM plan_table WHERE statement_id = 'TEST_STMT';

EXPLAIN PLAN
    SET STATEMENT_ID = 'TEST_STMT'
    FOR
    SELECT e.employee_id, e.last_name, d.department_name
    FROM   hr.employees   e,
           hr.departments d
    WHERE  e.department_id = d.department_id
    AND    e.salary > 5000;

-- Display the execution plan
set linesize 200
set pagesize 100

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(
    'PLAN_TABLE',
    'TEST_STMT',
    'TYPICAL'  -- options: BASIC, TYPICAL, ALL, ADVANCED
));

8.5 — Get Actual Execution Plan for a Running SQL

📝 Why use actual plan instead of explain plan? EXPLAIN PLAN shows what Oracle WOULD do. The actual plan from v$sql_plan shows what Oracle ACTUALLY DID — including real row counts, real elapsed time, actual vs estimated rows. Actual plans are far more useful for tuning.

-- Get actual plan for a sql_id from shared pool
-- This shows real execution statistics including actual rows processed
set linesize 200
set pagesize 100

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
    '&sql_id',    -- replace with actual sql_id
    NULL,          -- child_number (NULL = all children)
    'ALLSTATS LAST'  -- shows actual rows, elapsed time per operation
));

8.6 — Read and Interpret an Execution Plan

📝 Key things to look for in an execution plan:

Example plan output:
----------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows | Cost |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |   10 |   50 |
|   1 |  HASH JOIN                 |             |   10 |   50 |
|   2 |   TABLE ACCESS FULL        | DEPARTMENTS |   27 |    3 |
|   3 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES|   10 |   47 |
|   4 |    INDEX RANGE SCAN        | EMP_SAL_IDX |   10 |    2 |
----------------------------------------------------------------------

📝 What each operation means:

• TABLE ACCESS FULL — full table scan. May be correct for small tables, problematic for large ones.
• INDEX RANGE SCAN — uses an index to find a range of values. Usually good.
• INDEX UNIQUE SCAN — uses index to find exactly one row. Best.
• HASH JOIN — joins two result sets using a hash. Good for large datasets.
• NESTED LOOPS — for each row in outer set, look up inner set. Good when outer set is small and inner has index.
• MERGE JOIN — requires both sets to be sorted. Used when large sorted data is available.

Red flags in execution plans:

8.7 — SQL Tuning Advisor

📝 What is SQL Tuning Advisor (STA)? STA is an Oracle built-in tool that analyzes a specific SQL statement and provides recommendations — create this index, gather statistics on this table, accept this SQL profile. It is the automated version of manual SQL analysis.

⚠️ LICENSE REQUIREMENT: SQL Tuning Advisor requires Oracle Tuning Pack license.

-- Run SQL Tuning Advisor for a specific sql_id
DECLARE
    l_task_name  VARCHAR2(30);
    l_sql_text   CLOB;
BEGIN
    -- Create tuning task for a SQL from AWR
    l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id        => '&sql_id',
        scope         => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit    => 300,    -- max 300 seconds analysis time
        task_name     => 'tune_sql_&sql_id',
        description   => 'Tuning task for problem SQL'
    );

    DBMS_OUTPUT.PUT_LINE('Task created: ' || l_task_name);
END;
/

-- Execute the tuning task
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_sql_&sql_id');

-- View the recommendations
set linesize 200
set long 100000
set pagesize 0

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_sql_&sql_id')
FROM   dual;

8.8 — Create and Use SQL Profile

📝 What is a SQL Profile? A SQL Profile is a set of corrective hints stored in the database that Oracle applies automatically whenever a specific SQL statement executes. It fixes execution plans without changing application code. STA often recommends creating a SQL profile.

-- Accept a SQL Profile recommended by STA
-- (After running SQL Tuning Advisor above)
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name => 'tune_sql_&sql_id',
    replace   => TRUE
);

-- List existing SQL profiles
set linesize 200
set pagesize 100
col name        for a30
col sql_text    for a50
col status      for a10
col created     for a20

SELECT name,
       sql_text,
       status,
       TO_CHAR(created,'YYYY-MM-DD HH24:MI') created
FROM   dba_sql_profiles
ORDER BY created DESC;

-- Drop a SQL profile if needed
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_...');

8.9 — Create SQL Plan Baseline (SQL Patch)

📝 What is a SQL Plan Baseline? A SQL Plan Baseline pins a specific execution plan to a SQL statement. Even if statistics change or the optimizer would normally choose a different plan, Oracle always uses the pinned plan. Use this when you have verified a specific plan is optimal and want to prevent plan regression.

-- Load a specific plan from cursor cache into baselines
-- First find the sql_id and plan_hash_value of the good plan
set linesize 200
set pagesize 100
col sql_id          for a15
col plan_hash_value for 9999999999
col executions      for 9999999
col elapsed_secs    for 9999999.99

SELECT sql_id, plan_hash_value, executions,
       ROUND(elapsed_time/1000000,2) elapsed_secs
FROM   v$sql
WHERE  sql_id = '&sql_id'
ORDER BY elapsed_time;

-- Load the good plan as a SQL Plan Baseline
DECLARE
    l_plans PLS_INTEGER;
BEGIN
    l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id          => '&sql_id',
        plan_hash_value => &plan_hash_value
    );
    DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);
END;
/

-- Verify baseline was created
set linesize 200
set pagesize 100
col sql_handle     for a35
col plan_name      for a35
col enabled        for a8
col accepted       for a8
col fixed          for a8

SELECT sql_handle, plan_name, enabled, accepted, fixed,
       TO_CHAR(created,'YYYY-MM-DD HH24:MI') created
FROM   dba_sql_plan_baselines
ORDER BY created DESC;

9. Index Analysis and Rebuilding

9.1 — Find Missing Indexes (High Full Table Scans)

-- Tables with most full table scans (candidates for indexing)
set linesize 200
set pagesize 100
col owner      for a20
col table_name for a35
col full_scans for 9999999
col rows_processed for 9999999999

SELECT o.owner,
       o.object_name table_name,
       COUNT(*)       full_scans
FROM   v$sql_plan      p,
       dba_objects     o
WHERE  p.operation    = 'TABLE ACCESS'
AND    p.options      = 'FULL'
AND    p.object_owner = o.owner
AND    p.object_name  = o.object_name
AND    o.object_type  = 'TABLE'
GROUP BY o.owner, o.object_name
ORDER BY full_scans DESC
FETCH FIRST 20 ROWS ONLY;

9.2 — Find Unusable and Invalid Indexes

set linesize 200
set pagesize 100
col owner       for a20
col index_name  for a35
col table_name  for a35
col status      for a12
col index_type  for a20

-- Find unusable indexes
SELECT owner, index_name, table_name, status, index_type
FROM   dba_indexes
WHERE  status IN ('UNUSABLE','INVALID','N/A')
ORDER BY owner, table_name;

-- Find unusable index partitions
SELECT index_owner, index_name, partition_name, status
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'
ORDER BY index_owner, index_name;

9.3 — Identify Indexes Not Being Used

-- Enable index monitoring (must be done per index)
-- Run this on suspect indexes
ALTER INDEX hr.emp_name_idx MONITORING USAGE;

-- Wait for application to run (at least 24 hours)
-- Then check if index was used
set linesize 200
set pagesize 100
col index_name for a35
col table_name for a35
col monitoring for a5
col used       for a5
col start_time for a25
col end_time   for a25

SELECT index_name, table_name, monitoring, used,
       TO_CHAR(start_monitoring,'YYYY-MM-DD HH24:MI') start_time,
       TO_CHAR(end_monitoring,'YYYY-MM-DD HH24:MI')   end_time
FROM   v$object_usage
ORDER BY index_name;

-- Stop monitoring
ALTER INDEX hr.emp_name_idx NOMONITORING USAGE;

9.4 — Rebuild Fragmented Indexes

📝 Why rebuild indexes? Over time as rows are inserted, updated, and deleted, indexes can become fragmented — blocks are partially empty, the tree becomes unbalanced. This increases the number of block reads needed for index lookups. Rebuilding reclaims space and rebalances the tree.

📝 When to rebuild? Only when index statistics show significant fragmentation. Do NOT rebuild indexes on a schedule without checking if they need it — unnecessary rebuilds waste time and generate redo.

-- Check index fragmentation using ANALYZE
-- Run for specific indexes that seem slow
ANALYZE INDEX hr.emp_emp_id_pk VALIDATE STRUCTURE;

-- Check fragmentation results
set linesize 200
set pagesize 100
col name         for a35
col del_lf_rows  for 9999999
col lf_rows      for 9999999
col pct_deleted  for 999.99

SELECT name,
       del_lf_rows,
       lf_rows,
       ROUND(del_lf_rows*100/NULLIF(lf_rows,0),2) pct_deleted
FROM   index_stats;

-- If pct_deleted > 20% -- consider rebuild

-- Rebuild index online (no downtime -- application can still use table)
ALTER INDEX hr.emp_emp_id_pk REBUILD ONLINE;

-- Rebuild index with specific tablespace
ALTER INDEX hr.emp_emp_id_pk
    REBUILD ONLINE
    TABLESPACE users
    PARALLEL 4;

-- Rebuild all indexes on a table
BEGIN
    FOR idx IN (
        SELECT index_name
        FROM   dba_indexes
        WHERE  table_owner = 'HR'
        AND    table_name  = 'EMPLOYEES'
        AND    status     != 'UNUSABLE'
    ) LOOP
        EXECUTE IMMEDIATE
            'ALTER INDEX hr.' || idx.index_name || ' REBUILD ONLINE';
        DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || idx.index_name);
    END LOOP;
END;
/

9.5 — Coalesce Index (Alternative to Rebuild)

📝 COALESCE vs REBUILD: COALESCE merges adjacent leaf blocks within each branch — it is lighter weight than REBUILD and does not create a new segment. Use COALESCE for routine maintenance. Use REBUILD when you need to move the index to a different tablespace or change storage attributes.

-- Coalesce index (merges leaf blocks, lighter than rebuild)
ALTER INDEX hr.emp_emp_id_pk COALESCE;

10. Undo and Temp Space Issues

10.1 — Diagnose Undo Space Issues

📝 Common undo errors:

• ORA-01555: Snapshot too old — query ran too long and undo data it needed was overwritten
• ORA-30036: Unable to extend segment in undo tablespace — undo tablespace is full

-- Check undo tablespace usage
set linesize 200
set pagesize 100
col tablespace_name for a25
col status          for a12
col total_mb        for 9999999
col used_mb         for 9999999
col free_mb         for 9999999
col used_pct        for 999.99

SELECT u.tablespace_name,
       u.status,
       ROUND(t.total_bytes/1024/1024,2) total_mb,
       ROUND(u.used_bytes/1024/1024,2)  used_mb,
       ROUND((t.total_bytes-u.used_bytes)/1024/1024,2) free_mb,
       ROUND(u.used_bytes*100/t.total_bytes,2) used_pct
FROM  (SELECT tablespace_name,
              SUM(DECODE(status,'ACTIVE',bytes,
                                'UNEXPIRED',bytes,0)) used_bytes
       FROM   dba_undo_extents
       GROUP BY tablespace_name) u,
      (SELECT tablespace_name, SUM(bytes) total_bytes
       FROM   dba_data_files
       GROUP BY tablespace_name) t
WHERE  u.tablespace_name = t.tablespace_name;

-- Undo statistics -- check for tuning needs
set linesize 200
set pagesize 100
col begin_time  for a25
col end_time    for a25
col undotsn     for 9999
col txncount    for 9999999
col maxquerylen for 9999999
col ssolderrcnt for 9999

SELECT TO_CHAR(begin_time,'YYYY-MM-DD HH24:MI') begin_time,
       TO_CHAR(end_time,'YYYY-MM-DD HH24:MI')   end_time,
       undotsn,
       txncount,
       maxquerylen,
       ssolderrcnt    -- ORA-01555 occurrences
FROM   v$undostat
ORDER BY begin_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Check UNDO_RETENTION setting
SHOW PARAMETER undo_retention;
-- Default is 900 seconds (15 minutes)
-- If ORA-01555 errors occur -- increase this value

-- Increase undo retention (if ORA-01555 errors observed)
ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=BOTH;  -- 1 hour

-- Add datafile to undo tablespace if it is full
ALTER TABLESPACE undotbs1
    ADD DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs02.dbf'
    SIZE 10G
    AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

10.2 — Diagnose Temp Space Issues

📝 Common temp errors: ORA-01652: Unable to extend temp segment — temp tablespace is full. Caused by large sort operations, hash joins, or long-running queries.

-- Check current temp usage
set linesize 200
set pagesize 100
col username    for a15
col session_num for 9999999
col sql_id      for a15
col tablespace  for a20
col segtype     for a15
col mb_used     for 9999999.99

SELECT s.username,
       s.sid || ',' || s.serial# session_num,
       s.sql_id,
       u.tablespace,
       u.segtype,
       ROUND(u.blocks * t.block_size / 1024/1024,2) mb_used
FROM   v$sort_usage u,
       v$session    s,
       dba_tablespaces t
WHERE  u.session_addr = s.saddr
AND    u.tablespace   = t.tablespace_name
ORDER BY mb_used DESC;

-- Total temp usage
set linesize 200
set pagesize 50

SELECT tablespace_name,
       ROUND(total_blocks*8192/1024/1024,2)         total_mb,
       ROUND(used_blocks*8192/1024/1024,2)           used_mb,
       ROUND(free_blocks*8192/1024/1024,2)           free_mb,
       ROUND(used_blocks*100/NULLIF(total_blocks,0),2) used_pct
FROM   v$temp_space_header;

-- Add space to temp tablespace if needed
ALTER TABLESPACE temp
    ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/temp02.dbf'
    SIZE 10G
    AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- Check what SQL is consuming large temp space
set linesize 200
set pagesize 100
col sql_id     for a15
col mb_used    for 9999999.99
col sql_text   for a60

SELECT u.sqladdr,
       ROUND(SUM(u.blocks*8192/1024/1024),2) mb_used,
       SUBSTR(q.sql_text,1,60) sql_text,
       q.sql_id
FROM   v$sort_usage u,
       v$sql        q
WHERE  u.sqlhash = q.hash_value
GROUP BY u.sqladdr, q.sql_text, q.sql_id
ORDER BY mb_used DESC
FETCH FIRST 10 ROWS ONLY;

11. Locking and Blocking Sessions

11.1 — Find Blocking Sessions

📝 What is a blocking session? When Session A holds a lock on a row and Session B also wants to lock that row, Session B waits. Session A is the blocker, Session B is the waiter. If this goes on for a long time, applications hang. Finding and resolving blockers is one of the most common DBA tasks.

-- Find all blocking sessions and their victims
set linesize 200
set pagesize 100
col blocker_sid    for 9999
col blocker_user   for a15
col blocker_machine for a25
col blocker_sql    for a50
col waiter_sid     for 9999
col waiter_user    for a15
col waiter_sql     for a50
col wait_secs      for 9999999

SELECT b.sid                              blocker_sid,
       b.username                         blocker_user,
       b.machine                          blocker_machine,
       SUBSTR(bq.sql_text,1,50)           blocker_sql,
       w.sid                              waiter_sid,
       w.username                         waiter_user,
       SUBSTR(wq.sql_text,1,50)           waiter_sql,
       w.seconds_in_wait                  wait_secs
FROM   v$session b,
       v$session w,
       v$sql     bq,
       v$sql     wq
WHERE  b.sid      = w.blocking_session
AND    b.sql_id   = bq.sql_id (+)
AND    w.sql_id   = wq.sql_id (+)
ORDER BY wait_secs DESC;

11.2 — Find All Locks in the Database

-- Comprehensive lock view
set linesize 200
set pagesize 100
col sid         for 9999
col username    for a15
col lock_type   for a20
col object_name for a35
col mode_held   for a15
col mode_req    for a15
col block       for 9999

SELECT s.sid,
       s.username,
       DECODE(l.type,
           'TM','DML (Table Lock)',
           'TX','Transaction (Row Lock)',
           'UL','User Lock',
           l.type)                 lock_type,
       o.object_name,
       DECODE(l.lmode,
           0,'None', 1,'Null', 2,'Row Share',
           3,'Row Excl', 4,'Share', 5,'Share Row Excl',
           6,'Exclusive', l.lmode) mode_held,
       DECODE(l.request,
           0,'None', 1,'Null', 2,'Row Share',
           3,'Row Excl', 4,'Share', 5,'Share Row Excl',
           6,'Exclusive', l.request) mode_req,
       l.block
FROM   v$lock    l,
       v$session s,
       dba_objects o
WHERE  l.sid     = s.sid
AND    l.id1     = o.object_id (+)
AND    l.type   IN ('TM','TX','UL')
ORDER BY l.block DESC, s.sid;

11.3 — Find Lock Wait Chain (Deadlock Analysis)

-- Show complete wait chain (who is blocking whom)
set linesize 200
set pagesize 100
col wait_chain for a200

SELECT LPAD(' ',2*(LEVEL-1)) || s.sid || ' (' ||
       NVL(s.username,'(bg)') || ')' ||
       ' waits for ' || s.blocking_session ||
       ' [' || s.event || ' ' || s.seconds_in_wait || 's]' wait_chain
FROM   v$session s
START WITH s.blocking_session IS NOT NULL
       AND s.blocking_session NOT IN (
           SELECT sid FROM v$session
           WHERE  blocking_session IS NOT NULL
       )
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY s.sid;

11.4 — Kill a Blocking Session

⚠️ IMPORTANT: Never kill a session without first understanding what it is doing and getting approval from the application owner. Killing a session rolls back its transaction which may take time and can affect application users. Always try to contact the session owner first.

-- Get details about the blocking session before killing
set linesize 200
set pagesize 50
col username  for a15
col machine   for a25
col program   for a35
col status    for a10
col logon_time for a25

SELECT sid, serial#, username, machine, program,
       status,
       TO_CHAR(logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time,
       last_call_et seconds_active
FROM   v$session
WHERE  sid = &blocking_sid;

-- Kill the blocking session
-- Format: 'SID,SERIAL#'
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

-- If session does not die (OS-level kill needed)
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;
-- Get the OS PID
SELECT spid FROM v$process p, v$session s
WHERE  p.addr = s.paddr AND s.sid = &sid;

-- Then as root: kill -9 <spid>

11.5 — Find and Fix Missing Foreign Key Indexes (Common Deadlock Cause)

📝 Why does this cause locking? When a child table has a foreign key to a parent table but NO index on the FK column, Oracle must lock the entire child table during parent table DML operations. This is one of the most common and easily overlooked causes of locking problems.

-- Find foreign keys without corresponding indexes
set linesize 200
set pagesize 100
col owner       for a20
col table_name  for a30
col fk_name     for a35
col fk_columns  for a50

SELECT c.owner,
       c.table_name,
       c.constraint_name fk_name,
       c.status,
       cc.column_name    fk_columns
FROM   dba_constraints  c,
       dba_cons_columns cc
WHERE  c.constraint_type = 'R'
AND    c.owner           = cc.owner
AND    c.constraint_name = cc.constraint_name
AND    NOT EXISTS (
    SELECT 1
    FROM   dba_ind_columns ic
    WHERE  ic.table_owner  = c.owner
    AND    ic.table_name   = c.table_name
    AND    ic.column_name  = cc.column_name
    AND    ic.column_position = 1
)
ORDER BY c.owner, c.table_name;

-- Create index on missing FK column (example)
-- CREATE INDEX hr.emp_dept_fk_idx ON hr.employees(department_id);

12. SGA and PGA Parameter Tuning

12.1 — Check Current Memory Configuration

-- Overview of current memory allocation
set linesize 200
set pagesize 100
col name  for a40
col value for a25

SELECT name, value
FROM   v$parameter
WHERE  name IN (
    'memory_target',
    'memory_max_target',
    'sga_target',
    'sga_max_size',
    'pga_aggregate_target',
    'pga_aggregate_limit',
    'db_cache_size',
    'shared_pool_size',
    'large_pool_size',
    'java_pool_size',
    'streams_pool_size'
)
ORDER BY name;

12.2 — Check SGA Component Sizing

-- Current SGA component sizes and tuning advisories
set linesize 200
set pagesize 100
col component    for a35
col current_size_mb for 9999999
col min_size_mb  for 9999999
col max_size_mb  for 9999999

SELECT component,
       ROUND(current_size/1024/1024,0)   current_size_mb,
       ROUND(min_size/1024/1024,0)        min_size_mb,
       ROUND(max_size/1024/1024,0)        max_size_mb
FROM   v$sga_dynamic_components
ORDER BY current_size DESC;

12.3 — SGA Size Advisory

📝 What is SGA advisory? Oracle continuously simulates what the buffer cache hit ratio would be at different SGA sizes. Use this to determine optimal SGA size without guessing.

-- SGA sizing recommendations
set linesize 200
set pagesize 100
col sga_size_mb     for 9999999
col estd_db_time_s  for 9999999
col estd_physical_reads for 9999999
col db_time_factor  for 999.99

SELECT sga_size/1024/1024                   sga_size_mb,
       estd_db_time                         estd_db_time_s,
       estd_physical_reads,
       ROUND(estd_db_time/
             (SELECT estd_db_time
              FROM v$sga_target_advice
              WHERE sga_size_factor=1.0),2) db_time_factor
FROM   v$sga_target_advice
ORDER BY sga_size;

12.4 — Buffer Cache Advisory

-- Buffer cache size advisory
set linesize 200
set pagesize 100
col size_mb            for 9999999
col estd_physical_reads for 9999999
col estd_pct_prd       for 999.99

SELECT size_for_estimate/1024/1024         size_mb,
       estd_physical_reads,
       ROUND(estd_physical_read_factor*100,2) estd_pct_prd
FROM   v$db_cache_advice
WHERE  name      = 'DEFAULT'
AND    block_size = (SELECT value FROM v$parameter
                     WHERE name = 'db_block_size')
ORDER BY size_for_estimate;

12.5 — Shared Pool Advisory

-- Shared pool sizing advisory
set linesize 200
set pagesize 100
col shared_pool_size_mb for 9999999
col estd_lc_time_saved  for 9999999

SELECT shared_pool_size_for_estimate/1024/1024  shared_pool_size_mb,
       estd_lc_size/1024/1024                   estd_lc_size_mb,
       estd_lc_time_saved
FROM   v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;

12.6 — PGA Advisory

-- PGA sizing advisory
set linesize 200
set pagesize 100
col pga_target_mb      for 9999999
col estd_pga_cache_hit_pct for 999.99
col estd_overalloc_cnt for 9999999

SELECT pga_target_for_estimate/1024/1024     pga_target_mb,
       ROUND(estd_pga_cache_hit_pct,2)        estd_pga_cache_hit_pct,
       estd_overalloc_cnt
FROM   v$pga_target_advice
ORDER BY pga_target_for_estimate;

12.7 — Tune Key Parameters

-- After reviewing advisory recommendations, apply changes
sqlplus / as sysdba

-- Increase SGA Target (if advisory shows benefit)
ALTER SYSTEM SET SGA_TARGET=8G SCOPE=BOTH;

-- Increase PGA (if advisory shows over-allocation)
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2G SCOPE=BOTH;

-- Increase shared pool (if hard parse rate is high)
ALTER SYSTEM SET SHARED_POOL_SIZE=2G SCOPE=BOTH;

-- Tune cursor parameters (reduce hard parses)
-- session_cached_cursors = number of cursors cached per session
ALTER SYSTEM SET SESSION_CACHED_CURSORS=100 SCOPE=BOTH;

-- open_cursors = max open cursors per session
ALTER SYSTEM SET OPEN_CURSORS=500 SCOPE=BOTH;

-- cursor_sharing = FORCE makes Oracle treat similar SQL as same
-- Use with caution -- can cause plan instability
-- ALTER SYSTEM SET CURSOR_SHARING=FORCE SCOPE=BOTH;

-- LOG_BUFFER (increase if log buffer space wait event is high)
-- Requires restart
ALTER SYSTEM SET LOG_BUFFER=50M SCOPE=SPFILE;

12.8 — Check Parse Statistics

-- Hard vs soft parse ratio (high hard parses = missing bind variables)
set linesize 200
set pagesize 50

SELECT s1.value                               total_parses,
       s2.value                               hard_parses,
       s1.value - s2.value                    soft_parses,
       ROUND(s2.value*100/NULLIF(s1.value,0),2) hard_parse_pct
FROM   v$sysstat s1, v$sysstat s2
WHERE  s1.name = 'parse count (total)'
AND    s2.name = 'parse count (hard)';

📝 Hard parse ratio above 10% is a concern. It indicates applications are not using bind variables and Oracle must parse every SQL from scratch. Fix: ensure applications use bind variables, or set CURSOR_SHARING=FORCE as a temporary workaround.

13. Statspack — For Non-Enterprise Edition

📝 What is Statspack? Statspack is Oracle’s predecessor to AWR. It is available in ALL Oracle editions — including Standard Edition — and does not require the Diagnostics Pack license. It provides similar functionality to AWR (snapshots, reports, top SQL) but without the automation of AWR. If you are in a Standard Edition environment, Statspack is your primary performance analysis tool.

13.1 — Install Statspack

# Connect to database as SYSDBA from ORACLE_HOME
su - oracle
sqlplus / as sysdba

-- Create PERFSTAT tablespace for Statspack data
-- Statspack data can be significant -- allocate at least 2GB
CREATE TABLESPACE perfstat
    DATAFILE '/u01/app/oracle/oradata/ORCL/perfstat01.dbf'
    SIZE 2G
    AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

-- Run Statspack install script
-- It will prompt for PERFSTAT password and tablespace
@?/rdbms/admin/spcreate.sql

-- When prompted:
-- Enter password for PERFSTAT: Perfstat_123
-- Enter tablespace: PERFSTAT
-- Enter temp tablespace: TEMP

-- Verify Statspack installed correctly
CONNECT perfstat/Perfstat_123
SELECT * FROM stats$statspack_parameter;

13.2 — Configure Statspack

-- Connect as PERFSTAT
sqlplus perfstat/Perfstat_123

-- View default settings
set linesize 200
set pagesize 50
col name  for a30
col value for a20

SELECT name, value
FROM   stats$statspack_parameter;

-- Change snapshot level (default 5 is fine for most environments)
-- Level 5 = captures all the major statistics
-- Level 6 = also captures SQL statistics
-- Level 7 = also captures segment statistics (more space)
EXEC STATSPACK.SNAP(i_snap_level=>5);

13.3 — Take a Manual Statspack Snapshot

-- Take a snapshot manually
-- Run before and after the period you want to analyze
EXEC STATSPACK.SNAP;

-- Verify snapshot was taken
set linesize 200
set pagesize 50
col snap_id   for 9999999
col snap_time for a25

SELECT snap_id,
       TO_CHAR(snap_time,'YYYY-MM-DD HH24:MI:SS') snap_time
FROM   stats$snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

13.4 — Schedule Automatic Statspack Snapshots

-- Create a job to take snapshots every 30 minutes
-- Using DBMS_SCHEDULER (recommended)
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'STATSPACK_SNAPSHOT_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'STATSPACK.SNAP;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
        enabled         => TRUE,
        comments        => 'Automatic Statspack snapshot every 30 minutes'
    );
END;
/

-- Verify job was created and is running
SELECT job_name, enabled, state, last_run_duration
FROM   dba_scheduler_jobs
WHERE  job_name = 'STATSPACK_SNAPSHOT_JOB';

13.5 — Generate Statspack Report

-- Generate Statspack report between two snapshots
-- First list available snapshots
SELECT snap_id,
       TO_CHAR(snap_time,'YYYY-MM-DD HH24:MI:SS') snap_time
FROM   stats$snapshot
ORDER BY snap_id DESC;

-- Run the report
@?/rdbms/admin/spreport.sql

-- You will be prompted for:
-- Database ID (press Enter for default)
-- Instance number (press Enter for default)
-- Begin snapshot ID
-- End snapshot ID
-- Report name (e.g., sp_report_20240115.lst)

13.6 — Clean Up Old Statspack Data

-- Statspack data accumulates -- purge old snapshots regularly
-- Purge snapshots older than 30 days

EXEC STATSPACK.PURGE(
    i_begin_snap => (
        SELECT MIN(snap_id)
        FROM   stats$snapshot
        WHERE  snap_time < SYSDATE - 30
    ),
    i_end_snap => (
        SELECT MAX(snap_id)
        FROM   stats$snapshot
        WHERE  snap_time < SYSDATE - 30
    ),
    i_extended_purge => TRUE
);

-- Or purge to keep only last N days
EXEC STATSPACK.PURGE(
    i_num_days => 30   -- keep last 30 days only
);

13.7 — Uninstall Statspack (If Needed)

-- Uninstall Statspack completely
sqlplus / as sysdba

@?/rdbms/admin/spdrop.sql

-- Then drop the tablespace
DROP TABLESPACE perfstat
    INCLUDING CONTENTS AND DATAFILES;

14. Performance Tuning Checklist (Quick Assessment)

📝 Use this checklist when you get a “database is slow” call. Work through it in order.

-- STEP 1: What is the database doing RIGHT NOW?
SELECT event, wait_class, COUNT(*) cnt
FROM   v$session
WHERE  wait_class != 'Idle' AND type = 'USER'
GROUP BY event, wait_class ORDER BY cnt DESC;

-- STEP 2: Are there any blocking sessions?
SELECT blocking_session, sid, username, event, seconds_in_wait
FROM   v$session
WHERE  blocking_session IS NOT NULL;

-- STEP 3: Is temp or undo full?
SELECT tablespace_name, ROUND(used_blocks*8192/1024/1024,2) used_mb
FROM   v$temp_space_header;

-- STEP 4: What SQL is consuming most resources right now?
SELECT sql_id, elapsed_time, executions, cpu_time, buffer_gets
FROM   v$sql
WHERE  last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- STEP 5: Is the FRA full?
SELECT name, space_used/space_limit*100 pct_used
FROM   v$recovery_file_dest;

-- STEP 6: Any ORA- errors in alert log?
-- Check alert log: grep ORA- alert_ORCL.log | tail -50

15. Quick Reference Card

This SOP covers everything you need to diagnose and resolve Oracle Database performance issues without referring to any other source. Always start with wait event analysis before making any changes, always use AWR and ASH to identify root cause with evidence before tuning, never change parameters randomly without data to support the change, and always take an AWR snapshot before and after any tuning change so you can measure whether the change actually helped.