操作指导

前提条件

需要设置instr_unique_sql_track_type值为all。

语法

不涉及。

注意事项

DBE_PERF.STATEMENT表记录上限受instr_unique_sql_count控制，instr_unique_sql_track_type默认值为all，默认记录存储过程内语句，会使该表记录的语句条数更容易达到阈值，达到阈值后不会产生新的记录，当数据量达到阈值可以使用reset_unique_sql(scope, clean_type, clean_value) 接口清理该表数据。

查询DBE_PERF.STATEMENT_HISTORY需要在系统库执行，其他库中不保存数据。

示例

-- 检查instr_unique_sql_track_type的值，确认该参数值为all
gaussdb=# SHOW instr_unique_sql_track_type;
gaussdb=# SET instr_unique_sql_track_type='all';
-- 为了方便观察，临时设置记录全量SQL 的L0级别指标
gaussdb=# SET track_stmt_stat_level='L0,L0';
-- 创建测试库
gaussdb=# CREATE DATABASE tedb;
-- 创建存储过程并执行
gaussdb=# CREATE TABLE test(a INT,b INT);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
gaussdb=# INSERT INTO test VALUES(generate_series(1,30),2);
gaussdb=# CREATE OR REPLACE PROCEDURE test_proc1()
AS
num INT;
BEGIN
SELECT COUNT(*) INTO num FROM test;
END;
/
gaussdb=# CALL test_proc1();
 test_proc1
------------

(1 row)
-- 递归查询DBE_PERF.statement中的记录，查询结果时间类指标受实际环境影响，结果不固定。
gaussdb=# WITH p AS (SELECT * from DBE_PERF.STATEMENT WHERE query LIKE 'call test_proc1%' LIMIT 1)
SELECT
query,
n_soft_parse,
n_hard_parse,
n_returned_rows,
db_time,
cpu_time,
execution_time,
parse_time,
plan_time,
rewrite_time,
pl_execution_time,
pl_compilation_time,
data_io_time
FROM (
SELECT * FROM p UNION ALL SELECT * from DBE_PERF.STATEMENT
WHERE parent_unique_sql_id = (SELECT unique_sql_id FROM p)
);
               query                | n_soft_parse | n_hard_parse | n_returned_rows | db_time | cpu_time | execution_time | parse_time | plan_time | rewrite_time | pl_execution_time |
pl_compilation_time | data_io_time
------------------------------------+--------------+--------------+-----------------+---------+----------+----------------+------------+-----------+--------------+-------------------+-
--------------------+--------------
 call test_proc1();                 |            0 |            2 |               1 |   21590 |    13130 |          13769 |        327 |       728 |           11 |             12539 |
                659 |         2348
 SELECT COUNT(*)          FROM test |            0 |            1 |               1 |   19194 |    17705 |            289 |        327 |       728 |           11 |              9324 |
                659 |         2348
(2 rows)

-- 同理查询DBE_PERF.statement_history，需要在系统库查询
gaussdb=# WITH p AS (SELECT * FROM DBE_PERF.STATEMENT_HISTORY WHERE query LIKE 'call test_proc1%' LIMIT 1)
SELECT
parent_unique_sql_id,
unique_query_id,
query,
n_soft_parse,
n_hard_parse,
n_returned_rows,
db_time,
cpu_time,
execution_time,
parse_time,
plan_time,
rewrite_time,
pl_execution_time,
pl_compilation_time,
data_io_time
FROM (
SELECT * FROM DBE_PERF.STATEMENT_HISTORY
WHERE unique_query_id = (SELECT unique_query_id FROM p)
UNION ALL
SELECT * FROM DBE_PERF.STATEMENT_HISTORY
WHERE parent_unique_sql_id = (SELECT unique_query_id FROM p)
);
 parent_unique_sql_id | unique_query_id |               query                | n_soft_parse | n_hard_parse | n_returned_rows | db_time | cpu_time | execution_time | parse_time | plan_t
ime | rewrite_time | pl_execution_time | pl_compilation_time | data_io_time
----------------------+-----------------+------------------------------------+--------------+--------------+-----------------+---------+----------+----------------+------------+-------
----+--------------+-------------------+---------------------+--------------
                    0 |      4276558564 | call test_proc1();                 |            0 |            2 |               1 |   14842 |     7871 |          13480 |          0 |
  0 |            0 |             12539 |                   0 |            0
           4276558564 |      1011671199 | SELECT COUNT(*)          FROM test |            0 |            1 |               1 |   12446 |    12446 |              0 |        327 |
728 |           11 |              9324 |                 659 |          615
(2 rows)

-- 非系统库查询dbe_perf.statement_history，不存放数据
gaussdb=# \c tedb
tedb=# WITH p AS (SELECT * FROM DBE_PERF.STATEMENT_HISTORY WHERE query LIKE 'call test_proc1%' LIMIT 1)
SELECT
parent_unique_sql_id,
unique_query_id,
query,
n_soft_parse,
n_hard_parse,
n_returned_rows,
db_time,
cpu_time,
execution_time,
parse_time,
plan_time,
rewrite_time,
pl_execution_time,
pl_compilation_time,
data_io_time
from (
SELECT * FROM DBE_PERF.STATEMENT_HISTORY
WHERE unique_query_id = (SELECT unique_query_id FROM p)
UNION ALL
SELECT * FROM DBE_PERF.STATEMENT_HISTORY
WHERE parent_unique_sql_id = (SELECT unique_query_id FROM p)
);
 parent_unique_sql_id | unique_query_id | query | n_soft_parse | n_hard_parse | n_returned_rows | db_time | cpu_time | execution_time | parse_time | plan_time | rewrite_time | pl_execu
tion_time | pl_compilation_time | data_io_time
----------------------+-----------------+-------+--------------+--------------+-----------------+---------+----------+----------------+------------+-----------+--------------+---------
----------+---------------------+--------------
(0 rows)

-- 切换回初始数据库，并删除测试数据库
tedb=# \c postgres
gaussdb=# DROP DATABASE tedb;