cs_high_execution_rate_rps.sql

----------------------------------------------------------------------------------------
--
-- File name:   cs_high_execution_rate_rps.sql
--
-- Purpose:     List executions by time for a given SQL_ID with high RPS 
--
-- Author:      Carlos Sierra
--
-- Version:     2023/04/27
--
-- Usage:       Execute connected to PDB.
--
--              Enter SQL_ID when requested.
--
-- Example:     $ sqlplus / as sysdba
--              SQL> @cs_high_execution_rate_rps.sql
--
-- Notes:       Developed and tested on 19c
--             
---------------------------------------------------------------------------------------
--
@@cs_internal/cs_primary.sql
@@cs_internal/cs_cdb_warn.sql
@@cs_internal/cs_set.sql
@@cs_internal/cs_def.sql
@@cs_internal/cs_file_prefix.sql
--
DEF cs_script_name = 'cs_high_execution_rate_rps';
--
PRO 1. SQL_ID: 
DEF cs_sql_id = '&1.';
UNDEF 1;
--
PRO
PRO 2. Seconds: [{1}|1-60]
DEF cs_seconds = '&2.';
UNDEF 2;
COL cs_seconds NEW_V cs_seconds NOPRI;
SELECT CASE WHEN TO_NUMBER('&&cs_seconds.') BETWEEN 1 AND 60 THEN '&&cs_seconds.' ELSE '1' END AS cs_seconds FROM DUAL
/
--
@@cs_internal/cs_last_snap.sql
--
SELECT '&&cs_file_prefix._&&cs_script_name._&&cs_sql_id.' cs_file_name FROM DUAL;
--
@@cs_internal/cs_signature.sql
@@cs_internal/cs_spool_head.sql
PRO SQL> @&&cs_script_name..sql "&&cs_sql_id." "&&cs_seconds."
@@cs_internal/cs_spool_id.sql
@@cs_internal/cs_spool_id_list_sql_id.sql
--
PRO SECONDS      : &&cs_seconds.
--
@@cs_internal/cs_print_sql_text.sql
PRO
PRO Samples (v$sqlstats)
PRO ~~~~~~~ 
SET SERVEROUT ON;
DECLARE
  l_begin_timestamp TIMESTAMP(6) := SYSTIMESTAMP;
  l_exit_timestamp TIMESTAMP(6) := l_begin_timestamp + INTERVAL '&&cs_seconds.' SECOND;
  l_timestamp TIMESTAMP(6);
  l_parse_calls NUMBER;
  l_executions NUMBER;
  l_rows_processed NUMBER;
  l_us_delta NUMBER;
  l_parse_calls_total NUMBER := 0;
  l_executons_total NUMBER := 0;
  l_rows_processed_total NUMBER := 0;
  l_us_total NUMBER := 0;
  l_parse_calls_delta NUMBER;
  l_executions_delta NUMBER;
  l_rows_processed_delta NUMBER;
  l_timestamp_prior TIMESTAMP(6) := l_begin_timestamp;
  l_parse_calls_prior NUMBER;
  l_executions_prior NUMBER;
  l_rows_processed_prior NUMBER;
  l_timestamp_zero_begin TIMESTAMP(6) := l_begin_timestamp;
  l_timestamp_zero_end TIMESTAMP(6);
  l_us_delta_zero NUMBER;
  l_samples_zero NUMBER := 0;
  l_samples_total NUMBER := 0;
