AWR Snapshot SQL Analysis (snap_tracker.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This comprehensive AWR analysis script:

• Analyzes SQL performance data from a specific AWR snapshot
• Generates detailed performance metrics per SQL statement
• Creates two output files: summary and detailed explain plans
• Focuses on high-impact SQL statements based on elapsed time
• Calculates per-execution metrics for better analysis

Script

--
-- $Header$
--
-- Program     : snap_tracker.sql
--
-- Description : generate Explain SQL reports using the ExplainSQL against AWR snapshot data
--
-- Location    : toolkit/dba/sql_tracker
--
-- Author      : Keith D. Allen - Allstate Insurance Company
--
-- Revise Date : 02-JUL-2007 kallc - Initial Creation
--               06-Jul-2007 kallc - Added additional cols for summary report
--
col sum_spool new_value sum_spool noprint;
col dtl_spool new_value dtl_spool noprint;
col snap_selected new_value snap_selected noprint;
col time_selected new_value time_selected noprint;
--
define elapsed_time=2.5
define executions=0
--
set feedback off
set termout on
set heading off
select ltrim(to_char(MAX (snap_id) - &snap_ago)) snap_selected from SYS.wrm$_snapshot;

set termout off

SELECT    'sqlt_'|| min(TO_CHAR (begin_interval_time, 'YYYYMMDD_HH24MISS_'))||TO_CHAR(snap_id)||'_sum.txt' sum_spool,
          'sqlt_'|| min(TO_CHAR (begin_interval_time, 'YYYYMMDD_HH24MISS_'))||TO_CHAR(snap_id)||'_dtl.txt' dtl_spool,
          min(TO_CHAR (begin_interval_time, 'dd-mon-yyyy hh24:mi:ss')) time_selected
  FROM SYS.wrm$_snapshot WHERE snap_id = &snap_selected group by snap_id;

set heading on
set termout on
prompt Summary Report will be saved to &sum_spool
prompt and Detail Report to &dtl_spool

set linesize 180
set heading off
set pagesize 0
set trimspool on
set termout off
set feedback off

spool snaptracker.run

SELECT 'select * from table(explainsql('||chr(39)||sql_id||chr(39)||'));' run
  FROM (SELECT a.sql_id,PARSING_SCHEMA_NAME, module,
               sum(a.executions_delta) executions,
               sum(a.parse_calls_delta) parses,
               sum(a.sorts_delta) sorts,
               sum(a.fetches_delta) fetches,
               sum(a.px_servers_execs_delta) px_servers,
               sum(a.rows_processed_delta) rows_processed,
               sum(a.disk_reads_delta) disk_reads,
               sum(a.buffer_gets_delta) buffer_gets,
               sum(a.direct_writes_delta) direct_writes,
               sum(a.elapsed_time_delta)/1000000 elapsed_time,
               sum(a.cpu_time_delta)/1000000 cpu_time,
               sum(a.iowait_delta)/1000000 io_wait,
               sum(a.clwait_delta)/1000000 cluster_wait,
               sum(a.apwait_delta)/1000000 app_wait,
               sum(a.ccwait_delta)/1000000 concurrency_wait,
               sum(a.plsexec_time_delta)/1000000 plsql_time,
               sum(a.javexec_time_delta)/1000000 java_time,
               decode(sum (a.parse_calls_delta),0,0,sum(a.parse_calls_delta)/sum(a.executions_delta)) parses_exec,
               decode(sum (a.sorts_delta),0,0,sum(a.sorts_delta)/sum(a.executions_delta)) sorts_exec,
               decode(sum (a.fetches_delta),0,0,sum(a.fetches_delta)/sum(a.executions_delta)) fetches_exec,
               decode(sum (a.px_servers_execs_delta),0,0,sum(a.px_servers_execs_delta)/sum(a.executions_delta)) px_servers_exec,
               decode(sum (a.rows_processed_delta),0,0,sum(a.rows_processed_delta)/sum(a.executions_delta)) rows_processed_exec,
               decode(sum (a.disk_reads_delta),0,0,sum(a.disk_reads_delta)/sum(a.executions_delta)) disk_reads_exec,
               decode(sum (a.buffer_gets_delta),0,0,sum(a.buffer_gets_delta)/sum(a.executions_delta)) buffer_gets_exec,
               decode(sum (a.direct_writes_delta),0,0,sum(a.direct_writes_delta)/sum(a.executions_delta)) direct_writes_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.elapsed_time_delta)/1000000)/sum(a.executions_delta)) elapsed_time_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.cpu_time_delta)/1000000)/sum(a.executions_delta)) cpu_time_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.iowait_delta)/1000000)/sum(a.executions_delta)) iowait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.clwait_delta)/1000000)/sum(a.executions_delta)) clwait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.apwait_delta)/1000000)/sum(a.executions_delta)) apwait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.ccwait_delta)/1000000)/sum(a.executions_delta)) ccwait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.plsexec_time_delta)/1000000)/sum(a.executions_delta)) plsexec_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.javexec_time_delta)/1000000)/sum(a.executions_delta)) javexec_exec
          FROM SYS.wrh$_sqlstat a
         WHERE snap_id = &snap_selected
           AND parsing_schema_name not in ('SYS','SYSTEM','DBSNMP','ORAMON','ORADBA')
           and parsing_schema_name LIKE '%USR%'
      GROUP BY a.parsing_schema_name,a.sql_id, a.module
      ORDER BY elapsed_time_exec DESC)
 WHERE elapsed_time_exec >= &elapsed_time
   and executions >= &executions
 ORDER BY elapsed_time_exec DESC;

