入职外包仅两天，就利用ASP技能搞定核心问题，女上司直呼:提前转正

本文链接：https://blog.csdn.net/ORACLE_BBED/article/details/138048328

前言

上篇文章介绍了ASP的基本原理，详细内容请会看文章：

《刚刚下岗的Oracle DBA学了这个国产数据库技能后，下午就上岗了》： https://blog.csdn.net/ORACLE_BBED/article/details/138014441?spm=1001.2014.3001.5501

local_active_session视图

内存里的ASP信息保存在DBE_PERF.local_active_session视图中。视图DBE_PERF.local_active_session的实现是通过调用函数get_local_active_ session()获取g_instance.stat_cxt.active_sess_hist_arrary->active_sess_hist_info里的采样数据数据生成视图。这部分内容为实时的信息，存储在内存中，显示的是最近的活跃session信息。视图的具体内容如下表:

名称	类型	描述
sampleid	bigint	采样ID,后台ASP采样线程开始时从0开始，每采样一次递增1。
sample_time	timestamp w/ timezone	采样的时间。
need_flush_sample	boolean	该样本是否需要刷新到磁盘。该值是通过计算 sampleid%asp_flush_rate来判断是否需要刷新。 True表示需要，false表示不需要。
databaseid	oid	数据库ID。
thread_id	bigint	当前线程的ID，取值来源于t_thrd.proc_cxt.MyProcPid。
sessionid	bigint	会话的ID。
start_time	timestamp w/ timezone	会话的启动时间。
event	text	具体的事件名称。
lwtid	integer	当前线程的轻量级线程号。
psessionid	bigint	streaming线程的父线程。
tlevel	integer	streaming线程的层级。与执行计划的层级(ID)相对应。
smpid	integer	smp执行模式下并行线程的并行编号。
userid	oid	当前session用户的ID。
application_name	text	后台线程的名称，例如'ASP','Wal Writer'等。
client_addr	inet	数据库client端的地址。
client_hostname	text	client端的名称。
client_port	integer	客户端用于与后端通讯的TCP端口号。
query_id	bigint	debug query id，获取的是u_sess->debug_query_id值，由node id,timeline id和query sequence number 组合而成。
unique_query_id	bigint	当前sql在完成rewrite之后生成的unique query id，可能为0此时该字段显示为空。
user_id	oid	unique query的key中的user_id。
cn_id	integer	在DN上表示下发该unique sql的节点id，unique query的key中的cn_id
unique_query	text	规范化后的UniqueSQL文本串，只有unique query id不为0时才有内容。
locktag	text	会话等待锁的信息，为LOCALLOCKTAG数据结构，内容包含锁对象标志和锁的类型。
lockmode	text	会话等待锁的类型:- LW_EXCLUSIVE:排他锁,LW_SHARED:共享锁,LW_WAIT_UNTIL_FREE:等待LW_EXCLUSIVE可用
block_sessionid	bigint	如果会话正在等待锁，阻塞该会话获取锁的会话标识。为0时表示没有当前没有阻塞。
wait_status	text	等待事件的状态信息。
global_sessionid	text	全局会话ID。
plan_node_id	int	执行计划树的算子id，为-1时表明在ASP采样时，执行器还没有给该计划树分配算子id.
xact_start_time	timestamp w/ timezone	当前事务的开始时间
query_start_time	timestamp w/ timezone	当前语句的开始时间
state	enum	台线程的运行状态，可以是如下值:STATE_UNDEFINED, //初始化状态 STATE_IDLE, //正在空闲等待状态;STATE_RUNNING, //正在active执行状态;STATE_IDLEINTRANSACTION, // 在处理事务过程中，暂时无法执行处于空闲等待状态;STATE_FASTPATH, //在执行fast path 函数 STATE_IDLEINTRANSACTION_ABORTED, // 在处理事务过程中有某个语句执行错误，处于空闲状态;STATE_DISABLED, // 被禁用了track_activities，不能追踪状态 STATE_RETRYING, //重试;STATE_COUPLED, //绑定会话 STATE_DECOUPLED //取消绑定会话

ASP系统表

ASP的表为GS_ASP，属于CATALOG里的系统表，在src/include/catalog/gs_asp.h里定义: CATALOG(gs_asp,9534)。具体字段同视图DBE_PERF.local_active_session。GS_ASP的数据来源为选取g_instance.stat_cxt.active_sess_hist_arrary->active_sess_hist_info 里的部分采样并持久化到磁盘中。具体选取多少采样值是由GUC参数asp_flush_rate设定。相对于视图DBE_ PERF.local_active_session，ASP表中存放的是过去更长时间内的活跃会话历史信息，适合更长时间跨度的统计分析。

ASP Log 文件

当GUC参数asp_flush_mode设置为file时，g_instance.stat_cxt.active_sess_hist_arrary里的部分采样信息不会记录到GS_ASP表，而是会被持久化存放到ASP的log文件中。文件的地址和命名分别在GUC参数asp_log_directory和asp_log_filename中设置。默认为数据库asp_data目录下。

