SQL Statement History Analysis (sphistory.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 sophisticated script provides comprehensive SQL statement performance history analysis by:

• Tracking historical performance trends for a specific SQL statement using Statspack data
• Displaying execution plan changes over time with detailed plan information
• Calculating incremental performance metrics between snapshots
• Generating detailed reports showing performance degradation or improvement patterns
• Supporting multi-instance RAC environments with instance-specific analysis

Script

 *********************************************************************/
set echo off
set feedback off timing off verify off linesize 100 pagesize 130 recsep off
set serveroutput on size 1000000 format wrapped trimout on trimspool on
col snap_time format a12 truncate heading "Snapshot|Time"
col execs format 999,990 heading "Execs"
col lio_per_exec format 999,999,999,990.00 heading "Avg LIO|Per Exec"
col pio_per_exec format 999,999,999,990.00 heading "Avg PIO|Per Exec"
col cpu_per_exec format 999,999,999,990.00 heading "Avg|CPU (secs)|Per Exec"
col ela_per_exec format 999,999,999,990.00 heading "Avg|Elapsed (secs)|Per Exec"
col sql_text format a64 heading "Text of SQL statement"
clear breaks computes
ttitle off
btitle off

accept V_HASH_VALUE prompt "Enter the SQL statement hash value: "
accept V_ORACLE_SID prompt "Enter the SID of the Oracle instance (wildcard chars permitted): "

spool sphistory_&&V_HASH_VALUE

select  sql_text
from  stats$sqltext
where  hash_value = &&V_HASH_VALUE
order by text_subset, piece;

set linesize 0 heading off
select  decode(count(*),0,'CSTATS$PLAN_TABLE DOES NOT EXIST -- PLEASE RUN SCRIPT "top_stmt4_9i.sql" TO CREATE IT', '') message
from  user_tables
where  table_name = 'CSTATS$PLAN_TABLE';
set linesize 130 heading on

declare
  cursor get_plan_hash_value(in_hash_value in number)
  is
  select  pu.plan_hash_value,
    ss.snap_time,
    ss.snap_id
  from  stats$sql_plan_usage  pu,
    stats$snapshot    ss
  where  pu.dbid = ss.dbid
  and  pu.instance_number = ss.instance_number
  and  pu.snap_id = ss.snap_id
  and  pu.hash_value = in_hash_value
  order by ss.snap_time;
        --
  cursor get_xplan(in_plan_hv in number)
  is
  select  plan_table_output
  from  table(dbms_xplan.display('CSTATS$PLAN_TABLE', trim(to_char(in_plan_hv)), 'ALL'));
  --
  v_prev_plan_hash_value  number := -1;
  v_text_lines    number := 0;
  v_errcontext    varchar2(100);
  v_errmsg    varchar2(100);
begin
  --
  v_errcontext := 'open/fetch get_plan_hash_value';
  for phv in get_plan_hash_value(&&V_HASH_VALUE) loop
    --
    if v_prev_plan_hash_value <> phv.plan_hash_value then
      --
      v_prev_plan_hash_value := phv.plan_hash_value;
      --
      v_errcontext := 'insert into cstats$plan_table';
      insert into cstats$plan_table
      (  STATEMENT_ID,
        TIMESTAMP,
        REMARKS,
        OPERATION,
        OPTIONS,
        OBJECT_NODE,
        OBJECT_OWNER,
        OBJECT_NAME,
        OBJECT_INSTANCE,
        OBJECT_TYPE,
        OPTIMIZER,
        SEARCH_COLUMNS,
        ID,
        PARENT_ID,
        POSITION,
        COST,
        CARDINALITY,
        BYTES,
        OTHER_TAG,
        PARTITION_START,
        PARTITION_STOP,
        PARTITION_ID,
        OTHER,
        DISTRIBUTION,
        CPU_COST,
        IO_COST,
        TEMP_SPACE,
        ACCESS_PREDICATES,
        FILTER_PREDICATES)
      select  trim(to_char(p.PLAN_HASH_VALUE)),
        SYSDATE,
        'hash_value = '''||p.PLAN_HASH_VALUE||''' from STATS$SQL_PLAN',
        p.OPERATION,
        p.OPTIONS,
        p.OBJECT_NODE,
        p.OBJECT_OWNER,
        p.OBJECT_NAME,
        p.OBJECT#,
        o.OBJECT_TYPE,
        p.OPTIMIZER,
        p.SEARCH_COLUMNS,
        p.ID,
        p.PARENT_ID,
        p.POSITION,
        p.COST,
        p.CARDINALITY,
        p.BYTES,
        p.OTHER_TAG,
        p.PARTITION_START,
        p.PARTITION_STOP,
        p.PARTITION_ID,
        p.OTHER,
        p.DISTRIBUTION,
        p.CPU_COST,
        p.IO_COST,
        p.TEMP_SPACE,
        p.ACCESS_PREDICATES,
        p.FILTER_PREDICATES
      from  stats$sql_plan    p,
        stats$seg_stat_obj  o
      where  p.plan_hash_value = phv.plan_hash_value
      and  o.obj# (+) = p.object#;
      --
      v_text_lines := 0;
      v_errcontext := 'open/fetch get_xplan';
      for s in get_xplan(phv.plan_hash_value) loop
        --
        if s.plan_table_output like 'Predicate Information %' then
          exit;
        end if;
        --
        if v_text_lines = 0 then
          dbms_output.put_line('.');
          dbms_output.put_line('.  SQL execution plan from "'||
            to_char(phv.snap_time,'MM/DD/YY HH24:MI:SS') ||
            '" (snap #'||phv.snap_id||')');
        end if;
        --
        dbms_output.put_line(s.plan_table_output);
        v_text_lines := v_text_lines + 1;
        --
      end loop;
      --
      v_errcontext := 'delete from cstats$plan_table';
      delete
      from  cstats$plan_table
      where  statement_id = trim(to_char(phv.plan_hash_value));
      --
    end if;
    --
  end loop;
  --
exception
  when others then
    v_errmsg := sqlerrm;
    raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);
end;
/

select  to_char(s.snap_time, 'DD-MON HH24:MI') snap_time,
  ss.executions_inc execs,
  ss.buffer_gets_inc/decode(ss.executions_inc,0,1,ss.executions_inc) lio_per_exec,
  ss.disk_reads_inc/decode(ss.executions_inc,0,1,ss.executions_inc) pio_per_exec,
  ss.cpu_time_inc/decode(ss.executions_inc,0,1,ss.executions_inc) cpu_per_exec,
  ss.elapsed_time_inc/decode(ss.executions_inc,0,1,ss.executions_inc) ela_per_exec
from   stats$snapshot            s,
  (select  ss2.dbid,
    ss2.snap_id,
    ss2.instance_number,
    nvl(decode(greatest(ss2.executions, nvl(lag(ss2.executions) over (order by ss2.snap_id),0)),
         ss2.executions, ss2.executions - lag(ss2.executions) over (order by ss2.snap_id),
        ss2.executions), 0) executions_inc,
    nvl(decode(greatest(ss2.buffer_gets, nvl(lag(ss2.buffer_gets) over (order by ss2.snap_id),0)),
         ss2.buffer_gets, ss2.buffer_gets - lag(ss2.buffer_gets) over (order by ss2.snap_id),
        ss2.buffer_gets), 0) buffer_gets_inc,
    nvl(decode(greatest(ss2.disk_reads, nvl(lag(ss2.disk_reads) over (order by ss2.snap_id),0)),
         ss2.disk_reads, ss2.disk_reads - lag(ss2.disk_reads) over (order by ss2.snap_id),
        ss2.disk_reads), 0) disk_reads_inc,
    nvl(decode(greatest(ss2.cpu_time, nvl(lag(ss2.cpu_time) over (order by ss2.snap_id),0)),
         ss2.cpu_time, ss2.cpu_time - lag(ss2.cpu_time) over (order by ss2.snap_id),
        ss2.cpu_time), 0)/1000000 cpu_time_inc,
    nvl(decode(greatest(ss2.elapsed_time, nvl(lag(ss2.elapsed_time) over (order by ss2.snap_id),0)),
         ss2.elapsed_time, ss2.elapsed_time - lag(ss2.elapsed_time) over (order by ss2.snap_id),
        ss2.elapsed_time), 0)/1000000 elapsed_time_inc
   from  stats$sql_summary        ss2,
    (select distinct  dbid,
          instance_number
     from  stats$database_instance
     where  instance_name like '&&V_ORACLE_SID')  i
   where  ss2.hash_value = &&V_HASH_VALUE
   and  ss2.dbid = i.dbid
   and  ss2.instance_number = i.instance_number)  ss
where  s.snap_id = ss.snap_id
and  s.dbid = ss.dbid
and  s.instance_number = ss.instance_number
order by s.snap_time asc;

spool off
set verify on echo on feedback on

Usage

SQL> @sphistory.sql
Enter the SQL statement hash value: 1234567890
Enter the SID of the Oracle instance (wildcard chars permitted): PROD%

Parameters

• V_HASH_VALUE: Hash value of the SQL statement to analyze
• V_ORACLE_SID: Oracle instance name pattern (supports wildcards like %)

Prerequisites

• Statspack installed and configured
• CSTATS$PLAN_TABLE table must exist (created by top_stmt4_9i.sql)
• Historical Statspack snapshots available for analysis period
• SELECT privileges on STATS$ views

Required Privileges

• SELECT on STATS$SQLTEXT
• SELECT on STATS$SQL_PLAN_USAGE
• SELECT on STATS$SNAPSHOT
• SELECT on STATS$SQL_SUMMARY
• SELECT on STATS$SQL_PLAN
• SELECT on STATS$SEG_STAT_OBJ
• INSERT/DELETE on CSTATS$PLAN_TABLE

Sample Output

SQL Text Display

Text of SQL statement
----------------------------------------------------------------
SELECT c.customer_id, c.customer_name, o.order_date, o.total_amount
FROM customers c, orders o
WHERE c.customer_id = o.customer_id
AND o.order_date >= :1

Execution Plan History

.
.  SQL execution plan from "03/15/24 14:30:25" (snap #1245)

Plan hash value: 2084293868

------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   100 |  8600 |    45   (3)|
|   1 |  NESTED LOOPS                 |              |   100 |  8600 |    45   (3)|
|   2 |   TABLE ACCESS BY INDEX ROWID | ORDERS       |    50 |  2150 |    23   (0)|
|*  3 |    INDEX RANGE SCAN           | IX_ORD_DATE  |    50 |       |     2   (0)|
|   4 |   TABLE ACCESS BY INDEX ROWID | CUSTOMERS    |     2 |   172 |     1   (0)|
|*  5 |    INDEX UNIQUE SCAN          | PK_CUSTOMERS |     2 |       |     0   (0)|
------------------------------------------------------------------------------------

Performance History

Snapshot    Execs    Avg LIO      Avg PIO      Avg CPU (secs)   Avg Elapsed (secs)
Time                 Per Exec     Per Exec     Per Exec         Per Exec
---------- ------- ------------ ------------ ---------------- ------------------
15-MAR 09:00    45        125.5         12.3             0.05               0.08
15-MAR 10:00    67        134.2         15.1             0.06               0.09
15-MAR 11:00    89        142.8         18.7             0.07               0.12
15-MAR 12:00   156        156.3         22.4             0.08               0.15
15-MAR 13:00   234        189.7         35.6             0.12               0.23

Key Output Components

1. SQL Text Section

• Complete SQL statement broken into manageable pieces
• Original formatting preserved for readability

2. Execution Plan Analysis

• Plan hash values showing when plans changed
• Detailed execution plans with costs and cardinality estimates
• Plan change timestamps for correlation with performance changes
• Object access methods and join strategies

3. Performance History Table

• Snapshot Time: When performance was measured
• Execs: Number of executions in the interval
• Avg LIO Per Exec: Average logical I/O per execution
• Avg PIO Per Exec: Average physical I/O per execution
• Avg CPU Per Exec: Average CPU time per execution (seconds)
• Avg Elapsed Per Exec: Average elapsed time per execution (seconds)

Analysis Techniques

Performance Trend Analysis

• Increasing CPU/Elapsed Time: Performance degradation
• Increasing I/O Per Execution: Possible plan regression
• Execution Count Spikes: Application load changes
• Plan Hash Changes: Optimizer plan instability

Plan Stability Assessment

-- Find SQL statements with multiple plans
SELECT hash_value, COUNT(DISTINCT plan_hash_value) plan_count
FROM stats$sql_plan_usage
GROUP BY hash_value
HAVING COUNT(DISTINCT plan_hash_value) > 1
ORDER BY plan_count DESC;

Performance Correlation

• CPU vs Elapsed Time: Identify wait-bound vs CPU-bound periods
• I/O Trends: Compare logical vs physical I/O growth
• Execution Patterns: Peak vs off-peak performance differences

Common Use Cases

1. SQL Performance Regression Analysis

   • Identify when performance degraded
   • Correlate with plan changes
   • Track optimization efforts

2. Optimizer Plan Stability

   • Monitor for unwanted plan changes
   • Validate optimizer statistics refresh impact
   • Plan baseline creation decisions

3. Capacity Planning

   • Track resource consumption growth
   • Identify scaling bottlenecks
   • Plan hardware upgrades

4. Application Performance Tuning

   • Isolate problematic time periods
   • Correlate with application releases
   • Prioritize tuning efforts

Advanced Analysis

Finding Hash Values

-- Find hash values for problematic SQL
SELECT hash_value, sql_text, executions, buffer_gets
FROM stats$sql_summary s, stats$sqltext t
WHERE s.hash_value = t.hash_value
AND s.snap_id = (SELECT MAX(snap_id) FROM stats$snapshot)
ORDER BY buffer_gets DESC;

Cross-Instance Analysis

-- Compare performance across instances
SELECT instance_name,
       AVG(buffer_gets/executions) avg_lio,
       AVG(disk_reads/executions) avg_pio
FROM stats$sql_summary s, stats$database_instance i
WHERE s.dbid = i.dbid
AND s.instance_number = i.instance_number
AND s.hash_value = &hash_value
GROUP BY instance_name;

Troubleshooting

Missing CSTATS$PLAN_TABLE

-- Create the required table
CREATE TABLE cstats$plan_table AS
SELECT * FROM plan_table WHERE 1=2;

No Execution Plans Displayed

• Verify STATS$SQL_PLAN_USAGE has data
• Check if SQL was executed during plan capture periods
• Ensure sufficient Statspack retention

Performance Data Inconsistencies

• Check for Statspack snapshot gaps
• Verify instance restarts during analysis period
• Confirm consistent snapshot intervals

Integration with Modern Tools

Migration to AWR

This script concept can be adapted for AWR:

-- AWR equivalent query structure
SELECT snap_id, executions_delta, buffer_gets_delta
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id'
ORDER BY snap_id;

SQL Plan Management

Use insights from this analysis to:

• Create SQL plan baselines for stable plans
• Identify candidates for SQL profiles
• Monitor plan regression after changes

Best Practices

1. Regular Monitoring

   • Run for critical SQL statements weekly
   • Establish performance baselines
   • Track trends over time

2. Analysis Correlation

   • Correlate with system load changes
   • Check for statistics refresh impacts
   • Review alongside application changes

3. Documentation

   • Save reports for historical reference
   • Document plan changes and their causes
   • Maintain performance baseline records

Related Scripts

• AWR SQL Analysis
• SQL Plan Analysis
• SQL Performance Tracking