GaussDB语句执行相关SQL

2.1 常用视图
pg_stat_activity
pgxc_stat_activity
pg_thread_wait_status
pgxc_thread_wait_status
dbe_perf.summary_statement + dbe_perf.statement
dbe_perf.get_global_slow_sql_by_timestamp + dbe_perf.statement_history
gs_asp + dbe_perf.local_active_session
wdr报告 + snapshot视图
复制
2.2 常用语句
查看当前正在执行语句及其执行时长
select coorname, substring(0, 50, query), state, usename, now()-query_start as elp from pgxc_stat_activity where pid!=0 and usename in (’$user_name’) order by elp desc;

复制
select coorname, unique_sql_id, substring(query,0,100), state, usename, now()-query_start as elp from pgxc_stat_activity where pid!=0 and usename like ‘%hicloud%’ order by elp desc;

生成计划
set current_schema to $schema_name; set max_datanode_for_plan=1; – CN上执行 explain $sql; – 不会真正执行SQL explain verbose $sql; – 会真正执行SQL，写操作谨慎操作 – DN上执行 explain performance $sql; – 会真正执行SQL，写操作谨慎操作 explain analyze $sql; – 会真正执行SQL，写操作谨慎操作"
使用PBE生成计划
set current_schema to schema_name; set max_datanode_for_plan=1; prepare select_record(integer, varchar) as select col3 from t_1 where col1 = $1 and col2 = $2; explain execute select_record(1, ‘abc’); explain verbose execute select_record(1, ‘abc’); explain analyze execute select_record(1, ‘abc’); explain performance execute select_record(1, ‘abc’);"
动态生成计划
select * from dbe_perf.statement where unique_sql_id = unique_sql_id; -- CN执行 select * from dynamic_func_control('GLOBAL', 'STMT', 'TRACK', '{"unique_sql_id", “L2”}’); – 抓此SQL的FULLSQL L2 execute direct on datanodes ‘select * from statement_history where unique_query_id = unique_sql_id and query_plan ilike ''%cost%'' limit 1'; select * from dynamic_func_control('GLOBAL', 'STMT', 'UNTRACK', '{"unique_sql_id"}’); – 取消抓取 select * from dynamic_func_control(‘GLOBAL’, ‘STMT’, ‘LIST’, ‘{}’); select * from dynamic_func_control(‘GLOBAL’, ‘STMT’, ‘CLEAN’, ‘{}’); execute direct on datanodes ‘select query_plan from statement_history where unique_query_id = $unique_sql_id and query_plan ilike ‘’%cost%’’’;

查看当前正在执行的语句的等待事件
select a.pid, a.sessionid, a.datname, a.usename, a.client_addr, a.xact_start, a.query_start, (now() - a.query_start)::text as query_runtime, w.wait_status,w.wait_event,w.locktag,w.lockmode,w.block_sessionid,a.query from pg_stat_activity a join pg_thread_wait_status w on a.sessionid = w.sessionid where a.pid <> pg_backend_pid() and a.state = ‘active’ and a.client_addr is not null order by query_runtime desc;
select * from pgxc_thread_wait_Status where tid in (select pid from pgxc_stat_activity where pid != 0);

select * from pg_thread_wait_Status where tid in (select pid from pg_stat_activity where pid != 0);

select sample_time,substring(unique_query,0,100),application_name,wait_status from dbe_perf.local_active_session where wait_status not in (’‘none’’,’‘wait cmd’’) order by start_time desc;

查询连接数
– 查询整个实例的用户连接数，CN执行 select count() from pgxc_stat_activity where usename in (‘$user1', '$user2’);
– 查询当前集群上所有CN的连接数,CN执行 select coorname, count() from pgxc_stat_activity group by 1；

– 查询一个节点的用户连接数，CN和DN均可 select count(*) from pg_stat_activity where usename in (‘$user1', '$user2’);

查看表上的锁
select a.pid,a.sessionid,substring(a.query,0,50) from pg_stat_activity a,pg_locks l where a.pid=l.pid and a.sessionid=l.sessionid and l.relation=(select oid from pg_class where relname=’$table_name’);