scripts/collector/oracle/sql/extracts/statspack/awrhistcmdtypes.sql [38:161]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
FROM
(
select snap_id, dbid, instance_number, text_subset, old_hash_value, command_type, force_matching_signature, sql_id,
s.executions,
   NVL(
       DECODE(
              GREATEST(executions, NVL( LAG(executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              executions,
              executions - LAG(executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_executions,
      px_servers_executions,
      NVL(
       DECODE(
              GREATEST(px_servers_executions, NVL( LAG(px_servers_executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              px_servers_executions,
              px_servers_executions - LAG(px_servers_executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_px_servers_executions,
      elapsed_time,
      NVL(
       DECODE(
              GREATEST(elapsed_time, NVL( LAG(elapsed_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              elapsed_time,
              elapsed_time - LAG(elapsed_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_elapsed_time,
      disk_reads,
            NVL(
       DECODE(
              GREATEST(disk_reads, NVL( LAG(disk_reads) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              disk_reads,
              disk_reads - LAG(disk_reads) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_disk_reads,
      direct_writes,
            NVL(
       DECODE(
              GREATEST(direct_writes, NVL( LAG(direct_writes) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              direct_writes,
              direct_writes - LAG(direct_writes) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_direct_writes,
      end_of_fetch_count,
            NVL(
       DECODE(
              GREATEST(end_of_fetch_count, NVL( LAG(end_of_fetch_count) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              end_of_fetch_count,
              end_of_fetch_count - LAG(end_of_fetch_count) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_end_of_fetch_count,
      rows_processed,
            NVL(
       DECODE(
              GREATEST(rows_processed, NVL( LAG(rows_processed) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              rows_processed,
              rows_processed - LAG(rows_processed) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_rows_processed,
      buffer_gets,
            NVL(
       DECODE(
              GREATEST(buffer_gets, NVL( LAG(buffer_gets) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              buffer_gets,
              buffer_gets - LAG(buffer_gets) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_buffer_gets,
      cpu_time,
            NVL(
       DECODE(
              GREATEST(cpu_time, NVL( LAG(cpu_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              cpu_time,
              cpu_time - LAG(cpu_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_cpu_time,
      user_io_wait_time,
            NVL(
       DECODE(
              GREATEST(user_io_wait_time, NVL( LAG(user_io_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              user_io_wait_time,
              user_io_wait_time - LAG(user_io_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_user_io_wait_time,
      cluster_wait_time,
            NVL(
       DECODE(
              GREATEST(cluster_wait_time, NVL( LAG(cluster_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              cluster_wait_time,
              cluster_wait_time - LAG(cluster_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_cluster_wait_time,
      application_wait_time,
            NVL(
       DECODE(
              GREATEST(application_wait_time, NVL( LAG(application_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              application_wait_time,
              application_wait_time - LAG(application_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_application_wait_time,
      concurrency_wait_time,
            NVL(
       DECODE(
              GREATEST(concurrency_wait_time, NVL( LAG(concurrency_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              concurrency_wait_time,
              concurrency_wait_time - LAG(concurrency_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_concurrency_wait_time,
      plsql_exec_time,
            NVL(
       DECODE(
              GREATEST(plsql_exec_time, NVL( LAG(plsql_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              plsql_exec_time,
              plsql_exec_time - LAG(plsql_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_plsql_exec_time,
      java_exec_time,
            NVL(
       DECODE(
              GREATEST(java_exec_time, NVL( LAG(java_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              java_exec_time,
              java_exec_time - LAG(java_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_java_exec_time
From STATS$SQL_SUMMARY s
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



scripts/collector/oracle/sql/extracts/statspack/sqlstats.sql [59:182]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
FROM
(
select snap_id, dbid, instance_number, text_subset, old_hash_value, command_type, force_matching_signature, sql_id,
s.executions,
   NVL(
       DECODE(
              GREATEST(executions, NVL( LAG(executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              executions,
              executions - LAG(executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_executions,
      px_servers_executions,
      NVL(
       DECODE(
              GREATEST(px_servers_executions, NVL( LAG(px_servers_executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              px_servers_executions,
              px_servers_executions - LAG(px_servers_executions) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_px_servers_executions,
      elapsed_time,
      NVL(
       DECODE(
              GREATEST(elapsed_time, NVL( LAG(elapsed_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              elapsed_time,
              elapsed_time - LAG(elapsed_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_elapsed_time,
      disk_reads,
            NVL(
       DECODE(
              GREATEST(disk_reads, NVL( LAG(disk_reads) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              disk_reads,
              disk_reads - LAG(disk_reads) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_disk_reads,
      direct_writes,
            NVL(
       DECODE(
              GREATEST(direct_writes, NVL( LAG(direct_writes) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              direct_writes,
              direct_writes - LAG(direct_writes) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_direct_writes,
      end_of_fetch_count,
            NVL(
       DECODE(
              GREATEST(end_of_fetch_count, NVL( LAG(end_of_fetch_count) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              end_of_fetch_count,
              end_of_fetch_count - LAG(end_of_fetch_count) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_end_of_fetch_count,
      rows_processed,
            NVL(
       DECODE(
              GREATEST(rows_processed, NVL( LAG(rows_processed) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              rows_processed,
              rows_processed - LAG(rows_processed) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_rows_processed,
      buffer_gets,
            NVL(
       DECODE(
              GREATEST(buffer_gets, NVL( LAG(buffer_gets) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              buffer_gets,
              buffer_gets - LAG(buffer_gets) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_buffer_gets,
      cpu_time,
            NVL(
       DECODE(
              GREATEST(cpu_time, NVL( LAG(cpu_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              cpu_time,
              cpu_time - LAG(cpu_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_cpu_time,
      user_io_wait_time,
            NVL(
       DECODE(
              GREATEST(user_io_wait_time, NVL( LAG(user_io_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              user_io_wait_time,
              user_io_wait_time - LAG(user_io_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_user_io_wait_time,
      cluster_wait_time,
            NVL(
       DECODE(
              GREATEST(cluster_wait_time, NVL( LAG(cluster_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              cluster_wait_time,
              cluster_wait_time - LAG(cluster_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_cluster_wait_time,
      application_wait_time,
            NVL(
       DECODE(
              GREATEST(application_wait_time, NVL( LAG(application_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              application_wait_time,
              application_wait_time - LAG(application_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_application_wait_time,
      concurrency_wait_time,
            NVL(
       DECODE(
              GREATEST(concurrency_wait_time, NVL( LAG(concurrency_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              concurrency_wait_time,
              concurrency_wait_time - LAG(concurrency_wait_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_concurrency_wait_time,
      plsql_exec_time,
            NVL(
       DECODE(
              GREATEST(plsql_exec_time, NVL( LAG(plsql_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              plsql_exec_time,
              plsql_exec_time - LAG(plsql_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_plsql_exec_time,
      java_exec_time,
            NVL(
       DECODE(
              GREATEST(java_exec_time, NVL( LAG(java_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id), 0)),
              java_exec_time,
              java_exec_time - LAG(java_exec_time) OVER ( PARTITION BY s.dbid, s.instance_number, text_subset, old_hash_value, command_type, force_matching_signature ORDER BY s.snap_id),
             0),
      0) AS delta_java_exec_time
From STATS$SQL_SUMMARY s
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



