1 创建测试环境
CREATE SCHEMA IF NOT EXISTS test171;
DROP TABLE IF EXISTS test171.test_log;
CREATE TABLE test171.test_log (
id SERIAL PRIMARY KEY,
proc_name VARCHAR(100),
action VARCHAR(50),
run_time TIMESTAMP DEFAULT clock_timestamp(),
data VARCHAR(200)
);
2 创建存储过程
DROP PROCEDURE IF EXISTS test171.proc_inner;
DROP PROCEDURE IF EXISTS test171.proc_outer;
CREATE OR REPLACE PROCEDURE test171.proc_inner(p_round INT)
AS
v_count INT;
v_data VARCHAR(200);
BEGIN
INSERT INTO test171.test_log (proc_name, action, data)
VALUES ('proc_inner', 'INSERT', 'round-' || p_round);
SELECT count(*) INTO v_count
FROM test171.test_log WHERE proc_name = 'proc_inner';
UPDATE test171.test_log
SET data = data || '-updated'
WHERE proc_name = 'proc_inner'
AND run_time = (
SELECT max(run_time)
FROM test171.test_log
WHERE proc_name = 'proc_inner'
);
PERFORM pg_sleep(3.5);
SELECT data INTO v_data
FROM test171.test_log
WHERE proc_name = 'proc_inner'
ORDER BY run_time DESC LIMIT 1;
END;
/
CREATE OR REPLACE PROCEDURE test171.proc_outer()
AS
i INT;
v_count INT;
BEGIN
INSERT INTO test171.test_log (proc_name, action, data)
VALUES ('proc_outer', 'START', 'outer procedure begin');
FOR i IN 1..6 LOOP
CALL test171.proc_inner(i);
END LOOP;
SELECT count(*) INTO v_count FROM test171.test_log;
UPDATE test171.test_log
SET data = data || '-outer-done'
WHERE proc_name = 'proc_outer' AND action = 'START';
END;
/
3 开启参数
instr_unique_sql_track_type = 'all';
track_stmt_stat_level = 'L0,L0';
log_min_duration_statement = 0;
-- 验证参数已生效
SHOW instr_unique_sql_track_type;
SHOW track_stmt_stat_level;
SHOW log_min_duration_statement;
-- 执行自建存储过程，已有存储过程无需自建
CALL test171.proc_outer();
查询1:查看最近10min的存储过程信息
SELECT
sh.start_time AS 调用时间,
sh.schema_name AS 模式,
sh.user_name AS 用户,
parent.query AS 父存储过程
FROM dbe_perf.statement_history sh
LEFT JOIN dbe_perf.statement_history parent
ON sh.parent_query_id = parent.unique_query_id
WHERE sh.parent_query_id != 0
AND sh.start_time >= (clock_timestamp() - interval '10 minutes')
ORDER BY sh.start_time;
查询2：信息汇总
SELECT
COUNT(*) AS 总记录数,
COUNT(CASE WHEN parent_query_id = 0 THEN 1 END) AS 顶层语句,
COUNT(CASE WHEN parent_query_id != 0 THEN 1 END) AS 存储过程内部语句
FROM dbe_perf.statement_history
WHERE start_time >= (clock_timestamp() - interval '90 minutes');
查询3：存储过程内部信息（扩展查询，需要具体信息可根据自己环境定制）
SELECT
sh.start_time AS "调用时间",
sh.finish_time AS "结束时间",
sh.schema_name AS "模式",
sh.user_name AS "用户",
sh.query AS "SQL语句",
sh.parent_query_id AS "父SQL_ID",
sh.unique_query_id AS "当前SQL_ID",
parent.query AS "父存储过程",
sh.execution_time AS "执行耗时(微秒)"
FROM dbe_perf.statement_history sh
LEFT JOIN dbe_perf.statement_history parent
ON sh.parent_query_id = parent.unique_query_id
WHERE sh.parent_query_id != 0
AND sh.start_time >= (clock_timestamp() - interval '10 minutes')
ORDER BY sh.start_time;