spool off

set heading on
set pagesize 50
set linesize 235
set trimspool on
set termout off
set feedback off
col parsing_schema_name format a15 heading 'PARSING|SCHEMA'
col module format a50 heading 'MODULE' TRUNCATE
col executions format 999,999 heading 'TOTAL|EXEC'

col parses format 999,999 heading 'TOTAL|PARSE' NOPRINT
col sorts format 999,999 heading 'TOTAL|SORTS' NOPRINT
col fetches format 999,999 heading 'TOTAL|FETCH' NOPRINT
col px_servers format 9,999 heading 'TOTAL|PX' NOPRINT
col rows_processed format 999,999 heading 'TOTAL|ROWS'
col disk_reads format 999,999,999 heading 'TOTAL|DISK|READS'
col buffer_gets format 999,999,999 heading 'TOTAL|BUFFER|GETS'
col direct_writes format 999,999,999 heading 'TOTAL|DIRECT|WRITES' NOPRINT
col elapsed_time format 9,999.99 HEADING 'TOTAL|ELAPSED'
col cpu_time format 9,999.99 HEADING 'TOTAL|CPU'
col io_wait format 9,999.99 heading 'TOTAL|IO WAIT'
col cl_wait format 9,999.99 heading 'TOTAL|CL WAIT' NOPRINT
col ap_wait format 9,999.99 heading 'TOTAL|AP WAIT' NOPRINT
col cc_wait format 9,999.99 heading 'TOTAL|CC WAIT' NOPRINT
col plsql format 9,999.99 heading 'TOTAL|PL/SQL' NOPRINT
col java  format 9,999.99 heading 'TOTAL|JAVA' NOPRINT

col parses_exec format 999,999 heading 'PARSE|EXEC' NOPRINT
col sorts_exec format 999,999 heading 'SORTS|EXEC' NOPRINT
col fetches_exec format 999,999 heading 'FETCH|EXEC' NOPRINT
col px_servers_exec format 9,999 heading 'PX|EXEC' NOPRINT
col rows_processed_exec format 999,999 heading 'ROWS|EXEC'
col disk_reads_exec format 999,999,999 heading 'DISK|READS|EXEC'
col buffer_gets_exec format 999,999,999 heading 'BUFFER|GETS|EXEC'
col direct_writes_exec format 999,999,999 heading 'DIRECT|WRITES|EXEC' NOPRINT
col elapsed_time_exec format 9,999.99 HEADING 'ELAPSED|EXEC'
col cpu_time_exec format 9,999.99 HEADING 'CPU|EXEC'
col io_wait_exec format 9,999.99 heading 'IO WAIT|EXEC'
col cl_wait_exec format 9,999.99 heading 'CL WAIT|EXEC' NOPRINT
col ap_wait_exec format 9,999.99 heading 'AP WAIT|EXEC' NOPRINT
col cc_wait_exec format 9,999.99 heading 'CC WAIT|EXEC' NOPRINT
col plsqlexec format 9,999.99 heading 'PL/SQL|EXEC' NOPRINT
col javaexec  format 9,999.99 heading 'JAVA|EXEC' NOPRINT