ASP 的log文件内容样例如下:

{"sampleid":"350430","sample_time":"2023-07-27T23:59:35.558198+08:00","need_flush_sample":true ,"databaseid":0,"thread_id":"139681843377920","sessionid":"139681843377920","global_sessionid" :"0:0#0","start_time":"2023-07-07T16:39:56.969878+08:00","xact_start_time":null,"query_start_t ime":null,"state":"active","event":"none","waitstatus":"none","lwtid":23455,"psessionid":null, "tlevel":0,"smpid":0,"userid":0,"application_name":"Wal Writer","locktag":null,"lockmode":null,"block_sessionid":null,"client_addr":null,"client_hostn ame":null,"client_port":null,"query_id":"","unique_query_id":null,"user_id":null,"cn_id":null, "unique_query":null}

实战:定位阻塞源

模拟故障

执行session1的两条语句后，再执行session2的一条语句，session2会被阻塞等待session1的锁释放。

session 1:更新表emp中id为3的记录中字段name 值为c1。

begin;
update emp SET name = 'c1' where id = 3;

session2: 更新表emp中id为3的记录中字段name 值为c2。

update emp SET name = 'c2' where id = 3;

查看阻塞

如果是查询实时信息，那么我们可以通过如下SQL去查询阻塞链。

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.granted;
  blocked_pid   | blocked_user |  blocking_pid  | blocking_user |              blocked_statement              |    current_statement_in_blocking_process    
----------------+--------------+----------------+---------------+---------------------------------------------+---------------------------------------------
 47810227406592 | omm          | 47810357757696 | omm           | update emp SET name = 'MogDB' where id = 3; | update emp SET name = 'MogDB' where id = 3;

alt 可以看出，sessionid:47810227406592被阻塞。

通过ASP数据查询阻塞

SELECT sessionid, start_time, event, count
FROM (
SELECT sessionid, start_time, event, COUNT(*)
FROM dbe_perf.local_active_session
WHERE sample_time > now() - 10 / (24 * 60)
GROUP BY sessionid, start_time, event) as t ORDER BY SUM(t.count) OVER (PARTITION BY t. sessionid, start_time)DESC, t.event;

   sessionid    |          start_time           |         event         | count 
----------------+-------------------------------+-----------------------+-------
 47810357757696 | 2024-04-21 21:07:23.08784+08  | wait cmd              |   598
 47810390652672 | 2024-04-21 21:12:03.224273+08 | HashJoin - build hash |     1

SELECT sessionid,start_time,event,wait_status,block_sessionid,final_block_sessionid FROM dbe_perf.local_active_session where block_sessionid in (47810357757696,47810390652672);

alt 发现阻塞源头是sessionid:47810357757696，sessionid:47810390652672是被阻塞者，与前面结果一致。

增强ASP功能

MogDB企业版增强的ASH能力，称为"SQL运行状态观测"，主要是通过在采样数据中增加SQL执行算子的采样来完成的。在dbe_perf.local_active_session和GS_ASP中新增一列plan_node_id来记录SQL语句每个算子操作的执行情况。即可知道对于出现性能问题的SQL具体是慢在了执行计划的哪个步骤上。

开启增强ASP功能的参数:

resource_track_level 参数指定为operator，则会开启算子采样能力，默认值是query，只会记录SQL级别采样。

模拟

创建表test：

MogDB=# create table test(c1 int);
CREATE TABLE
MogDB=# insert into test select generate_series(1, 1000000000);

查询出该SQL的query_id

MogDB=# select query,query_id from pg_stat_activity where query like 'insert into test select%';
                    query                                  |    query_id
-----------------------------------------------------------+-----------------
 insert into test select generate_series(1, 100000000000); | 562949953421368
(1 row)

查询带plan_node_id的执行计划

Set resource_track_cost=10;


MogDB=# select query_plan from dbe_perf.statement_complex_runtime where queryid = 562949953421368;
                                 query_plan
----------------------------------------------------------------------------
 Coordinator Name: datanode1                                               +
 1 | Insert on test  (cost=0.00..17.51 rows=1000 width=8)                         +
 2 |  ->  Subquery Scan on "*SELECT*"  (cost=0.00..17.51 rows=1000 width=8)    +
 3 |   ->  Result  (cost=0.00..5.01 rows=1000 width=0)                         +
                                                                        +
(1 row)

SQL运行状态观测

在session2中，根据query_id从采样视图dbe_perf.local_active_session中查询出该语句的采样情况，结合上面查询的执行计划做性能分析。

MogDB=# select plan_node_id, count(plan_node_id) from dbe_perf.local_active_session where query_id = 562949953421368 group by plan_node_id;
 plan_node_id | count
--------------+-------
        3     |   12
        1     |   366
        2     |   2
(3 rows)