BEGIN
    WHILE SYSTIMESTAMP < l_exit_timestamp 
    LOOP
        SELECT parse_calls, executions, rows_processed INTO l_parse_calls, l_executions, l_rows_processed FROM v$sqlstats WHERE sql_id = '&&cs_sql_id.';
        l_timestamp := SYSTIMESTAMP;
        l_us_delta := ((86400 * EXTRACT(DAY FROM (l_timestamp - l_timestamp_prior)) + (3600 * EXTRACT(HOUR FROM (l_timestamp - l_timestamp_prior))) + (60 * EXTRACT(MINUTE FROM (l_timestamp - l_timestamp_prior))) + EXTRACT(SECOND FROM (l_timestamp - l_timestamp_prior)))) * 1e6;
        l_parse_calls_delta := l_parse_calls - l_parse_calls_prior;
        l_executions_delta := l_executions - l_executions_prior;
        l_rows_processed_delta := l_rows_processed - l_rows_processed_prior;
        l_samples_total := l_samples_total + 1;
        IF l_us_delta > 0 THEN
            l_us_total := l_us_total + l_us_delta;
            l_parse_calls_total := NVL(l_parse_calls_total, 0) + l_parse_calls_delta;
            l_executons_total := NVL(l_executons_total, 0) + l_executions_delta;
            l_rows_processed_total := NVL(l_rows_processed_total, 0) + l_rows_processed_delta;
        END IF;
        --
        IF l_parse_calls_delta > 0 OR l_executions_delta > 0 OR l_rows_processed_delta > 0 THEN
            IF l_timestamp_zero_begin IS NOT NULL AND l_timestamp_zero_end IS NOT NULL THEN
                l_us_delta_zero := ((86400 * EXTRACT(DAY FROM (l_timestamp_zero_end - l_timestamp_zero_begin)) + (3600 * EXTRACT(HOUR FROM (l_timestamp_zero_end - l_timestamp_zero_begin))) + (60 * EXTRACT(MINUTE FROM (l_timestamp_zero_end - l_timestamp_zero_begin))) + EXTRACT(SECOND FROM (l_timestamp_zero_end - l_timestamp_zero_begin)))) * 1e6;
                DBMS_OUTPUT.put_line (
                    RPAD(TO_CHAR(l_timestamp_zero_begin, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'), 26, ' ')||' - '||
                    RPAD(TO_CHAR(l_timestamp_zero_end, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'), 26, ' ')||
                    LPAD(TO_CHAR(l_us_delta_zero, '999,999,990'), 12, ' ')||' us'||
                    LPAD(TO_CHAR(0, '999,990'), 8, ' ')||' parses'||
                    LPAD(TO_CHAR(0, '999,990'), 8, ' ')||' executions'||
                    LPAD(TO_CHAR(0, '999,999,990'), 12, ' ')||' rows'||
                    LPAD(TO_CHAR(l_samples_zero, '9,999,990'), 10, ' ')||' samples'
                );
            END IF;
            --
            IF l_timestamp_prior IS NOT NULL AND l_timestamp IS NOT NULL THEN
                DBMS_OUTPUT.put_line (
                    RPAD(TO_CHAR(l_timestamp_prior, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'), 26, ' ')||' - '||
                    RPAD(TO_CHAR(l_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'), 26, ' ')||
                    LPAD(TO_CHAR(l_us_delta, '999,999,990'), 12, ' ')||' us'||
                    LPAD(TO_CHAR(l_parse_calls_delta, '999,990'), 8, ' ')||' parses'||
                    LPAD(TO_CHAR(l_executions_delta, '999,990'), 8, ' ')||' executions'||
                    LPAD(TO_CHAR(l_rows_processed_delta, '999,999,990'), 12, ' ')||' rows'
                );
            END IF;
            l_timestamp_zero_begin := l_timestamp;
            l_timestamp_zero_end := NULL;
            l_samples_zero := 0;
        ELSE
            l_timestamp_zero_end := l_timestamp;
            l_samples_zero := NVL(l_samples_zero, 0) + 1;
        END IF;
        --
        l_timestamp_prior := l_timestamp;
        l_parse_calls_prior := l_parse_calls;
        l_executions_prior := l_executions;
        l_rows_processed_prior := l_rows_processed;
    END LOOP;
    --
    DBMS_OUTPUT.put_line('---');
    DBMS_OUTPUT.put_line (
        RPAD(TO_CHAR(l_begin_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'), 26, ' ')||' - '||
        RPAD(TO_CHAR(l_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'), 26, ' ')||
        LPAD(TO_CHAR(l_us_total, '999,999,990'), 12, ' ')||' us'||
        LPAD(TO_CHAR(l_parse_calls_total, '999,990'), 8, ' ')||' parses'||
        LPAD(TO_CHAR(l_executons_total, '999,990'), 8, ' ')||' executions'||
        LPAD(TO_CHAR(l_rows_processed_total, '999,999,990'), 12, ' ')||' rows'||
        LPAD(TO_CHAR(l_samples_total, '9,999,990'), 10, ' ')||' samples'
    );
END;
/
SET SERVEROUT OFF;
--
PRO
PRO SQL> @&&cs_script_name..sql "&&cs_sql_id." "&&cs_seconds."
--
@@cs_internal/cs_spool_tail.sql
@@cs_internal/cs_undef.sql
@@cs_internal/cs_reset.sql
--