158 lines
6.6 KiB
MySQL
158 lines
6.6 KiB
MySQL
----------------------------------------------------------------------------------------
|
|
--
|
|
-- 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
|
|
-- |