ttitle 'Workload Repository SQL Summary for Snapshot &snap_selected - &time_selected, executions >= &executions and elapsed time >= &elapsed_time'

spool &sum_spool

SELECT *
  FROM (SELECT a.sql_id,PARSING_SCHEMA_NAME, module,
               sum(a.executions_delta) executions,
               sum(a.parse_calls_delta) parses,
               sum(a.sorts_delta) sorts,
               sum(a.fetches_delta) fetches,
               sum(a.px_servers_execs_delta) px_servers,
               sum(a.rows_processed_delta) rows_processed,
               sum(a.disk_reads_delta) disk_reads,
               sum(a.buffer_gets_delta) buffer_gets,
               sum(a.direct_writes_delta) direct_writes,
               sum(a.elapsed_time_delta)/1000000 elapsed_time,
               sum(a.cpu_time_delta)/1000000 cpu_time,
               sum(a.iowait_delta)/1000000 io_wait,
               sum(a.clwait_delta)/1000000 cl_wait,
               sum(a.apwait_delta)/1000000 ap_wait,
               sum(a.ccwait_delta)/1000000 cc_wait,
               sum(a.plsexec_time_delta)/1000000 plsql,
               sum(a.javexec_time_delta)/1000000 java,
               decode(sum (a.parse_calls_delta),0,0,sum(a.parse_calls_delta)/sum(a.executions_delta)) parses_exec,
               decode(sum (a.sorts_delta),0,0,sum(a.sorts_delta)/sum(a.executions_delta)) sorts_exec,
               decode(sum (a.fetches_delta),0,0,sum(a.fetches_delta)/sum(a.executions_delta)) fetches_exec,
               decode(sum (a.px_servers_execs_delta),0,0,sum(a.px_servers_execs_delta)/sum(a.executions_delta)) px_servers_exec,
               decode(sum (a.rows_processed_delta),0,0,sum(a.rows_processed_delta)/sum(a.executions_delta)) rows_processed_exec,
               decode(sum (a.disk_reads_delta),0,0,sum(a.disk_reads_delta)/sum(a.executions_delta)) disk_reads_exec,
               decode(sum (a.buffer_gets_delta),0,0,sum(a.buffer_gets_delta)/sum(a.executions_delta)) buffer_gets_exec,
               decode(sum (a.direct_writes_delta),0,0,sum(a.direct_writes_delta)/sum(a.executions_delta)) direct_writes_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.elapsed_time_delta)/1000000)/sum(a.executions_delta)) elapsed_time_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.cpu_time_delta)/1000000)/sum(a.executions_delta)) cpu_time_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.iowait_delta)/1000000)/sum(a.executions_delta)) io_wait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.clwait_delta)/1000000)/sum(a.executions_delta)) cl_wait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.apwait_delta)/1000000)/sum(a.executions_delta)) ap_wait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.ccwait_delta)/1000000)/sum(a.executions_delta)) cc_wait_exec,
               decode(sum (a.executions_delta),0,0,(sum(a.plsexec_time_delta)/1000000)/sum(a.executions_delta)) plsqlexec,
               decode(sum (a.executions_delta),0,0,(sum(a.javexec_time_delta)/1000000)/sum(a.executions_delta)) javaexec
          FROM SYS.wrh$_sqlstat a
         WHERE snap_id = &snap_selected
           AND parsing_schema_name not in ('SYS','SYSTEM','DBSNMP','ORAMON','ORADBA')
      GROUP BY a.parsing_schema_name,a.sql_id, a.module
      ORDER BY elapsed_time_exec DESC)
 WHERE elapsed_time_exec >= &elapsed_time
   and executions >= &executions
 ORDER BY elapsed_time_exec DESC;

