Oracle

What is it?

The AWR (Automatic Workload Repository) report is the most comprehensive tool for analysing Oracle performance. It captures system statistics over a time period and presents them in a detailed report. Requires Oracle Diagnostic Pack licence.

When to generate it?

• When there are performance issues in production
• For weekly or monthly trend analysis
• Before and after significant database changes
• When the system is slow during a specific period

How to generate it (SQL*Plus):

-- View available snapshots
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 20 ROWS ONLY;

-- Generate AWR report (HTML recommended)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- Select: html
-- Enter start and end snap_id
-- The file is generated in the current directory

ASH Report

What is it?

The ASH (Active Session History) report analyses active session activity over a specific period. It is ideal for diagnosing point-in-time issues or short-duration slowdowns. Requires Oracle Diagnostic Pack licence.

When to generate it?

• When there are sudden performance spikes
• To analyse what the database was doing at an exact moment
• As a complement to AWR for more granular analysis
• When the problem lasted less than 30 minutes

How to generate it:

-- Generate ASH report
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
-- Select: html
-- Enter start date/time and duration in minutes

What is it?

Statspack is the free alternative to AWR for Oracle databases without a Diagnostic Pack licence. It provides similar information with less detail. No additional licence required.

When to use it?

• When you do not have an Oracle Diagnostic Pack licence
• In Standard Edition environments
• As a cost-effective alternative for performance analysis

Installation (first time only):

-- As SYSDBA
@$ORACLE_HOME/rdbms/admin/spcreate.sql
-- Create PERFSTAT user with a password

Take a manual snapshot:

CONNECT perfstat/password
EXECUTE statspack.snap;

Generate report:

@$ORACLE_HOME/rdbms/admin/spreport.sql
-- Select start and end snap_id

Alert Log

What is it?

The Alert Log is Oracle's event journal. It records errors, startups, shutdowns, configuration changes and critical database events.

When to analyse it?

• When ORA- errors appear in the application
• After unexpected database crashes or restarts
• To detect block corruptions
• As first evidence in any investigation

Location:

# Oracle 11g and later (ADR)
$ORACLE_BASE/diag/rdbms/{db_name}/{instance}/trace/alert_{instance}.log

# Query location from SQL*Plus
SELECT value FROM v$parameter WHERE name = 'background_dump_dest';

-- Oracle 12c+:
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Trace Files

What is it?

Trace files (.trc) contain detailed information about specific sessions, errors and Oracle dumps. They are especially useful for specific ORA- errors.

Location and generation:

-- Trace directory
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- Enable trace for the current session
ALTER SESSION SET sql_trace = TRUE;
-- Execute the problematic operation
ALTER SESSION SET sql_trace = FALSE;
-- The trace file is created in the directory above

Other useful files

Export active sessions (v$session):

SELECT sid, serial#, username, status, sql_id, event,
       wait_class, seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE'
  AND username IS NOT NULL;

Execution plan for a problematic query:

EXPLAIN PLAN FOR
SELECT ... -- your query here
;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());