spool off
--
set heading off
set pagesize 0
spool &dtl_spool
@snaptracker.run
spool off

col sum_spool new_value
col dtl_spool new_value
col snap_selected new_value

undefine dtl_spool
undefine sum_spool
undefine snap_selected
undefine snap_ago
undefine executions
undefine elapsed_time
set feedback on
set termout on

Usage

SQL> define snap_ago=1
SQL> @snap_tracker.sql

Parameters

• snap_ago: Number of snapshots back from latest (default: requires manual definition)
• elapsed_time: Minimum elapsed time per execution threshold (default: 2.5 seconds)
• executions: Minimum execution count threshold (default: 0)

Required Privileges

• SELECT on SYS.WRM$_SNAPSHOT
• SELECT on SYS.WRH$_SQLSTAT
• SELECT on DBA_HIST_SNAPSHOT
• Access to AWR repository

Output Files

Summary Report (sqlt_YYYYMMDD_HHMMSS_SNAPID_sum.txt)

Contains comprehensive SQL performance summary with metrics like:

• Total and per-execution statistics
• CPU, I/O, and wait time analysis
• Resource consumption patterns

Detail Report (sqlt_YYYYMMDD_HHMMSS_SNAPID_dtl.txt)

Contains execution plans for each SQL statement using EXPLAINSQL function.

Key Metrics Analyzed

Execution Statistics

• EXECUTIONS: Total number of executions
• PARSES: Parse call count
• SORTS: Sort operations
• FETCHES: Fetch operations
• ROWS_PROCESSED: Total rows affected

Resource Consumption

• DISK_READS: Physical I/O operations
• BUFFER_GETS: Logical I/O operations
• DIRECT_WRITES: Direct path writes
• PX_SERVERS: Parallel execution usage

Time Analysis

• ELAPSED_TIME: Total elapsed time
• CPU_TIME: CPU consumption
• IO_WAIT: I/O wait time
• CLUSTER_WAIT: RAC cluster wait time
• APP_WAIT: Application wait time
• CONCURRENCY_WAIT: Concurrency-related waits

Per-Execution Metrics

All totals are also calculated per execution for better comparison.

Common Use Cases

1. Performance Baseline Analysis

   • Establish performance baselines
   • Compare snapshots over time
   • Identify performance degradation

2. SQL Tuning Identification

   • Find highest-impact SQL statements
   • Prioritize tuning efforts
   • Analyze execution plan changes

3. Workload Characterization

   • Understand application workload patterns
   • Identify resource-intensive operations
   • Plan capacity requirements

Customization

Schema Filtering

The script filters specific schemas:

-- Modify this line to change schema filtering
and parsing_schema_name LIKE '%USR%'

Performance Thresholds

Adjust thresholds in the script:

define elapsed_time=5.0    -- Increase for fewer results
define executions=10       -- Minimum execution count

Snapshot Selection

-- Analyze specific snapshot
define snap_ago=0         -- Latest snapshot
define snap_ago=24        -- 24 snapshots back (roughly 12 hours)

Troubleshooting

No Results

• Check AWR retention period
• Verify snapshot exists for specified time
• Adjust filtering thresholds

Performance Issues

• Script can be resource-intensive
• Run during low-activity periods
• Consider limiting time range

Missing EXPLAINSQL Function

• Requires custom function or package
• May need to modify detail report generation
• Can skip detail report if function unavailable

Best Practices

1. Regular Analysis

   • Run weekly for trending
   • Compare peak vs. off-peak periods
   • Archive results for historical analysis

2. Threshold Tuning

   • Adjust thresholds based on environment
   • Focus on statements that matter
   • Balance between detail and relevance

3. Integration

   • Incorporate into monitoring procedures
   • Automate for regular reporting
   • Use results to drive tuning priorities

Related Scripts

• AWR Report Generation
• Top SQL Finder
• Last Snapshot Finder